http://www-1.ibm.com/support/docview.wss?rs=64&uid=swg27006152
Determining which programs read or update a specific DB2® table Forum/discussion group
Abstract
Occasionally applications programmers want to know which programs read or update a specific DB2 ® table. If you use SYSPLANDEP or SYSPACKDEP, you find out which plan or packages are dependent on the table. You do not find out if they are inserting, reading, updating or deleting from a table. This information can be found in SYSTABAUTH. Multiple rows can exist for the same package or plan and table combo if one authority was achieved one way and another authority by a different means.
This example has two separate SQL statements that both create a CRUD ( Create - Read - Update - Delete ) matrix. Both statements are full outer joins of four tables ( 4 different SELECTs of the SYSIBM.SYSTABAUTH table).
The predicate is repeated four times. One query creates a CRUD matrix for all tables used by a program, and the other query creates a CRUD matrix of all programs that use a table.
Content
Before running these queries, change the sample program, creator or table to your desired predicate as indicated in the comments at the top of each file.
Sample output of the program SELECT:
+-------------+------------------+--------+-+-+-+-+
TB_VW_CREATOR TB_VW_NAME PROGNAME C R U D
+-------------+------------------+--------+-+-+-+-+
AR CUSTMAST CO8502 . R . .
EBDEBIZ COMPANY_CUSTOMER CO8502 . R . .
EBDEBIZ ORDERSTATUSHEADER CO8502 C . . .
EBDEBIZ ORDERSTATUSLINE CO8502 C . . .
IV OILPURCH CO8502 C R . .
IV PCMITMEX CO8502 . R . .
LOC ANXLOC CO8502 . R . .
T10 BM_VWO_CNTL CO8502 . R U .
T10 BM_VWO_CO_ASSOC CO8502 C . . .
T10 BM_VWO_MASTER CO8502 C . . .
T10 ED_VEDI_PO_ACKMT CO8502 C . . .
T10 EN_VCRPF_OACCT_DTL CO8502 . R . .
T10 EN_VCRPF_OACCT_HDR CO8502 C R U .
T10 ES_VLOCATION CO8502 . R . .
T10 VANXLOC_2 CO8502 . R . .
T10 VDUTY_RATE CO8502 . R . .
T10 VFORGN_EXCH_RATE_C CO8502 . R . .
T10 VHD_HOLD_DETAIL CO8502 C R . D
T10 VHRMNZ_SYST_GROUP CO8502 . R . .
T10 VINTL_ORD_SW CO8502 C . . .
T10 VITEM_QTY CO8502 C R U .
T10 VOP_AUDIT CO8502 C . U .
T10 VPEND_PO_LINE_NOTE CO8502 C . . .
T10 VSH_SO_HOLD CO8502 C R . D
T10 VTRGR_SO CO8502 C . . .
T10 VVNDR_CSTG CO8502 . R . .
Sample output of the table SELECT:
+-------------+------------------+--------+-+-+-+-+
TB_VW_CREATOR TB_VW_NAME PROGNAME C R U D
+-------------+------------------+--------+-+-+-+-+
T10 VSH_SO_HOLD AO30 C . . .
T10 VSH_SO_HOLD CO8005 C . . .
T10 VSH_SO_HOLD CO8502 C R . D
T10 VSH_SO_HOLD CR8B . R . D
T10 VSH_SO_HOLD DAFIX04 . R . D
T10 VSH_SO_HOLD EB8502 C . . .
T10 VSH_SO_HOLD IB36 . R U .
T10 VSH_SO_HOLD IB41 . R . D
T10 VSH_SO_HOLD IB4100 . R . D
T10 VSH_SO_HOLD IQSB . R . .
T10 VSH_SO_HOLD IQ81 . R . .
T10 VSH_SO_HOLD OEIB C R . D
T10 VSH_SO_HOLD OEMU . R . .
T10 VSH_SO_HOLD OEOP C R . D
T10 VSH_SO_HOLD OESL C R . D
Example submitted by: Damon Anderson, Sr. DBA, Anixter, Inc.
Rate this example
User comments on this example:
"thanks, thats exactly what I need."
Notice: This example is from the DB2 Examples Trading Post and is provided on an "as-is" basis. You may copy and modify this example in any form without payment to IBM, for the purposes of designing and developing application programs conforming to the application programming interface for the z/OS or OS/390 operating system for which these examples are written. This example has been tested, but it has not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of this example in your particular environment. Use this example as a model for your own situations.
Return to the DB2 Examples Trading Post
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software Group
DB2 Development
DB2 Information Management Software
