Table Dependency

Products

RC Compare for DB2 for z/OS Bind Analyzer for DB2 for z/OS SQL-Ease for DB2 for z/OS SYSVIEW Performance Management Option for DB2 for z/OS Plan Analyzer for DB2 for z/OS Subsystem Analyzer for DB2 for z/OS Database Analyzer for DB2 for z/OS Fast Unload for DB2 for z/OS Fast Check for DB2 for z/OS Fast Index for DB2 for z/OS Rapid Reorg for DB2 for z/OS

Issue/Introduction

Description:

How can you use Plan Analyzer to generate a report listing all the affected DBRMs (not just plans or packages) identified by table?

Solution:

The Object Reporting option 9 from the PPADB2 main menu has the ability to report dependencies by Plan-DBRMs and/or Collection id-Packages.

You can determine table dependencies for Collid/Package by specifying an Object summary report (TYPE=S)

    r11.5   -----------   Table    Dependency (SUM)  ----------- 2008/12/12 10:18
                                                                 Line 1 of 158  >   
    Option   ===> O                  Report   ===> T    
    Object   ===> TSTSEG1            Creator  ===> *         Where ===> N    
    Plan     ===>                    DBRM     ===>           Type  ===> S <-------    
    Collid   ===> *                  Package  ===> *    
    ----------------------------------------------------------------- USERID
    Location ===> LOCAL              DB2 SSID ===> D81A    Version ===> V8R1M0
    CMD  TABLE              PLAN/COLLECTION    DBRM/PKG STMTNO    
    ____ TSTSEG1
    ____     Collection --> BIND_PARMV8_REOPT>
    ____                                       FXCBCNT    ____     Collection --> BIND_PARMV8_REOPT>    ____                                       FXCBCNT    ____     Collection --> BIND_PARMV8_REOPT>    ____                                       FXCBCNT    ____     Collection --> CITIBANK_TEST_COLL    ____                                       SQORDBY8    ____     Collection --> DRBCOLL

The summary report doesn't display SQL statements. Object Detail report (TYPE=D) is required in order to display all dependencies,DBRMs and SQL statements.

    r11.5   -----------   Table    Dependency (DET) ----------- 2008/12/12 10:31
                                                                 Line 1 of 3187  >     Option   ===> O                  Report   ===> T    
  Object   ===> TSTSEG1            Creator  ===> *         Where ===> N  
  Plan     ===>                    DBRM     ===>           Type  ===> D <----  
  Collid   ===> *                  Package  ===> *  
  ----------------------------------------------------------------- USERID   Location ===> LOCAL              DB2 SSID ===> D81A    Version ===> V8R1M0
  CMD  TABLE              PLAN/COLLECTION    DBRM/PKG       STMTNO    SQL STATEMENTS   
  ____ TSTSEG1  ____     Collection --> BIND_PARMV8_REOPT>  ____                                       FXCBCNT  ____                                                        60     SELECT  COUNT ( * )                                                                       INTO :HCOUNT                                                                       FROM PDDJC.TSTSEG1

In the above example TSTSEG1 is the name of the table.
BIND_PARMV8_REOPT is the name of the collection that is dependent on table TSTSEG1
FXCBCNT is the name the DBRM that is dependent on table TSTSEG1
SELECT COUNT ( * ) INTO :HCOUNT FROM PDDJC.TSTSEG1 is the statement dependent on table TSTSEG1

Environment

Release:
Component: PPADB2