RC/Query for DB2 for Z/OS :What SQL is executed and which catalog tables are accessed as a result of generating a Table Drop Impact Report?
search cancel

RC/Query for DB2 for Z/OS :What SQL is executed and which catalog tables are accessed as a result of generating a Table Drop Impact Report?

book

Article ID: 53374

calendar_today

Updated On:

Products

RC/Query for DB2 for z/OS

Issue/Introduction

The SQL that is generated when requesting a report in RC/Query can be displayed by issuing the command SQL for the Primary command. Issuing the SQL command for a Drop Impact report issued a query accessing the SYSIBM.SYSTABLES and did not display SQL for any other catalog tables which would have been accessed. What catalog tables are accessed and what is the SQL that is executed?

 

Environment

Release: R20
Component: RCQ

Resolution

A simple RC/Query report such as a Table List (T/L) will use the SQL presented by the SQL command as displayed.

More complex reports such as a Table Drop Impact generate several queries across many modules to create the report, not a single query. So the SQL displayed is generally for the initial query being done.

The catalog tables that are being accessed are:

SYSIBM.SYSTABLES
SYSIBM.SYSPACKDEP
SYSIBM.SYSPLANDEP
SYSIBM.SYSTABLESPACE
SYSIBM.SYSAUXRELS
SYSIBM.SYSTRIGGERS
SYSIBM.SYSRELS
SYSIBM.SYSVIEWDEP
SYSIBM.SYSSYNONYMS
SYSIBM.SYSINDEXES
SYSIBM.SYSINDEXPART

 

The actual queries that are being executed are:

SELECT  DISTINCT A.BNAME , A.BQUALIFIER , A.BTYPE , A.DCOLLID , A.DNAME
   FROM SYSIBM.SYSPACKDEP A
  WHERE A.BQUALIFIER = ?
    AND A.BNAME = ?
    AND A.BTYPE IN ( ? , ? ) WITH UR

SELECT  A.NAME , A.CREATOR , A.TBNAME , A.TBCREATOR , A.UNIQUERULE ,
        A.DBNAME , B.PARTITION , ' '
   FROM SYSIBM.SYSINDEXES A , SYSIBM.SYSINDEXPART B
  WHERE A.CREATOR = B.IXCREATOR
    AND A.NAME = B.IXNAME
    AND A.TBNAME = ?
    AND A.TBCREATOR = ?
    AND B.PARTITION IN ( 0 , 1 ) WITH UR

SELECT  A.NAME , A.CREATOR , A.TYPE , A.DBNAME , A.TSNAME , A.LOCATION ,
        A.TBCREATOR , A.TBNAME , A.VERSIONING_SCHEMA ,
        A.VERSIONING_TABLE , A.ARCHIVING_SCHEMA , A.ARCHIVING_TABLE
   FROM SYSIBM.SYSTABLES A
  WHERE A.NAME = ?
    AND A.CREATOR = ?
    AND A.TYPE IN ( 'G' , 'M' , 'T' , 'X' , 'C' , 'P' , 'H' , 'R' ) WITH
        UR

SELECT  A.NAME , A.CREATOR , A.DBNAME , A.IMPLICIT , A.PARTITIONS , ' '
   FROM SYSIBM.SYSTABLESPACE A
  WHERE A.NAME = ?
    AND A.DBNAME = ? WITH UR

SELECT  A.CREATOR , A.TBNAME , A.RELNAME , A.REFTBNAME , A.REFTBCREATOR
   FROM SYSIBM.SYSRELS A
  WHERE ( A.CREATOR = ?
    AND A.TBNAME = ? )
     OR ( A.REFTBCREATOR = ?
    AND A.REFTBNAME = ? ) WITH UR

SELECT  A.BNAME , A.BCREATOR , A.BTYPE , A.DNAME
   FROM SYSIBM.SYSPLANDEP A
  WHERE A.BCREATOR = ?
    AND A.BNAME = ?
    AND A.BTYPE IN ( ? , ? ) WITH UR

SELECT  A.NAME , A.CREATOR , A.TYPE , A.DBNAME , A.TSNAME , A.LOCATION ,
        A.TBCREATOR , A.TBNAME , A.VERSIONING_SCHEMA ,
        A.VERSIONING_TABLE , A.ARCHIVING_SCHEMA , A.ARCHIVING_TABLE
   FROM SYSIBM.SYSTABLES A
  WHERE A.TYPE = 'A'
    AND A.TBNAME = ?
    AND A.TBCREATOR = ? WITH UR

SELECT  A.BNAME , A.BCREATOR , A.DNAME , A.DCREATOR , A.DTYPE
   FROM SYSIBM.SYSVIEWDEP A
  WHERE BCREATOR = ?
    AND BNAME = ? WITH UR

SELECT  A.NAME , A.CREATOR , A.TYPE , A.DBNAME , A.TSNAME , C.IMPLICIT ,
        B.TBOWNER , B.TBNAME
   FROM SYSIBM.SYSTABLES A , SYSIBM.SYSAUXRELS B , SYSIBM.SYSTABLESPACE
        C
  WHERE A.NAME = B.AUXTBNAME
    AND A.CREATOR = B.AUXTBOWNER
    AND A.TYPE = 'X'
    AND A.TSNAME = C.NAME
    AND A.DBNAME = C.DBNAME
    AND B.TBNAME = ?
    AND B.TBOWNER = ? WITH UR

SELECT  A.NAME , A.CREATOR , A.TBNAME , A.TBCREATOR
   FROM SYSIBM.SYSSYNONYMS A
  WHERE TBCREATOR = ?
    AND TBNAME = ? WITH UR

SELECT  A.TBNAME , A.TBOWNER , A.NAME , A.SCHEMA
   FROM SYSIBM.SYSTRIGGERS A
  WHERE A.TBOWNER = ?
    AND A.TBNAME = ? WITH UR

SELECT  A.NAME , A.CREATOR , A.TYPE , A.DBNAME , A.TSNAME , A.LOCATION ,
        A.TBCREATOR , A.TBNAME , A.VERSIONING_SCHEMA ,
        A.VERSIONING_TABLE , A.ARCHIVING_SCHEMA , A.ARCHIVING_TABLE
   FROM SYSIBM.SYSTABLES A
  WHERE A.DBNAME = ?
    AND A.TSNAME = ?
    AND A.TYPE IN ( 'G' , 'M' , 'T' , 'X' , 'C' , 'P' , 'H' , 'R' ) WITH
        UR

The host variables used will be supplied for each query based on the data that was input on the initial Drop Impact screen.

Additional Information

Table Reports