How to quickly generate DDL to drop a relationship to a child table using RI/Manager for Db2 for z/OS.
RI relationships can be displayed using the RI/Manager utility.
Enter into RI/Manager from the Value Pack menu on the main products menu.
Specify the required search conditions on the page for the table that is required and use the EXPLODE command to display the RI structure.
20.0 --------------- RI Manager Relationship Sets Management ---------------
Command ===> Scroll ===> CSR
Specify Named Relationship Set Filter Criteria:
DB2 SSID ==> ssid Include NRS ==> N RI only ==> Y
Tbl Name ==> TABLE1 Creator ==> authid1 >
Nrs Name ==> Creator ==> Share ==> A
Cm Name Creator Sh +---- NRS Last Update -----+
- -ssid:authid1:TABLE1
Desc: Catalog derived relationship set Lvl RI Status
1 authid1.TABLE1 1
2 authid1.TABLE1 (1+) 2 S
3 authid1.TABLE2 2 S
4 authid1.TABLE2 (3) 2 U A
The structure above shows a set of two tables where TABLE1 and TABLE2 are associated with each other via two RI relationships,
one being a SYSTEM(DB2 created) and the other being a USER(Defined by RI/Manager).
Using the "L" line command on line 3 to see that relationship this is displayed:
Table => TABLE2 > Creator => authid1 >
Parent => TABLE1 > Creator => authid1 >
Constraint name => COL1TO2 >
Child Columns ## Parent Columns ##
------------------ -- ------------------ --
COLUMN1 1 COLUMN1 1
**************** BOTTOM OF DATA *****************
When the "L" line command is used on line 4 this is displayed:
Table => TABLE2 > Creator => authid1 >
Parent => TABLE1 > Creator => authid1 >
Constraint name => COLUMN1 >
Child Columns ## Parent Columns ##
------------------ -- ------------------ --
COLUMN1 1 COLUMN1 1
**************** BOTTOM OF DATA *****************
This information indicates that there is a COL1TO2 and a COLUMN1 relationship between the two tables.
So, having decided to drop both of them , there are a number of line commands available at each line above.
One of them is:
D - Drop Child. Drop the child table designation, severing the RI
relationship (the table is NOT dropped).
The statement that is generated is different depending on the type of relationship it is, DB2 created or RI/Manager created.
Having entered "D" on line 3 above this is the code generated for the SYSTEM RI(DB2 created) relationship COL1TO2.
-- DROP SYSTEM DEFINED CHILD.
ALTER TABLE authid1."TABLE2"
DROP FOREIGN KEY "COL1TO2" ;
Having entered "D" on line 4 above this is the code generated for the USER DEFINE RI(created by RI/Manager) relationship COLUMN1.
-- DROP USER DEFINED CHILD.
DELETE
FROM PTI.PTPRI_SYSRELS_0100
WHERE
CREATOR = 'authid1'
AND TBNAME = 'TABLE2'
AND REFTBNAME = 'TABLE1'
AND REFTBCREATOR = 'authid1'
AND RELNAME = 'COLUMN1'
;
Both of these statements are then able to be executed using Database Management for DB2 for z/OS Batch Processor.
The ALTER command above obviously is DDL for Db2 to process. The DELETE SQL command above will update the product table PTI.PTPRI_SYSRELS_0100 which is ONLY recognized by RI/Manager and some other products that form part of the Database Management for Db2 for Z/OS toolset such as Fast Check, RC/Extract and Fast Load.
RI Manager USER Defined Relationships are not recognized by any other products outside the Database Management for DB2 for z/OS toolset.