I am new to this forum.I am developing an automation tool for search. Basically the search is to find the source of the DB2 table. The DB2 table names present in programs is stored in PS file which is obtained using searchfor utility . I need to search now these programs present in PS file in separate job pds where its is used. How this can be done?
Joined: 01 Dec 2006 Posts: 739 Location: Pennsylvania
Many years ago I spent a solid six months working on such a project.
This is no easy task. I will mention a few points to consider.
Since we are talking about DB2 programs, you will not see PGM=MYPROG statement in your JCL.
You will most likely see PGM=IKJEFT1B or IKJEFT01 perhaps
You will have a SYSTSIN DD card that has the driver program perhaps in this fashion:
DSN SYSTEM (PROD1)
RUN PROGRAM (DRIVER) -
If you are lucky, that is the program you are looking for and you got your Job name.
More likely, your program is a subroutine of another program. Hopefully you have that documented someplace and you can use your subroutine list to help you.
//SORTLIST DD SYSOUT=*
//SORTIN DD DSN=&&T1,DISP=SHR
//SYMNAMES DD DSN=&&S1,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
HEADER3=(' SELECT *',/,
' FROM SYSIBM.SYSTABAUTH',/,
' AND TTNAME IN ('),
TRAILER3=(' ) WITH UR;'))
I would let you make change to HEADER3 query to following query.
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'
END AS C
, CASE WHEN SELECTAUTH = 'Y' THEN 'R'
END AS R
, CASE WHEN UPDATEAUTH = 'Y' THEN 'U'
END AS U
, CASE WHEN DELETEAUTH = 'Y' THEN 'D'
END AS D
, CASE WHEN COLLID = ' ' THEN '** PLAN **'
END AS "PLAN/COLLECTION"
WHERE GRANTEETYPE = 'P'
AND COLLID = 'TEST'
AND TCREATOR = 'SYSIBM'
ORDER BY 3 , 2
Once ran, you will get a query formed, use DSNTIAUL to run that query and you will get what you want.