DB2 Administrative SQL Cookbook: Identifying Dependent MQTs and Views

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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.