Find the list of programs using a DB2 table.

If the question of nature, how can I find the list of programs using a DB2 table? Then this article might help you. To elaborate on the topic let’s assume that a table A is used by Program-1, Program-2 and Program-3. Program-1 inserts rows, program-2 reads the rows and program-3-updates the rows.

User needs “something” which should be able to give the output in a format like:

tablea 
program-1 insert 
Program-2 select 
Program-3 update

Then the further discussion might help you.

We’ll create a CRUD matrix – CRUD stand for – Create, Read, Update, Delete.

The following SQL will look at SYSTABAUTH catalog table into (SELECT,INSERT,UPDATE,DELETE) auth columns and give back the package name.

The Query for the C R U D MATRIX:

SELECT SUBSTR(TCREATOR,1,10) AS CREATOR 
, SUBSTR(TTNAME,1,10) AS NAME 
, SUBSTR(GRANTEE,1,10) AS PROGRAM 
, CASE WHEN INSERTAUTH = 'Y' THEN 'C' 
ELSE '-' 
END AS C 
, CASE WHEN SELECTAUTH = 'Y' THEN 'R' 
ELSE '-' 
END AS R 
, CASE WHEN UPDATEAUTH = 'Y' THEN 'U' 
ELSE '-' 
END AS U 
, CASE WHEN DELETEAUTH = 'Y' THEN 'D' 
ELSE '-' 
END AS D 
, CASE WHEN COLLID = ' ' THEN '** PLAN **' 
ELSE COLLID 
END AS "PLAN/COLLECTION" 
, CASE WHEN CONTOKEN = ' ' THEN CONTOKEN 
ELSE HEX(CONTOKEN) 
END AS TOKEN 
FROM SYSIBM.SYSTABAUTH 
WHERE GRANTEETYPE = 'P' 
AND TCREATOR = 'TABCRT' 
;

Output:

---------+---------+---------+---------+---------+---------+---------+- 
CREATOR NAME    PROGRAM     C R U D PLAN/COLLECTION 
---------+---------+---------+---------+---------+---------+---------+- 
TABCRT CUSTOMER BOBDBXX     - R - D ** PLAN ** 
TABCRT CUSTOMER DB2XXX      C - - - CAPN 
TABCRT CUSTOMER DB2XXX      - - - D CAPN 
TABCRT CUSTOMER DB2INS      - R - - CAPN 
TABCRT CUSTOMER DB2CURAO    - R - - CAPN 
TABCRT CUSTOMER MULTIXXX    - R - - CAPN 
TABCRT CUSTOMER DB2CURXX    - R - - CAPN 
TABCRT EMP      AMKIMADB    C - - - INVF 
TABCRT EMP      BMAIXXDB    - R - - KIRA 
TABCRT EMP      TRIGGER3    - R - - TABCRT 
TABCRT CUSTOMER DB2MULSE    - R - - TEST 
DSNE610I NUMBER OF ROWS DISPLAYED IS 11 
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
---------+---------+---------+---------+---------+---------+---------+-

The out put has Creator, Name (Table), Program Name, C (Insert), R (Read), U (Update), D (Delete) and plan and collection. The output shown above is modified a bit for the readability. You’ll get the similar report for the actual run also but with the SQL used here, it might not align the way it is shown in the above output.