Extract a referentially intact set of data from tables which do not have Db2 RI defined
Article ID: 54344
Updated On:
Products
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.
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 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-Broadcom tools
but, it is recognized by some Broadcom 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 "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 -----+
- -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
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____________ authid1 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 RC/Extract for Db2 for z/OS (RCX)
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 .........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.
Extract Reference Guide and User Guide.
Feedback
