Incorporating DB2 and SQL/DS Tables/Views

This document covers the following topics:

Prerequisites

  • For DB2 tables and views, a Predict object documenting the DB2 database containing the tables/views must exist.

  • For SQL/DS tables and views, a corresponding database object in Predict is not required. See the section DB2 and SQL/DS in the Predict and Other Systems documentation.

    Note:
    See Concepts of Incorporation for basic information on how to use incorporation functions.

Selecting DB2 and SQL/DS Tables/Views

Selection criteria

Common parameters which are not included in the table below are described under Common Parameters for All DB2 Types.

Parameters
Table / view name The name in DB2 of the tables and views from which the data dictionary objects are to be created. Asterisk notation is allowed.
Tables or views
Limit the function to files of one type. Valid values:
T DB2 tables
V DB2 views
blank All types
Creator name The DB2 "creator" of the tables and views from which the data dictionary objects are to be created. Asterisk notation is allowed.
DB2 database name The name in DB2 of the database or databases in which the specified tables and views reside. Asterisk notation is allowed. When incorporating Tables/Views from SQL/DS, this parameter is protected.
Update file attributes (connect option) Provided on a later page of the schema incorporation wizard (after the physical object has been selected from the drop-down list box). Y - Attributes of the Predict file object and, for files of type E, the subselect specification of the SQL create statement are to be updated. The triggers linked to the file via association "Has TR" are also updated.

Files which meet the given selection criteria and are not already connected to a Predict file object are then provided in the drop-down list box on the next page of the schema incorporation wizard.

Information in the drop-down list box
Creator Creator in DB2.
Table/view name Name of the table/view.
Database Name of DB2 database(s) where the table/view is implemented.
T
Type of the DB2 file:
T DB2 table
V DB2 view
M DB2 query table
Predict file ID The ID of Predict file object for a DB2 table or view with this DB2 name.
Remark Any of the following type-specific remarks or blank. For type-independent remarks see Remarks which apply to all Object Types.
DA missing The DB2 database, storagegroup or tablespace the table/view belongs to are not documented in Predict. Incorporation is not possible.
ST missing
DS missing
Master missing The specified DB2 view cannot be incorporated as a data dictionary object until a dictionary object for a related DB2 table or view has been created.
Invalid file-type The type of the specified DB2 table or view does not match the type of the corresponding Predict object: the file type of the data dictionary object is either not D (DB2 table) or not E (DB2 view).

Incorporation Functions for Tables/Views

Valid functions:

Note:
Corresponding DB2 tables/views and Predict file objects of type D and E are identified by DB2 table/view name.

Test

Compares DB2 table/views and corresponding Predict file object. Differences between field lists and attributes of the tables/views are reported.

Connect

Connects DB2 tables/views to Predict file object and

  • copies current information from the DB2 catalog to the dictionary object;

  • writes to the generation log of the Predict file object that a corresponding implemented table/view existed when the connection was established.

Specify the following parameters in an additional page of the schema incorporation wizard.

Connect Options
Update file attributes See the parameter table in Selecting DB2 and SQL/DS Tables/Views for a description of this parameter.

Display Masters

DB2 databases, storagegroups and tablespace and tables/views a table/view is related to must be documented in Predict before the table/view can be incorporated. This function finds all objects that are not documented. These can then be incorporated before the table/view is incorporated.

Incorporate

Scope of the Incorporation

  • One Predict file object is created for each table or view. The ID of the new file object consists of the creator name and DB2 name of the table or view joined by a hyphen.

  • One field object is created for each field in the table or view. The ID assigned to each new field object will be the name of the field in DB2. For each view that is incorporated, the appropriate parts of the subselect specification in the SQL statement CREATE will be copied to the file and field objects created.

  • One field object is added to file SAG-DISTINCT-TYPE for each distinct type that is not already documented. The ID assigned to each new field object consists of the schema name and the type name concatenated by a hyphen. The connection between the standard fields of SAG-DISTINCT-TYPE and the table field is established if the field in DB2 is distinct type.

  • One Predict file object with file type IV (Intermediate view) is created for each subselect clause in the from clause of a view definition. For each column in the select list of subselect a Predict field object is created. For each intermediate view that is created, the appropriate parts of the subselect specification in the SQL statement CREATE will be copied to the file and field objects created.

  • One Predict field object (type SP) for each composite index is created.

  • One Predict field object (type SP) for each composite foreign key is created if there is not already a Predict field object for a composite index built from the same columns.

  • One Predict relationship object is created for each foreign key.

  • Labels defined for columns are documented as field headers.

  • A trigger object is added for each trigger defined for a table or view. A link is established either to the file object via association "Has TR" or to the corresponding field objects in case of an update trigger.

  • A verification is added and linked to the corresponding field for each check constraint that is defined for one column.

  • Attributes of the corresponding file object are updated for each table check constraint (check expression for more than one column).

Specify the following parameters in an additional page of the schema incorporation wizard.

Incorporation Options
Comments as abstract
Y String constants assigned with COMMENT ON are to be incorporated as the abstract of the corresponding Predict object:

  • Comments assigned to the table or view are to be copied to the file object.

  • Comments assigned to a field of the table or view are to be copied to the field object.

List incorporated code
Y The values of all attributes incorporated for the table or view are to be listed.
Document as table
Y

For views and materialized-query tables only.

This incorporation option can be used if you are not interested in the derivation of a view from the underlying base table or if the used query involves the usage of compiled SQL scalar or table functions.

Keywords

The schema incorporation wizard uses the following keywords when showing the command that is to be processed:

Field Keyword
Table/View name TABLE
Tables or Views TYPE
Creator name CREATOR
DB2 Database name DB2NAME
Function SUB-FUNCTION
Incorporate comments COMMENT
List incorporated code LIST
Incorporate comments ABSTRACT
Update file attributes UPD-FILE
SQL Server SQL-SERVER
Document as table AS-TABLE