RC/Query for DB2 for Z/OS : Find and display Table Check constraints
Article ID: 235155
Updated On:
Products
RC/Query for DB2 for z/OS
Issue/Introduction
RC/Query is able to report on DB2 table check constraints so that RC/Migrator or RC/Update is then able to manipulate them using an ALTERATION strategy.
Environment
DB2 for Z/OS
Release : R20
Resolution
In order to find tables which have CHECK CONSTRAINTS defined an RC/Query EQF can be used.
A "Y" in the Where => field
RQM 20.0 ---------------- RC/Q Main Menu --------------- yyyy/mm/dd hh:mm
COMMAND ===>
DB2 Object ===> t Option ===> l Where => Y
Item Name ===> > Creator ===> AUTHID1 >
Qualifier ===> * > Grantor ===> * >
ACM ==> OFF ACMID ==> AUTHID1 ------------------- AUTHID1
Location ===> LOCAL DB2 SSID ===> ssid DB2 Version => 121M500
The EQF screen is displayed....
A WHERE Clause is used to find tables where the CHECKS column has a positive value.
As the CHECKS column is #39 in the displayed list of columns this syntax can be entered:
and :39 > 0
When ENTER is pressed RC/Query converts it to "AND A.CHECKS > 0"
20.0 ----------------- SQL Selection Panel ----------------- yyyy/mm/dd hh:mm
Command ==> SCROLL ===> CSR
Name ==> TEMP Share => N Default => N
Description => Panel : RQTL View SQL => N
Confirm Replace ==> Y Userid : AUTHID1
Where Clause:
01 AND A.CHECKS > 0
PF3 back to get the list of tables found.
In this sample there are 6 tables found matching this predicate. I want to look at TABLE1.
RQTL 20.0 --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => S TEMP
Table Name ===> * > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 6 >
CMD TABLE NAME CREATOR DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
________ CONSULTANT AUTHID1 DBCORP1 TSTEST 15 N/A
________ CONSULTANT1 AUTHID1 DBCORP1 TSTEST 15 N/A
________ DSN_STATEMENT_CAC> AUTHID1 PLANDB DSNR1JYT 93 N/A
________ DSN_VIRTUAL_INDEX> AUTHID1 PLANDB DSNRVIRT 150 N/A
________ TABLE1 AUTHID1 DB1 TS1 18 N/A
________ TNCC011_CLM_NBR AUTHID1 ODNC001 SNCPC01 255 0
******************************* BOTTOM OF DATA ********************************
PF11 to the right till the CHECKS column is seen:
RQTL 20.0 --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => S TEMP
Table Name ===> * > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 6 < >
CMD TABLE NAME PCTROWCOMP CHECKS CHECKRID5B ENCOD_ID
________ CONSULTANT N/A 1 E
________ CONSULTANT1 N/A 1 E
________ DSN_STATEMENT_CAC> N/A 1 U
________ DSN_VIRTUAL_INDEX> N/A 146 U
________ TABLE1 N/A 3 E
________ TNCC011_CLM_NBR 0 1 E
******************************* BOTTOM OF DATA *******************************
Use the T/CK command combination on the intended table.
RQTL 20.0 --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => S TEMP
Table Name ===> * > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 6 <
CMD TABLE NAME STATS_FEEDBACK REGENERATETS
________ CONSULTANT Y 2020-05-12-00.11.29.983435
________ CONSULTANT1 Y 2020-05-12-00.11.30.473501
________ DSN_STATEMENT_CAC> Y 2020-09-16-01.18.22.946717
________ DSN_VIRTUAL_INDEX> Y 2020-09-18-02.39.56.198310
T CK____ TABLE1 Y 2021-03-16-01.37.26.513384
________ TNCC011_CLM_NBR Y 2021-03-02-00.02.19.563839
and the next screen displayed is....
RQTCK 20.0 --------- RC/Q Table Check Constraint --------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> CK Where => N
Table Name ===> TABLE1 > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 4 >
CMD TBNAME TBOWNER CHECKNAME
________ TABLE1 AUTHID1
________ C_RETCCR
________ C_TIECCR
________ C_TYDCCR
******************************* BOTTOM OF DATA ********************************
The CHECKNAME column can be seen.
PF11 one screen...the CHECKCOND can now be seen. As the count above indicated there are three for this table.
RQTCK 20.0 --------- RC/Q Table Check Constraint --------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> CK Where => N
Table Name ===> TABLE1 > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 4 < >
CMD TBNAME CHECKCOND
________ TABLE1
________ RETCCR IN ( 'Y' , 'N' )
________ TIECCR IN ( 'Y' , 'N' )
________ TYDCCR IN ( 'C' , 'I' )
******************************* BOTTOM OF DATA ********************************
The T/CE command combination can also be used to display only one on screen at a time.
RQTCK 20.0 --------- RC/Q Table Check Constraint --------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> CK Where => N
Table Name ===> TABLE1 > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -AUTHID1 - LINE 1 OF 4 >
CMD TBNAME TBOWNER CHECKNAME
T CE____ SNHINDT0 AUTHID1
________ C_RETCCR
________ C_TIECCR
________ C_TYDCCR
and the next screen is displayed....
RQTCE 20.0 ---------- RC/Q Table Check Details ----------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> CE Where => N
Table Name ===> TABLE1 > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - FRAME 1 OF 3
********************************* TOP OF DATA *********************************
CMD: ________
TBNAME : TABLE1
:
TBOWNER : AUTHID1
:
CHECKNAME : C_RETCCR
:
CREATOR : AUTHID1
:
CHECKCOND : RETCCR IN ( 'Y' , 'N' )
--------------- END OF DATA ITEM ---------------
Then PF7/PF8 through the check constraints one by one.
If the details of a CHECK CONSTRAINT need to be ALTERED.....see knowledgebase article:
RC/Migrator for DB2 for Z/OS: Delete an existing check constraint on a table
Additional Information
Feedback
Yes
No
Powered by
