Retrieving plans or packages with access to the same table

For example, to retrieve the names of application plans and packages that refer to table DSN8D10.EMP directly, issue the following statement:

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSTABAUTH
  WHERE GRANTEETYPE = 'P' AND
        TCREATOR = 'DSN8D10'  AND
        TTNAME = 'EMP';

The preceding query does not distinguish between plans and packages. To identify a package, use the COLLID column of table SYSTABAUTH, which names the collection in which a package resides and is blank for a plan.

A plan or package can refer to the table indirectly, through a view.

To find all views that refer to the table:

1. Issue the following query:

   SELECT DISTINCT DNAME FROM SYSIBM.SYSVIEWDEP
     WHERE BTYPE = 'T' AND
           BCREATOR = 'DSN8D10'  AND
           BNAME = 'EMP';

2. Write down the names of the views that satisfy the query. These values are instances of DNAME_list.
3. Find all plans and packages that refer to those views by issuing a series of SQL statements. For each instance of DNAME_list, issue the following statement:

   SELECT DISTINCT GRANTEE FROM SYSIBM.SYSTABAUTH
     WHERE GRANTEETYPE = 'P' AND
           TCREATOR = 'DSN8D10'  AND
           TTNAME = DNAME_list;