DB2 Administrative SQL Cookbook: Identifying Dependent MQTs and Views

Posted by

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!


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.


Should work on most versions of DB2.


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'
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
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.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

One comment

Leave a Reply

Your email address will not be published.

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