|
View previous topic :: View next topic
|
| Author |
Message |
i_sivapras
New User
Joined: 04 Aug 2009
Posts: 4
Location: hyderabad
|
|
|
|
Hi,
How can i find list of programs using db2 tables.
for ex : if table A is being used by pgm1,pgm2,pgm3.Pgm1-inserts rows
pgm2- reads the rows pgm3-update the rows.
my output of the rexx should be
tablea pgm1 insert
pgm2 select
pgm2 update
Can any one help me on this |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Solution inside DB2 :
if these are static-sql programs
you can query sysibm.syspackdep and sysibm.syspackstmt
If you have explain info of your programs
you can query PLAN_TABLE and DSN_STATEMNT_TABLE
or if you really want something fancy you can even xquery DSN_QUERY_TABLE
Solution outside DB2 :
scanning & parsing the source libs |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi i_sivapras,
What you looking for is CRUD(Create Read Update Delete) Matrix, i think.
I have seen the SQL statements somewhere in web, i don't know whether i have it or not, meanwhile you can search too, if you find post it...
Basically what is does is, it looks at SYSTABAUTH catalog table into (SELECT,INSERT,UPDATE,DELETE)auth columns and gives you the package name.
Thank You,
Sushanth |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Sushanth,
I am not a DB2 specialist, so I took the time to Google for 'CRUD Matrix'.
It took me another 25 seconds to do a SELECT on table SYSTABAUTH.
And another split second to understand that your answer is valueless.  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hello Marso,
| Quote: |
| I am not a DB2 specialist |
Me too, i am just a beginner.......
I did a little search and i couldn't find it, since the CRUD had spaces in-between. After that, did some browsing and found it and executed it.
| Code: |
--TO GET A C R U D MATRIX FOR A DB2 PLAN OR PACKAGE
--
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 = 'SHANDB'
;
---------+---------+---------+---------+---------+---------+---------+-
CREATOR NAME PROGRAM C R U D PLAN/COLLECTION
---------+---------+---------+---------+---------+---------+---------+-
SHANDB EMPLOYEE BOBDBRM - R - D ** PLAN **
SHANDB EMPLOYEE DB2SEL C - - - BOBC
SHANDB EMPLOYEE DB2DEL - - - D BOBC
SHANDB EMPLOYEE DB2INS - R - - BOBC
SHANDB EMPLOYEE DB2CURSO - R - - BOBC
SHANDB EMPLOYEE MULTISEL - R - - BOBC
SHANDB EMPLOYEE DB2CURFU - R - - BOBC
SHANDB EMP KMKIMADB C - - - INVF
SHANDB EMP BMKIRADB - R - - KIRA
SHANDB EMP TRIGGER3 - R - - SHANDB
SHANDB EMPLOYEE DB2MULSE - R - - TEST
DSNE610I NUMBER OF ROWS DISPLAYED IS 11
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+- |
Let me know if this does the trick....
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
