There is a lot of information in the DB2-catalog and some of this information is hidden in the most strange places. Don't you think that the tables SYSPACKDEP and SYSPLANDEP should contain information about how tables are used, and not only which tables are used. We have to go elsewhere to find this information.

The table SYSTABAUTH contains this information for all static binded plans and packages. The following SQL-statement will create a CRUD-matrix for package MYPACK in collection MYCOLL:

SELECT TCREATOR AS CREATOR
     , TTNAME AS TABLE
     , MAX(INSERTAUTH) AS CREATE
     , MAX(SELECTAUTH) AS READ
     , MAX(UPDATEAUTH) AS UPDATE
     , MAX(DELETEAUTH) AS DELETE
FROM SYSIBM.SYSTABAUTH
WHERE COLLID = 'MYCOLL'
  AND GRANTEE = 'MYPACK'
  AND GRANTEETYPE = 'P'
GROUP BY TCREATOR, TTNAME

This is a fast performing SQL-statement, because there is an index on GRANTEE (followed by a lot of other columns). If you are versioning your packages, you must include column CONTOKEN in your WHERE-clause to identify a specific version of your package. If you are still using DB2 "the old fashioned way" with DBRM's, you must use COLLID = '' and replace MYPACK with the planname.

It is an easy task to make a CRUD-matrix for a table instead of a package or plan. This SQL-statement finds all static binded packages which uses table MYTABLE with creator ME:

SELECT COLLID AS COLLECTION
     , GRANTEE AS PACKAGE
     , MAX(INSERTAUTH) AS CREATE
     , MAX(SELECTAUTH) AS READ
     , MAX(UPDATEAUTH) AS UPDATE
     , MAX(DELETEAUTH) AS DELETE
FROM SYSIBM.SYSTABAUTH
WHERE TCREATOR = 'ME'
  AND TTNAME = 'MYTABLE'
  AND GRANTEETYPE = 'P'
  AND COLLID ^= ''
GROUP BY COLLID, GRANTEE

This may be a slow performing SQL-statement, if you don't have an index on SYSTABAUTH starting with column TCREATOR or TTNAME and preferably the other column as the next one. If you have versioned packages, you must include CONTOKEN in the SELECT-list and in the GROUP BY clause to be able to identify a specific version. When you are looking for plans you must use COLLID = '' and replace GRANTEE AS PACKAGE with GRANTEE AS PLAN in the SELECT-list.

Lets say you are looking for all packages inserting rows into a certain table then you can easily do this. Add a HAVING MAX(INSERTAUTH) ^= '' after the GROUP BY clause. You can use exactly the same trick for the other three AUTH-columns.

I know that DBRM's are a dying race, fortunately, and I have to mention, that for plans containing many DBRM's the SQL-statements in this tip will be of little use. Therefore, get rid of those DBRM's, there is no help in the DB2-catalog regarding these crossreferences.