{"id":652,"date":"2015-09-10T17:25:04","date_gmt":"2015-09-10T11:55:04","guid":{"rendered":"http:\/\/ibm-mainframes.com\/blog\/?p=652"},"modified":"2023-06-13T23:10:56","modified_gmt":"2023-06-13T17:40:56","slug":"find-the-list-of-programs-using-a-db2-table","status":"publish","type":"post","link":"https:\/\/zmainframes.com\/zlog\/find-the-list-of-programs-using-a-db2-table\/","title":{"rendered":"Find the list of programs using a DB2 table."},"content":{"rendered":"

Find the list of programs using a DB2 table.<\/span><\/h4>\n

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.<\/p>\n

User needs “something” which should be able to give the output in a format like:<\/p>\n

tablea \nprogram-1 insert&nbsp;\nProgram-2 select \nProgram-3 update\n\n<\/pre>\n
Then the further discussion might help you.<\/p>\n
We’ll create a CRUD matrix – CRUD stand for – Create, Read, Update, Delete.<\/p>\n
The following SQL will look at SYSTABAUTH catalog table into (SELECT,INSERT,UPDATE,DELETE) auth columns and give back the package name.<\/p>\n
The Query for the C R U D MATRIX:<\/p>\n
SELECT SUBSTR(TCREATOR,1,10) AS CREATOR \n, SUBSTR(TTNAME,1,10) AS NAME \n, SUBSTR(GRANTEE,1,10) AS PROGRAM \n, CASE WHEN INSERTAUTH = 'Y' THEN 'C' \nELSE '-' \nEND AS C \n, CASE WHEN SELECTAUTH = 'Y' THEN 'R' \nELSE '-' \nEND AS R \n, CASE WHEN UPDATEAUTH = 'Y' THEN 'U' \nELSE '-' \nEND AS U \n, CASE WHEN DELETEAUTH = 'Y' THEN 'D' \nELSE '-' \nEND AS D \n, CASE WHEN COLLID = ' ' THEN '** PLAN **' \nELSE COLLID \nEND AS \"PLAN\/COLLECTION\" \n, CASE WHEN CONTOKEN = ' ' THEN CONTOKEN \nELSE HEX(CONTOKEN) \nEND AS TOKEN \nFROM SYSIBM.SYSTABAUTH \nWHERE GRANTEETYPE = 'P' \nAND TCREATOR = 'TABCRT' \n;<\/pre>\n
Output:<\/p>\n
---------+---------+---------+---------+---------+---------+---------+- \nCREATOR NAME    PROGRAM     C R U D PLAN\/COLLECTION \n---------+---------+---------+---------+---------+---------+---------+- \nTABCRT CUSTOMER BOBDBXX     - R - D ** PLAN ** \nTABCRT CUSTOMER DB2XXX      C - - - CAPN \nTABCRT CUSTOMER DB2XXX      - - - D CAPN \nTABCRT CUSTOMER DB2INS      - R - - CAPN \nTABCRT CUSTOMER DB2CURAO    - R - - CAPN \nTABCRT CUSTOMER MULTIXXX    - R - - CAPN \nTABCRT CUSTOMER DB2CURXX    - R - - CAPN \nTABCRT EMP      AMKIMADB    C - - - INVF \nTABCRT EMP      BMAIXXDB    - R - - KIRA \nTABCRT EMP      TRIGGER3    - R - - TABCRT \nTABCRT CUSTOMER DB2MULSE    - R - - TEST \nDSNE610I NUMBER OF ROWS DISPLAYED IS 11 \nDSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 \n---------+---------+---------+---------+---------+---------+---------+-<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
Find the list of programs using a DB2 table. If the question of nature, how can I find the list […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,30],"tags":[92,41,37],"class_list":["post-652","post","type-post","status-publish","format-standard","hentry","category-mainframes","category-ibm-db2","tag-database","tag-db2","tag-mainframes"],"amp_enabled":true,"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"Anuj Dhawan","author_link":"https:\/\/zmainframes.com\/zlog\/author\/anuj-dhawan\/"},"rttpg_comment":29,"rttpg_category":"Mainframes<\/a> IBM DB2<\/a>","rttpg_excerpt":"Find the list of programs using a DB2 table. If the question of nature, how can I find the list […]","_links":{"self":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/652","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/comments?post=652"}],"version-history":[{"count":4,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/652\/revisions"}],"predecessor-version":[{"id":1175,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/posts\/652\/revisions\/1175"}],"wp:attachment":[{"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/media?parent=652"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/categories?post=652"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zmainframes.com\/zlog\/wp-json\/wp\/v2\/tags?post=652"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}