Objects that depend on the dropped table

Before dropping a table, check to see what objects are dependent on the table. The Db2 catalog tables SYSIBM.SYSVIEWDEP, SYSIBM.SYSPLANDEP, and SYSIBM.SYSPACKDEP indicate what views, application plans, and packages are dependent on different Db2 objects.

Finding dependent views

Begin general-use programming interface information.

The following example query lists the views, with their creators, that are affected if you drop the project table:

SELECT DNAME, DCREATOR
  FROM SYSIBM.SYSVIEWDEP
  WHERE BNAME = 'PROJ'
  AND BCREATOR = 'DSN8910'
  AND BTYPE = 'T';

End general-use programming interface information.

Finding dependent packages

The next example lists the packages, identified by the package name, collection ID, and consistency token (in hexadecimal representation), that are affected if you drop the project table:

SELECT DNAME, DCOLLID, HEX(DCONTOKEN)
  FROM SYSIBM.SYSPACKDEP
  WHERE BNAME = 'PROJ'
  AND BQUALIFIER = 'DSN8910'
  AND BTYPE = 'T';

Finding dependent plans

The next example lists the plans, identified by plan name, that are affected if you drop the project table:

SELECT DNAME
  FROM SYSIBM.SYSPLANDEP
  WHERE BNAME = 'PROJ'
  AND BCREATOR = 'DSN8910'
  AND BTYPE = 'T';

Finding other dependencies

In addition, the SYSIBM.SYSINDEXES table tells you what indexes currently exist on a table. From the SYSIBM.SYSTABAUTH table, you can determine which users are authorized to use the table.