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