RC/Extract : DB2 System RI and User RI Selection or Exclusion.
Article ID: 11115
Updated On:
Products
Issue/Introduction
RC/Extract for DB2 for z/os (RCX) is able to use both DB2 Defined System RI and RI created by RI/Manager called "User Defined RI". User Defined RI is only recognised by products that are part of the Database Management for DB2 for z/os tools including RCX and Fast Check to name two.
When a table has both DB2 system defined unique key and a user defined key, does RCX allow the user to select or exclude the system RI or the user RI?
Environment
Db2 for Z/os
Release: R20
Resolution
Yes, RCX will allow the user to select or exclude the system RI, the user RI or both under Relationship Display Mode.
If RCX is currently in object mode, issue the MODESW command from the command line during source definition creation or update into the source definition and issue the command.
Here is the display when in relationship mode:
---------------- RC/EXTRACT Source Definition --------------- yyyy/mm/dd hh:mm
Command ===> Scroll ===> CSR
PLAN: Display plan report MODESW: Object Display Mode
Source Definition: authid1.USERDEF Source SSID: ssid
Description ===> USER DEFINED VS SYSTEM R Share Option ===> U
Start Object Parameters: -----------------------------------------------------
Sampling Ratio ==> ____ of ____ Initial No. Rows ==> _______
WHERE Clause ==> N ________
Key File Input ==> N ________________________________________________________
TDM MAPCSV File => N ________________________________________________________
Registry SSID: ssid ------ Display Mode: Relationship -------------- authid1
CMD STATUS PARENT CHILD RELNAME CP PC
___ SELECT TABLE1 TABLE1 COLU1SPS Y N
___ SELECT TABLE1 TABLE2 COL1TO2 Y N
___ SELECT TABLE1 TABLE2 COLUMN1 Y N
******************************* BOTTOM OF DATA ********************************
Use the "X" line command(Exclude) on the relationship you don't want in the CMD column. EXCLUD shows in the STATUS column.
The type of relationship that these are can be seen by using the PLAN command and then the EXPLODE command.
Enter in Command ===> PLAN;EXPLODE
The report below shows that these two tables have :
DB2 RELATIONSHIP COLU1SPS
DB2 RELATIONSHIP COL1TO2
RI/MANAGER RELATIONSHIP COLUMN1
--------------- RC/EXTRACT Extract Plan Report -------------- yyyy/mm/dd hh:mm
Command ===> Scroll ===> CSR
MODESW: Toggle Display Mode
Source SSID: ssid
Source Definition: authid1.USERDEF
Description: USER DEFINED VS SYSTEM R
Registry SSID: ssid -------------------------------------------- authid1
Display Mode is PLAN
_ STEP# 1 OBJECT: authid1.TABLE1 .
.
REASON: .
THIS OBJECT WAS SELECTED AS THE STARTING OBJECT. .
------------------------------------------------------------------ .
_ STEP# 2 OBJECT: authid1.TABLE1 GOTO 2 .
.
REASON: .
DB2 RELATIONSHIP COLU1SPS INDICATES THAT THIS OBJECT .
IS A PARENT OF authid1.TABLE1, WHICH WAS PROCESSED IN
STEP(S) 1,2,6,7.
.
THE CP (CHILD-TO-PARENT) TRAVERSAL RULE FOR THIS
RELATIONSHIP WAS SET TO Y(ES).
------------------------------------------------------------------ .
_ STEP# 3 OBJECT: authid1.TABLE1 GOTO 3 .
.
REASON: .
DB2 RELATIONSHIP COLU1SPS INDICATES THAT THIS OBJECT .
IS A CHILD OF authid1.TABLE1, PROCESSED IN
STEP(S) 1,3.
------------------------------------------------------------------ .
_ STEP# 4 OBJECT: authid1.TABLE2 .
.
REASON: .
DB2 RELATIONSHIP COL1TO2 INDICATES THAT THIS OBJECT .
IS A CHILD OF authid1.TABLE1, PROCESSED IN
STEP(S) 1,3.
------------------------------------------------------------------ .
_ STEP# 5 OBJECT: authid1.TABLE2 .
.
REASON: .
RI/MANAGER RELATIONSHIP COLUMN1 INDICATES THAT THIS OBJECT .
IS A CHILD OF authid1.TABLE1, PROCESSED IN
STEP(S) 1,3.
------------------------------------------------------------------ .
_ STEP# 6 OBJECT: authid1.TABLE1 GOTO 2 .
.
REASON: .
RI/MANAGER RELATIONSHIP COLUMN1 INDICATES THAT THIS OBJECT .
IS A PARENT OF authid1.TABLE2, WHICH WAS PROCESSED IN
STEP(S) 4.
.
THE CP (CHILD-TO-PARENT) TRAVERSAL RULE FOR THIS
RELATIONSHIP WAS SET TO Y(ES).
------------------------------------------------------------------ .
_ STEP# 7 OBJECT: authid1.TABLE1 GOTO 2 .
.
REASON: .
DB2 RELATIONSHIP COL1TO2 INDICATES THAT THIS OBJECT .
IS A PARENT OF authid1.TABLE2, WHICH WAS PROCESSED IN
STEP(S) 5.
.
THE CP (CHILD-TO-PARENT) TRAVERSAL RULE FOR THIS
RELATIONSHIP WAS SET TO Y(ES).
------------------------------------------------------------------ .
For this extract only the RI/MANAGER RELATIONSHIP COLUMN1 is required so the 'X' line command is used on the other two to exclude them.
---------------- RC/EXTRACT Source Definition --------------- yyyy/mm/dd hh:mm
Command ===> Scroll ===> CSR
PLAN: Display plan report MODESW: Object Display Mode
Source Definition: authid1.USERDEF Source SSID: ssid
Description ===> USER DEFINED VS SYSTEM R Share Option ===> U
Start Object Parameters: -----------------------------------------------------
Sampling Ratio ==> ____ of ____ Initial No. Rows ==> _______
WHERE Clause ==> N ________
Key File Input ==> N ________________________________________________________
TDM MAPCSV File => N ________________________________________________________
Registry SSID: ssid ------ Display Mode: Relationship -------------- authid1
CMD STATUS PARENT CHILD RELNAME CP PC
x__ SELECT TABLE1 TABLE1 COLU1SPS Y N
x__ SELECT TABLE1 TABLE2 COL1TO2 Y N
___ SELECT TABLE1 TABLE2 COLUMN1 Y N
******************************* BOTTOM OF DATA ********************************
ENTER
---------------- RC/EXTRACT Source Definition --------------- yyyy/mm/dd hh:mm
Command ===> Scroll ===> CSR
PLAN: Display plan report MODESW: Object Display Mode
Source Definition: authid1.USERDEF Source SSID: ssid
Description ===> USER DEFINED VS SYSTEM R Share Option ===> U
Start Object Parameters: -----------------------------------------------------
Sampling Ratio ==> ____ of ____ Initial No. Rows ==> _______
WHERE Clause ==> N ________
Key File Input ==> N ________________________________________________________
TDM MAPCSV File => N ________________________________________________________
Registry SSID: ssid ------ Display Mode: Relationship -------------- authid1
CMD STATUS PARENT CHILD RELNAME CP PC
___ EXCLUD TABLE1 TABLE1 COLU1SPS Y N
___ EXCLUD TABLE1 TABLE2 COL1TO2 Y N
___ SELECT TABLE1 TABLE2 COLUMN1 Y N
******************************* BOTTOM OF DATA ********************************
They both show the STATUS of EXCLUD .
Enter Command ===> PLAN;EXPLODE
The report below now shows that for this extract only the RI/MANAGER RELATIONSHIP COLUMN1 will be used.
--------------- RC/EXTRACT Extract Plan Report -------------- yyyy/mm/dd hh:mm
Command ===> Scroll ===> CSR
MODESW: Toggle Display Mode
Source SSID: ssid
Source Definition: authid1.USERDEF
Description: USER DEFINED VS SYSTEM R
Registry SSID: ssid -------------------------------------------- authid1
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
Feedback
