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

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

Description:

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?

Solution:

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

The actual queries that are being executed are:

SELECT A.NAME, A.CREATOR, A.TYPE, A.DBNAME, A.TSNAME, A.LOCATION, A.TBCREATOR, A.TBNAME
FROM SYSIBM.SYSTABLES A
WHERE A.NAME = ? AND A.CREATOR = ? AND A.TYPE IN ('G','M','T','X')
WITH UR ;
   
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.BNAME, A.BCREATOR, A.BTYPE, A.DNAME
FROM SYSIBM.SYSPLANDEP A
WHERE A.BCREATOR = ? AND A.BNAME = ? AND A.BTYPE IN (?, ?)
WITH UR ;
   
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.DBNAME, A.IMPLICIT, A.PARTITIONS, ''
FROM SYSIBM.SYSTABLE A
WHERE A.NAME = ? AND A.DBNAME = ?
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.TBNAME, A.TBOWNER, A.NAME, A.OWNER
FROM SYSIBM.SYSTRIGGERS A
WHERE A.TBOWNER = ? AND A.TBNAME = ?
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.DNAME, A.DCREATOR, A.DTYPE
FROM SYSIBM.SYSVIEWDEP A
WHERE B.CREATOR = ? AND BNAME = ?
WITH UR ;
 
SELECT A.NAME, A.CREATOR, A.TBNAME A.TBCREATOR, A.UNIQUERULE, A.DBNAME, B. PARITION, ''
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 ;

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