When you’re considering dropping and re-creating a table, view, or MQT, it is critical to ensure that you consider any dependencies. When dropping a table, any MQTs that rely on it will be dropped, and views marked inoperative. With multiple levels, it can be difficult to Identify everything. Recursive SQL to the rescue!
Purpose
To list all MQTs and views dependent on a single table, MQT, or view. This SQL does NOT look at foreign keys to determine child tables.
Version
Should work on most versions of DB2.
Statement
Note: replace SCHEMA_NAME and TABLE_NAME with the values for the table you are looking for dependencies for
with d1 (tabschema, tabname, dtype, bschema, bname) as (select tabschema
, tabname
, dtype
, bschema
, bname
from syscat.tabdep
where bschema='SCHEMA_NAME' and bname='TABLE_NAME'
UNION ALL
Select d2.tabschema
, d2.tabname
, d2.dtype
, d2.bschema
, d2.bname
from d1, syscat.tabdep d2 where d2.bschema=d1.tabschema and d2.bname=d1.tabname
)
select * from d1
with ur
Sample Output
TABSCHEMA TABNAME DTYPE BSCHEMA BNAME --------- --------------------------------- ----- ---------- ---------------------- SQL0347W The recursive common table expression "DB2ADMIN.D1" may contain an infinite loop. SQLSTATE=01605 DB2 DP_WEEKLY_SNAPSHOT V DB2 ALL_ORDERS DB2 DIGGER_PORTAL_PERFORMANCE S DB2 ALL_ORDERS DB2 DIGGER_PORTAL_WEEKLY_SNAPSHOT S DB2 ALL_ORDERS THESYS DIGGER_PROFILE V DB2 ALL_ORDERS THESYS DIGGER_STATS_COUNT S DB2 ALL_ORDERS THESYS TALLIER_EXPORT_TO_TPS V DB2 ALL_ORDERS ONNTRAK ORDER_SUMMARY V DB2 ALL_ORDERS THESYS DIGGER_STATS V THESYS DIGGER_STATS_COUNT 8 record(s) selected with 1 warning messages printed.
The SQL0347W is returned with any recursive SQL and can be ignored as long as you’re careful not to actually incur an infinite loop.
[…] DB2 Administrative SQL Cookbook: Identifying Dependent MQTs and Views […]