Incorporating DB2 and SQL/DS Tables/Views

This document covers the following topics:

• Prerequisites

• Selecting DB2 and SQL/DS Tables/Views

• Incorporation Functions for Tables/Views

• Incorporating DB2 Tables and Views in Batch Mode

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

The Incorporate DB2 Tables/Views screen is called with function code I and object code T2 in a Predict main menu or with the command INCORPORATE TABLES.

17:55:19             *****  P R E D I C T  *****              2007-05-31
Plan   0              - Incorporate DB2 Tables/Views -                         
                                                                               
                                                                               
                                                                               
Table / View name .....                                                        
SQL Server ...........*                                  <Default Server>      
                                                                               
Select options                                                                 
  Tables or Views ....*                                                        
  Creator name ........                                                        
  DB2 database name ...                                                        
                                                                               
Processing option                                                              
  Connect automatic ... N (Y,N)                                                
                                                                               
Update when connecting                                                         
  File attributes ..... Y (Y/N)                                                
                                                                               
                                                                               
Command ===>                                                                   
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
      Help  Next  Stop  Last  LnkEl Flip  Print Impl  AdmFi SelFi Prof  Main   

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

Files which meet the given selection criteria and are not already connected to a Predict file object are then listed as follows.

13:03:38             *****  P R E D I C T  *****           2007-05-31
                         - Incorporate DB2 Tables/Views -
    Search-crit.: Table/View name=ALL,DB name=ALL,Creator=ALL
                                                    Doc.
    Cmd Creator   Table/View name     Database  T   exists      Remark
   
    __  ABA       DEMO1               DSNDB04   T   No
    __  ABA       PLAN_TABLE          DSNDB04   T   No
    __  AZ        APPT_TODAY          DSNDB04   V   No
    __  AZ        D_BAR               PREDICT   T   No
    __  AZ        D_FX                PREDICT   T   No
    __  AZ        D_NAT1              DSNDB04   T   Yes
  

The tables/views in the list can then be processed by entering a function code in the Cmd column. The functions T, C and I apply.

Incorporation Functions for Tables/Views

Valid functions:

• Compare - Code T

• Connect - Code C

• Display Masters - Code M

• Incorporate - Code I

Compare - Code T

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

If no differences between the field lists are found and Connect automatic is set to Y, the DB2 tables/view and the Predict file object are connected.

Connect - Code C

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.

Display Masters - Code M

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 - Code I

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).

Calling the Function

Specify the following parameters in an additional input screen before executing the function.

Incorporating DB2 Tables and Views in Batch Mode

Command: INCORPORATE TABLE

Enter parameters on the next line in positional or keyword form.

If you explicitly set SQL-SERVER to blank in order to use the default server, another keyword must be specified after the SQL-SERVER keyword. If it is not possible to specify another keyword, the SQL-SERVER keyword must be followed by "D1=*" as shown below:

SQL-SERVER= ,D1=*

Example

To incorporate the table "TDEPT" in database "DSN8130", code the command:

INCORPORATE TABLE
TABLE=TDEPT,TYPE=T,DB2NAME=DSN8130,,SUB-FUNCTION=I

or

INCORPORATE TABLE
TDEPT,T,DSN8130,,I

The example above uses the Natural parameters ID=, and IA==.