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?
Article ID: 53374
Updated On:
Products
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
Feedback
