How to produce a formatted RI Report of Child records belonging to a given parent table?

Document ID : KB000024397
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

How do I get a formatted Referential Integrity Report of Children belonging to a given parent table?

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.

Answer:

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=> DSN8810  WHERE=> N               
        QUALIFIER=> *                   GRANTOR=> *                 
         DB2 SSID=> SSID               LOCATION=> LOCAL            
RQE00015: NORMAL COMPLETION                                                                    
RQE01000: INPUT FILE: SYSIN, AT END; EXECUTION COMPLETED                                                   
2009/12/01  00:07                                                                                           Page      1.1
                 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 C
DEPT             DSN8810   DEPTNO    PROJ                DSN8810   RESTRICT         1       26       26     Y         I
                           RDD       DEPT                DSN8810   CASCADE          1       52       52     Y         I
                           RED       EMP                 DSN8810   SET NULL         1       19       19     Y         I
******************************************************************************************* END OF REPORT ***********************