How to produce a formatted RI Report of Child records belonging to a given parent table in RC/Query

Products

• RC/Query for DB2 for z/OS

Issue/Introduction

Produce a formatted Referential Integrity Report of Children belonging to a given parent table using RC/Query for Db2 for z/OS (RCQ).

Resolution

RC/Query has Batch Reports. The BR command line command can be used. This command will access the batch report facility in RC/Query.

After the "RC/Q Batch Specification" screen is completed this selection screen is displayed when the Db2 Object is "RI". There are a number of
reports to choose from apart from the RI reports.

Enter Report Selection Criteria (spaces for EQF Selection suppress EQFs)
 
Rpt. Selection: DB2 Object => RI       Option  => *
EQF  Selection: EQF Name   =>          Creator =>          Where => N
Loc: LOCAL ---------- SSID: SSID ----------userid -                         >
       Enter 'S
REPORT/QUERY     DESCRIPTION                    ITEM NAME          CREATOR
_ REF INT  (RI) INFORMATION ON REF. INTEGRITY  __________________ ________
 _ ALL      (A)  REF INT - ALL RELATIONSHIPS    __________________ ________
 _ ALLKEY   (AK) ALL KEY COLUMNS                __________________ ________
 _ CHILD    (C)  REF INT - CHILD RELATIONSHIPS  __________________ ________
 _ FRGNKEY  (FK) FOREIGN KEY COLUMNS            __________________ ________
 _ LIST     (C)  REF INT - LIST RELATIONSHIPS   __________________ ________
 S PARENT   (P)  REF INT - PARENT RELATIONSHIPS DEPT______________ DSN8810_
 _ PRIMKEY  (PK) PRIMARY KEY COLUMNS            __________________ ________
 _ SPACESET (SS) LIST SPACESETS                 __________________ ________
 _ TABLES   (T)  CHECK PENDING TABLES           __________________ ________
 _ TBLSPACE (TS) CHECK PENDING TABLESPACES      __________________ ________

There is a PARENT report among other RI reports available. The screen selection above is where we enter an "S" and the ITEM NAME and CREATOR of the parent table.
Press enter and PF3 to submit in batch.

The Batch Report looks like this.

FOR REQ:   OBJECT=> RI                   OPTION=> P
             ITEM=> DEPT                CREATOR=> DSN81110 WHERE=> N
        QUALIFIER=> *                   GRANTOR=> *
         DB2 SSID=> ssid               LOCATION=> LOCAL
RQE00015: NORMAL COMPLETION
RQE01000: INPUT FILE: SYSIN, AT END; EXECUTION COMPLETED
yyyy/mm/dd  hh:mm
                 DB2 SSID:ssid     ------- RC/Q R/I PARENT RELATIONSHIPS ------
LOCATION: LOCAL
PARENT TABLE        PCREATOR  RULENAME  CHILD TABLE         CCREATOR  DELRULE   COLCOUNT PRNTOBID CHLDOBID ENFORCED  CHKEDATA  REL CRTD
DEPT                DSN81110  DEPTNO    PROJ                DSN81110  RESTRICT        1       26       26     Y         I       DB2v11
                              RDD       DEPT                DSN81110  CASCADE         1       52       52     Y         I       DB2v11
                              RED       EMP                 DSN81110  SET NULL        1       19       19     Y         I       DB2v11

Additional Information

The display of the referential integrity that is currently defined in Db2 in a report format can assist in the use of other products such as RC/Migrator and RC/Extract.
|Knowledge of which RI have been defined can give information that will help make correct object selections for a migration or an extract. Knowledge of the RI could also help with Alterations.