RC/Migrator : How to manipulate the Referential Integrity of a table being Altered or Templated
Article ID: 234434
Updated On:
Products
RC/Migrator for DB2 for z/OS
Issue/Introduction
Existing Referential Integrity relationships can be manipulated while Altering or Templating a table from the ALTER or TEMPLATE screen before the Strategy is analysed and executed.
Environment
DB2 for Z/OS
Release : R20
Resolution
While altering or templating a table the existing referential integrity relationships can be manipulated using the Forgn Key => U field(Update).
For example, table TBPROJECT has three relationships.
ROPTBALX ---------------- CA - Table Alter --------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TBPROJECT > Creator => AUTHID1 > Comm/Lab => N
Database => DBCORPC Editproc => Data Cap => NONE
Tablespace => TSPROJ Validproc => OBID =>
Partitioning > NO (TS Parts:NONE) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 157/-3,891 Forgn Key => U Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 PROJ_NO CHAR 6 N _ MIXED 1
___ 2 PROJ_NAME CHAR 20 N _ MIXED __
___ 3 PROJ_DEPT CHAR 6 N _ MIXED __ Y
___ 4 PROJ_MGR CHAR 6 Y N MIXED __ Y
___ 5 PROJ_START DATE 4 Y N _____ __
___ 6 PROJ_END DATE 4 Y N _____ __
___ 7 PROJ_DESC CHAR 100 Y N MIXED __
___ 8 PROJ_SUPERIOR CHAR 6 Y N MIXED __ Y
When "U" is entered into the Forgn Key field the existing relationships are displayed:
ROPTFREL --------- Table Referential Constraints --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Foreign Key Referential Constraints For Table => TBPROJECT >
SSID: ssid ---------------------------------------------------------- authid1
RELATION +---- PARENT TABLE -------+ DELETE E +-------- COLUMN ---------+
O NAME CREATOR NAME RULE N NAME TYPE
_ ________ ________ __________________ _________ Y <== CONSTRAINT CREATION
_ PDEPT AUTHID1 TBDEPT CASCADE Y PROJ_DEPT CHAR
_ PEMP AUTHID1 TBEMP SET NULL Y PROJ_MGR CHAR
_ PPROJ AUTHID1 TBPROJECT CASCADE Y PROJ_SUPERIOR CHAR
******************************* BOTTOM OF DATA ********************************
Note that this is the RI information that was retrieved from the catalog and stored in the Strategy.
It shows the details of relationships including the DELETE RULE.
The above relationships can be deleted or reinstated during the edit of the table inside the strategy.
NOTE: This has no impact on the catalog till the Strategy is analysed and executed.
ROPTFREL --------- Table Referential Constraints --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Foreign Key Referential Constraints For Table => TBPROJECT >
SSID: ssid ---------------------------------------------------------- authid1
RELATION +---- PARENT TABLE -------+ DELETE E +-------- COLUMN ---------+
O NAME CREATOR NAME RULE N NAME TYPE
_ ________ ________ __________________ _________ Y <== CONSTRAINT CREATION
_ PDEPT AUTHID1 TBDEPT CASCADE Y PROJ_DEPT CHAR
_ PEMP AUTHID1 TBEMP SET NULL Y PROJ_MGR CHAR
D PPROJ AUTHID1 TBPROJECT CASCADE Y PROJ_SUPERIOR CHAR
******************************* BOTTOM OF DATA ********************************
The above example is where the "D" line command will delete the PPROJ relationship.
ROPTFREL --------- Table Referential Constraints --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> COMPARE SCROLL ===> CSR
Foreign Key Referential Constraints For Table => TBPROJECT >
SSID: ssid ---------------------------------------------------------- authid1
RELATION +---- PARENT TABLE -------+ DELETE E +-------- COLUMN ---------+
O NAME CREATOR NAME RULE N NAME TYPE
_ ________ ________ __________________ _________ Y <== CONSTRAINT CREATION
_ PDEPT AUTHID1 TBDEPT CASCADE Y PROJ_DEPT CHAR
_ PEMP AUTHID1 TBEMP SET NULL Y PROJ_MGR CHAR
******************************* BOTTOM OF DATA ********************************
The COMPARE command in the COMMAND line will show a report of the changes made:
ROPTFREL --------- Table Referential Constraints --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Foreign Key Referential Constraints For Table => TBPROJECT >
SSID: ssid ---------------------------------------------------------- authid1
RELATION +---- PARENT TABLE -------+ DELETE E
O NAME CREATOR NAME RULE N
_ ________ ________ __________________ _________ Y <== CONSTRAINT CREATION
_ PDEPT AUTHID1 TBDEPT CASCADE Y
O PDEPT AUTHID1 TBDEPT CASCADE Y
_ PEMP AUTHID1 TBEMP SET NULL Y
O PEMP AUTHID1 TBEMP SET NULL Y
_ <-- CONSTRAINT DELETED -->
O PPROJ AUTHID1 TBPROJECT CASCADE Y
******************************* BOTTOM OF DATA ********************************
To reinstate the deleted relationship above the "U" line command is used.
ROPTFREL --------- Table Referential Constraints --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Foreign Key Referential Constraints For Table => TBPROJECT >
SSID: ssid ---------------------------------------------------------- authid1
RELATION +---- PARENT TABLE -------+ DELETE E
O NAME CREATOR NAME RULE N
_ ________ ________ __________________ _________ Y <== CONSTRAINT CREATION
_ PDEPT AUTHID1 TBDEPT CASCADE Y
O PDEPT AUTHID1 TBDEPT CASCADE Y
_ PEMP AUTHID1 TBEMP SET NULL Y
O PEMP AUTHID1 TBEMP SET NULL Y
U <-- CONSTRAINT DELETED -->
O PPROJ AUTHID1 TBPROJECT CASCADE Y
******************************* BOTTOM OF DATA ********************************
A screen like this appears where the deleted foreign key can be reinstated.
ROPTFK5 ------------ Table Foreign Key Detail ------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Parent Table Name => TBPROJECT > Creator => AUTHID1 >
SSID: ssid -------------------------------------------------------------------
Relation Name => PPROJ > Delete Rule => CASCADE Enforce => Y
CMD FK# COLUMN NAME COLTYPE SIZE N FLDPROC PARENT COLUMN
___ <== ENTER 'I' TO OBTAIN A CHILD COLUMN LIST
******************************* BOTTOM OF DATA ********************************
...............................................................................
Index Information For Parent Table TBPROJECT
CMD INDEX NAME COLTYPE SIZE N FLDPROC PRIMKEY#
_ AUTHID1.PIXTBPROJECT - Primary
IX COLS: PROJ_NO CHAR 6 N 1
******************************* BOTTOM OF DATA ********************************
If the objective of the strategy was to remove that one relationship, the alteration can be
checked when the table edit is completed using the SUMMARY command in the COMMAND line of the main
strategy edit screen:
-------------- RC/M Alteration Strategy Services -------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SUMMARY SCROLL ===> CSR
Strategy ===> PROJECT Description ===>
Creator ===> AUTHID1 Share Option ===> U (U,Y,N,X,L) SSID ===> ssid
--------------------------------------------------------------------- AUTHID1
Option => (A,C,T,D) Object => (SG,DB,TS,T,I,V,S,A,TG,SQ,PR,UF)
Name => TBPROJECT > Creator => AUTHID1 > Where ==> N
CMD OBJECT TYPE NAME CREATOR PROCESS
_ TABLE TBPROJECT AUTHID1 ALTER
******************************* BOTTOM OF DATA ********************************
RMARPT 20.0 ------------ RC/M Alter Report Output ------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
------------------------------------------------------------- USER ID: AUTHID1
++++++++++++++++++++++++++++ ALTERED OBJECTS +++++++++++++++++++++++++++++
Table Changes for:
Creator: AUTHID1
Name: TBPROJECT
------------ Foreign Key Changes ---------------------
Attribute Status Value
------------- ----------- ----+----1----+----2----+----3--
RELNAME Currently: PPROJ
Changed To: (DELETED)
******************************** BOTTOM OF DATA *******************************
Additional Information
Feedback
Yes
No
Powered by
