2011-09: CRUD – How to find which program is doing what to which table

 

Now it might sound a bit rude, but CRUD is one of my favourite SQL queries and I use it a lot!

 

CRUD. Create, Read, Update, and Delete

However, this newsletter topic probably created most fun for me in advance because I have a couple of colleagues -including non-technicians- reading the stuff before it gets published and the feedback on this content is definitely worth a mention. It’s all about this nice acronym that doesn’t leave any open questions for me – it actually stands for Create, Read, Update, and Delete I guess that Insert, Select, Update, and Delete (ISUD) just doesn’t have a ring to it! Anyway it looks like some others never heard of it before, or associate something  totally different… a couple of funny prompts for clarification later led me to do quick search in Wikipedia, which opened up some other associations besides my topic…

  • A sticky substance, especially when dirty and/or encrusted
  • Crud (game), played on a billiard table
  • Chalk river unidentified deposits, corroded products containing radioactive nuclear species
  • Neil Crud, Welsh rock musician, journalist and DJ
  • Crud Puppy, a cartoon character from User Friendly
  • Crud, minced oath for crap, meaning feces
  • Crud, a radio station in Rochdale College
  • All right folks, we’ll skip those for today, cause what I associate with
  • CRUD is “Create, read, update and delete” summarized by Wiki as basic functions of a computer database

All right folks, we’ll skip those for today, cause what I associate with CRUD is “Create, read, update and delete” summarized by Wiki as basic functions of a computer database.

 

Which static SQL program does what to which of my tables?”

Anyway this topic helps you guys with that age old problem of Table usage or even, horror of horrors, the Auditors asking questions…
The problem is “Which static SQL program does what to which of my tables?”
So my first exercise for this is; I wish to see all packages in the TEST collection that have anything whatsoever to do with the SYSIBM.SYSDATABASE table.
Now you could trawl through the SYSxxxxAUTH etc with GRANTEE and GRANTOR and with PUBLIC etc or you could run this (make use of the exclude/include place holder if you like):

SELECT SUBSTR(TCREATOR, 1 , 8) AS CREATOR                  
     , SUBSTR(TTNAME, 1 , 18)   AS NAME                                 
     , SUBSTR(GRANTEE, 1 , 8)  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" 
  FROM SYSIBM.SYSTABAUTH 
 WHERE GRANTEETYPE = 'P' 
   AND COLLID      =    'TEST' 
-- AND NOT GRANTEE =    '<exclude>'
-- AND GRANTEE     =    '<include>' 
   AND TCREATOR    =    'SYSIBM' 
   AND TTNAME      =    'SYSDATABASE'                           
--ORDER BY 1 , 2
ORDER BY 3 , 2 
WITH UR  
;

 

The output looks like

---------+---------+---------+---------+---------+---------+--------
CREATOR   NAME                PROGRAM   C  R  U  D  PLAN/COLLECTION     
---------+---------+---------+---------+---------+---------+--------
SYSIBM    SYSDATABASE         M2DBIN09  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         M2DBRI09  -  R  -  -  TEST
SYSIBM    SYSDATABASE         M2DBRT09  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         M2DBTS09  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2DB41  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2DB45  -  R  -  -  TEST  
SYSIBM    SYSDATABASE         MDB2DBPD  -  R  -  -  TEST                  
SYSIBM    SYSDATABASE         MDB2DBTS  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2SY8T  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2SY9C  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2SYSC  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         MDB2SYST  -  R  -  -  TEST           
SYSIBM    SYSDATABASE         O2RTS030  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         SQLDDLD   -  R  -  -  TEST       
SYSIBM    SYSDATABASE         SQLDDLS   -  R  -  -  TEST 
SYSIBM    SYSDATABASE         SQLDV9DB  -  R  -  -  TEST 
SYSIBM    SYSDATABASE         SQLDV9TS  -  R  -  -  TEST 
DSNE610I NUMBER OF ROWS DISPLAYED IS 17                                   
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

 

The data shown from this simple example lists 17 programs with select, or lets say read authority on SYSDATABASE. Now, let’s have a look at a scenario where I have a program that updates  SYSTABLESPACESTATS. I use the same SQL statement and change the last line of the where clause to

  AND TTNAME      =    'SYSTABLESPACESTATS'
---------+---------+---------+---------+---------+---------+-------
CREATOR  NAME                PROGRAM   C  R  U  D  PLAN/COLLECTION
---------+---------+---------+---------+---------+---------+------- 
SYSIBM   SYSTABLESPACESTATS  M2DBRT09  -  R  U  -  TEST 
SYSIBM   SYSTABLESPACESTATS  MDB2DB46  -  R  -  -  TEST 
SYSIBM   SYSTABLESPACESTATS  MDB2T003  -  -  U  -  TEST 
SYSIBM   SYSTABLESPACESTATS  MOD#CTLG  C  R  U  D  TEST 
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

A lot less programs and you can see not just read access, but updates are  also done. There is even a MOD#CTLG program that does additionally inserts and updates.

Simple, easy and very straight forward if you need to determine what your static SQL applications are shooting against your DB2 tables.
If you like to do some checks which programs in your shop update the salary table, or who has authority to access sensitive financial data this  may be the easiest way. There are lots of alternatives that you can think  of yourselves! Very handy for housekeeping, documentation, or checking out which programs will be affected by a table change etc.

 

I hope that this little bit of data was of interest and/or of use and as always comments or questions are more than welcome!
TTFN,
Roy Boxwell
Senior Architect