Table Dependency

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

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.

  1. 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
  2. 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