Extract a referentially intact set of data from tables which do not have DB2 RI defined.

Document ID : KB000054344
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

How do I extract a referentially intact set of data from two tables which do not have DB2 RI defined between them?

RC/Extract allows DB2 mapping of tables, but will not allow mapping of two tables unless RI exist. If DB2 RI is not permitted for business reasons and no primary keys exist, how do I go about extracting a referentially intact set of data from these two tables. The application handles RI and no DB2 RI may be created.

Solution:

In order to extract data from two tables not related in DB2, it is necessary to first create some User Defined RI. User defined RI is created with the use of RI Manager and will store this User defined RI within its own product tables. This User defined RI is not known to or recognized by DB2 or other non-CA tools but, it is recognized by some CA tools like RC/Extract, Fast Check, Fast Load and RC/Update.

Create User Defined Referential Integrity between two tables
Two tables called TABLE1 and TABLE2 both share a key column "COL1" defined with the same column type and length.

RI Manager shows us that they are not related by RI

 PARENT NAME ===>                    CREATOR ===>          MODE    ===> O
 CHILD  NAME ===> TABLE%             CREATOR ===> BASLUA3  RI ONLY ===> N
 GROUP  NAME ===> *                  STATUS  ===> *        DISPLAY LEVEL => 0
 ----------------------------------------------------------------------
 DB2 SYSTEM ID ===> D81A  LOCATION ===> LOCAL              VERSION ===> V8R1M0
 S  XXX  TABLE NAME                         LVL  CREATOR  GROUP     RI  STATUS
    --------------------------------------------------------------------------
 __   1. TABLE2                                  BASLUA3            ** NO RI
    --------------------------------------------------------------------------
 __   1. TABLE1                                  BASLUA3            ** NO RI

First, we must create a Primary key on the Parent table like this:

Note that columns to be used for a primary key must be NOT NULL. The "P" line command is used to create a Primary Key(PK) on the parent table. This example assumes no primary key exists already.

S  XXX  TABLE NAME
   ----------------
__   1. TABLE2
   ----------------
P_   1. TABLE1

In the next screen a "C" line CMD is entered to create a PK, pick a name "tab1pk", a KEY of "P" for Primary Key and a TYPE of "U" for User defined and press enter thus. A KEY of "X" can be used if it's a Unique key.

Table Name ==> TABLE1             Creator ==> BASLUA3
CMD NAME               CREATOR   KEY  TYPE
 C  tab1pk____________ BASLU02    P    U

On the next screen we choose which column or columns will be part of the PK with a "Pn", "n" being a seq number. For example "P1" would designate the that column as the first column of the primary key and so on if there are more than one columns in the key as there so often is. In this example only one column will be used.

Table Name  ==> TABLE1             Creator   ==> BASLUA3
 Key Type    ==> P   (P/X)          RI Type   ==> U   (S/U)
 CMD ### COLUMN NAME         COLTYPE   BASETYPE  SIZE   N KEYSEQ#
 P1_ 1   COL1                CHAR      CHAR      4      N
 ___ 2   COL2                CHAR      CHAR      4      N
 ___ 3   COL3                CHAR      CHAR      4      N
 ___ 4   COL4                CHAR      CHAR      4      N

Press enter and the screen looks like this. COL1 has a KEYSEQ of '1'.

Table Name  ==> TABLE1             Creator   ==> BASLUA3
Key Type    ==> P   (P/X)          RI Type   ==> U   (S/U)
CMD ### COLUMN NAME         COLTYPE   BASETYPE  SIZE   N KEYSEQ#
___ 1   COL1                CHAR      CHAR      4      N  1
___ 2   COL2                CHAR      CHAR      4      N
___ 3   COL3                CHAR      CHAR      4      N
___ 4   COL4                CHAR      CHAR      4      N

Press PF3 and SQL to create the PK is generated on the screen.
Press Enter to run this online.
This will store the PK information on the RI Manager product tables.
It should run quickly. Check to make sure the SQLCODES are zero before exiting back to the first screen.
Having done that we now must create the RI between the two tables.
On the same RI Manager main screen we now use the "C" line command on TABLE1 to create a child relationship with another table.

PARENT NAME ===>                    CREATOR ===>          MODE    ===> O ONLI
CHILD  NAME ===> TABLE%             CREATOR ===> BASLUA3  RI ONLY ===> N
GROUP  NAME ===> *                  STATUS  ===> *        DISPLAY LEVEL => 0
---------------------------------------------------------------------- BASLU0
DB2 SYSTEM ID ===> D81A  LOCATION ===> LOCAL              VERSION ===> V8R1M0
S  XXX  TABLE NAME                         LVL  CREATOR  GROUP     RI   STATU
S
   --------------------------------------------------------------------------
__   1. TABLE2                                  BASLUA3            ** NO RI
   --------------------------------------------------------------------------
C_   1. TABLE1                                  BASLUA3            ** NO RI

On the next screen type in the name of the child table in the search criteria at the top of the screen. Here TABLE2 has been specified in CHILD NAME and when it is listed we use the "S" line command to select it as below.

Parent Name ===> TABLE1             Creator ===> BASLUA3
Child  Name ===> TABLE2             Creator ===> BASLUA3  Where ==> N
------------------------------------------------------------------ BASLU02
Enter 'S' to select one or more tables
 
SEL TABLE NAME         CREATOR  DATABASE  TSNAME    COLCOUNT  RECLEN   OBID
S__ TABLE2             BASLUA3  DBCORP2   TSTEST        4        20       5

On the next screen we see that we are ready to create the RI. Check that RI TYPE is "U" for "User RI" and decide on the DELETE RULE. In this case it is set to RESTRICT "R". Press PF3 to generate the SQL and press enter to execute the SQL online. This will store the RI information on the RI Manager product tables. It should run quickly.

Check the SQL return codes. PF3 back to the main RI Manager Screen.

 Parent Name ==> TABLE1             Creator  ==> BASLUA3    Mode    ==> O
 Child  Name ==> TABLE2             Creator  ==> BASLUA3    RI Type ==> U
 Delete Rule ==> R                  Rulename ==>
 -----------------------------------------------------------------------
  Use F1-Fn to add a Foreign Key Rule
 CMD ### COLUMN NAME         COMMENTS
 F1_   1 COL1

After returning to the main RI Manager screen TABLE2 can be seen to be slightly indented beneath TABLE1. This indenting is the means by which RI Manager denotes a CHILD relationship. The LVL column on screen for TABLE1 shows it is the first level and LVL for TABLE2 shows that it is a second level table....a CHILD.

PARENT NAME ===>                    CREATOR ===>          MODE    ===> O ONLI
CHILD  NAME ===> TABLE%             CREATOR ===> BASLUA3  RI ONLY ===> N
GROUP  NAME ===> *                  STATUS  ===> *        DISPLAY LEVEL => 0
---------------------------------------------------------------------- BASLU0
DB2 SYSTEM ID ===> D81A  LOCATION ===> LOCAL              VERSION ===> V8R1M0
S  XXX  TABLE NAME                         LVL  CREATOR  GROUP     RI   STATU
   --------------------------------------------------------------------------
__   1. TABLE1                              1   BASLUA3
__   2. TABLE2                             2   BASLUA3            U    A

This shows that TABLE2 is a child of TABLE1 and that it's RI is of "RI" "U" and that it's STATUS is ACTIVE "A".
This RI can be INACTIVATED by the use of the "I" line command on TABLE2.

Extract the data from the two tables with RC/Extract
Now RC/EXTRACT will be able to select TABLE1 as the parent and then be able to select TABLE2 as a child with all the normal extract functions available to the user. Rather than outlining the steps to create a RCX Strategy this report shows a PLAN report from RC/EXTRACT which describes the situation that RCX can see after these two tables have been selected in a Strategy. RCX refers to the relationship in the PLAN report below as a "RI/MANAGER RELATIONSHIP COL1" which means that it's User Defined RI. If the RI was in fact DB2 RI, RCX refers to this relationship as "DB2 RELATIONSHIP COL1"

                           Display Mode is PLAN
********************************* TOP OF DATA **********************
_ STEP# 1         OBJECT: BASLUA3.TABLE1
  REASON:
     THIS OBJECT WAS SELECTED AS THE STARTING OBJECT.
  ------------------------------------------------------------------
_ STEP# 2         OBJECT: BASLUA3.TABLE2
  REASON:
     RI/MANAGER RELATIONSHIP COL1 INDICATES THAT THIS OBJECT
     IS A CHILD OF BASLUA3.TABLE1, PROCESSED IN
     STEP(S) 1.
  ------------------------------------------------------------------

Other Relevant Information
In RI Manager if the "L" line command is used on a CHILD table like TABLE2 then RI manager will display the columns which participate in the relationship, both on the parent and child end. This can be useful to check which columns are being used particularly, if the order of the columns is incorrect on either primary keys.
The "V" line command can be used to verify that the User Defined RI has been correctly stored on the product tables.

Documentation References
RI Manager User Guide.
RC Extract Reference Guide and User Guide.