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?
I am developing an automation tool for search. Basically the search is to find the source of the DB2 table.
How is it related to either JCL, or VSAM?????
Bhuvana Narasimhan wrote:
The DB2 table names present in programs is stored in PS file which is obtained using searchfor utility .
How is it related to either JCL, or VSAM?????
Bhuvana Narasimhan wrote:
I need to search now these programs present in PS file in separate job pds where its is used.
What did you do to achieve this?
Bhuvana Narasimhan wrote:
How this can be done?
There are obviously 100+ ways of doing this.
First of all, try something by yourself. For instance, try to understand the difference between JCL, VSAM, DB2, job, PS file, searchfor utility, and other smart words.
To let anyone help you here, share us the sample data and expected output. So far you can use JOINKEYS with what you want to achieve but again do that first to get to the solution.
Joined: 01 Dec 2006 Posts: 792 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) -
PLAN (SOMEPLAN)
END
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.
@daveporcelan - I have taken all the points in consideration mentioned by you. Now am stuck with read the programs in PS file line by line and search that in job pds.
Rohit mentioned to use the Joinkeys but that can be used if am going to give 2 input PS files or instream data as input and match but my requirement is not that.
Read the first line in PS file (PARB206) and search that in PDS members and if found write in output file.
In searchfor jcl we can hardcode the string that we wanted to search for but I want to read the data in file and search.Can you suggest any to proceed with.
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
You are on the right track. I used Rexx to read every program.
I had to save Working-Storage fields and the MOVE statements for values moved into them.
This is because most calls are dynamic using the WS field not a literal.
One I completed a program, its information was saved into a pds for future reference.
This way if a driver is called from more than one Job, I only had to process it one.
Same thing if a subroutine is called from multiple places.
This is a very tedious process. Mine takes six hours to run, once every two week.
Note: Rather than try to process the SQL for DB2 usage, I chose to read the SYSIBM.SYSTABAUTH table.
This has all the authorized usage for each module.
Continue on the path you are taking. you will get it.
For searching a pds I typicaly use the PDS86 program from CBTTAPE.ORG file 182.
Something like:
zz=outtrap('lst.')
"pds86 '"libname"' FI : '"string"'"
zz=outtrap('off')
Then postprocess the list.
//SORTLIST DD SYSOUT=*
//SORTIN DD DSN=&&T1,DISP=SHR
//SYMNAMES DD DSN=&&S1,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
OUTFIL REMOVECC,
IFOUTLEN=80,
IFTHEN=(WHEN=(81,8,ZD,EQ,LASTRCD),OVERLAY=(11:X)),
SECTIONS=(1,1,
HEADER3=(' SELECT *',/,
' FROM SYSIBM.SYSTABAUTH',/,
' AND TTNAME IN ('),
TRAILER3=(' ) WITH UR;'))
I would let you make change to HEADER3 query to following query.
Code:
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 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.
