search cancel

CA RC/Extract for DB2 for Z/OS : Extract a referentially intact set of data from tables which do not have DB2 RI defined.

book

Article ID: 54344

calendar_today

Updated On:

Products

RC/Extract for DB2 for z/OS RI Manager for DB2 for z/OS

Issue/Introduction

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

RC/Extract for DB2 for z/os (RCX) 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 is a referentially intact set of data extracted from these two tables.

The user application handles RI manually and no DB2 RI may be created.

 

 

Environment

Release: R19, R20
Component: RCX

Resolution

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 CA 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 CA RC/Extract, CA Fast Check, CA Fast Load and CA RC/Update.

Create User Defined Referential Integrity between two tables

Two tables called TABLE1 and TABLE2 both share a key column "COLUMN1" defined with the same column type and length.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
 + -ssid:authid1:TABLE2
 + -ssid:authid1:TABLE1

Use the L - List (expand) command to expand the components of an NRS.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
 - -ssid:authid1:TABLE2
   Desc: Catalog derived relationship set                      Lvl RI Status
    1 authid1.TABLE2                                           1 ** NO RI
 - -ssid:authid1:TABLE1
   Desc: Catalog derived relationship set                      Lvl RI Status
    1 authid1.TABLE1                                           1 ** NO RI

RI Manager indicates that they are not related by RI.

Create a Primary key on the Parent table.

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.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
 - -DT32:authid1:TABLE2
   Desc: Catalog derived relationship set                      Lvl RI Status
    1 authid1.TABLE2                                           1 ** NO RI
 - -ssid:authid1:TABLE1
   Desc: Catalog derived relationship set                      Lvl RI Status
P     1 authid1.TABLE1                                           1 ** NO RI
******************************* BOTTOM OF DATA ********************************

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 ==> authid1 CMD NAME CREATOR KEY TYPE C tab1pk____________ BASLU02 P U

On the next screen choose which column or columns will be part of the PK with a "Pn", "n" being a seq number. For example "P1" would designate 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   ==> authid1
 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   ==> authid1
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.


Create the RI between the two tables.


PF3 back to the same RI Manager main screen. Now use the "C" line command on TABLE1 to create a child relationship with another table.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
 - -ssid:authid1:TABLE2
   Desc: Catalog derived relationship set                      Lvl RI Status
    1 authid1.TABLE2                                           1 ** NO RI
 - -ssid:authid1:TABLE1
   Desc: Catalog derived relationship set                      Lvl RI Status
C     1 authid1.TABLE1                                           1 ** 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 (Child  name => TABLE2) and when it is listed use the "S" line command to select it as below.

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

On the next screen the RI is ready to be created. 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  ==> authid1     Mode    ==> O
 Child  Name ==> TABLE2             Creator  ==> authid1     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 beneath TABLE1. The LVL column on screen for TABLE1 shows it is the first level , a Parent, and LVL for TABLE2 shows that it is a second level table....a CHILD.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
 - -ssid:BASLUYY:TABLE1
   Desc: Catalog derived relationship set                      Lvl RI Status
    1 authid1.TABLE1                                           1
    2 authid1.TABLE2                                           2 U    A

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

Use CA RC Extract for DB2 for z/os(RCX) 

CA RC Extract for DB2 for z/os(RCX) can be used to extract the data from the two tables in a referentially intact manner. 

RCX 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 as if the tables have DB2 RI available. Rather than outlining the steps to create a RC/EXTRACT Source Definition 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 Source Definition.

RCX refers to the relationship in the PLAN report below as a "RI/MANAGER RELATIONSHIP COLUMN1" which means that it is User Defined RI. If the RI was in fact DB2 system RI, RCX refers to this relationship as "DB2 RELATIONSHIP COLUMN1"

                           Display Mode is PLAN

_ STEP# 1         OBJECT: authid1.TABLE1

  REASON:
     THIS OBJECT WAS SELECTED AS THE STARTING OBJECT.
  ------------------------------------------------------------------
_ STEP# 2         OBJECT: authid1.TABLE2

  REASON:
     RI/MANAGER RELATIONSHIP COLUMN1 INDICATES THAT THIS OBJECT
      IS A CHILD OF authid1.TABLE1, PROCESSED IN
        STEP(S) 1.
  ------------------------------------------------------------------

Additional Information

"L" line command: List Key Columns


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.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
    1 authid1.TABLE1                                           1
L     2 authid1.TABLE2                                           2 U    A

The report .......

              RI Manager List Key Columns

Command ===>

Table  => TABLE2              > Creator => authid1   >
Parent => TABLE1              > Creator => authid1   >
Constraint name => COLUMN1                           >

Child Columns      ## Parent Columns     ##
------------------ -- ------------------ --
COLUMN1            1  COLUMN1            1
**************** BOTTOM OF DATA *****************

"V" line command: Verification Report

The "V" line command can be used to verify that the User Defined RI has been correctly stored on the product tables.

20.0   --------------- RI Manager Relationship Sets Management ---------------
Command ===>                                                  Scroll ===> CSR

Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid                     Include NRS ==> Y        RI only ==> N
Tbl Name ==> TABLE%                       Creator ==> authid1   >
  Nrs Name ==>                              Creator ==>            Share ==> A

Cm   Name                             Creator  Sh +---- NRS Last Update -----+
    1 authid1.TABLE1                                           1
V     2 authid1.TABLE2                                           2 U    A

and the report........Type of Verification ===> R and run the job online.

yy/mm/dd                   Computer Associates RI Manager
                                Verification Report

COMM      .........RD200 PRD ENVIRONMENT

Relationship: COLUMN1
Parent Name : TABLE1
Creator     : authid1
Child  Name : TABLE2
Creator     : authid1

PARENT AND CHILD TABLE VERIFICATION
----------------------------------
RIV015I: PARENT TABLE FOUND.
RIV018I: CHILD  TABLE FOUND.

PARENT KEY VERIFICATION
-----------------------
RIV010I: PARENT KEY OK.

FOREIGN KEY VERIFICATION
-----------------------
RIV024I: FOREIGN KEY OK.

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