Find String PDM to an outfile

We have all had those requests from our superiors: "Where is insert-name-here file used?"

There are tools from third parties that will give me this information. I have used some of them, and found that they missed some objects. I always combine the information these tools provide me with a scan of the members in the source files in the production library list. I use the Find String PDM command, FNDSTRPDM, to search for the string of information I want. Most people who use this command use it to produce a printed list of the source members it finds. While that may be OK if there are just a few libraries and source members, I work in an environment where there are 25 libraries in the production library list and many of these libraries contain multiple source files. I do not want to have to copy information from all of the generated spool files into a physical file that I can share with others, that would be too much work.

Looking at the help for the FNDSTRPDM I had a eureka moment, that would give me a way to write the name of the source member to an output file.

Find String Type choices, press Enter. Find . . . . . . . . . . From column number . . 1 - *RCDLEN To column number . . . *RCDLEN 1 - *RCDLEN Kind of match . . . . 2 1=Same case, 2=Ignore case Option . . . . . . . . . *NONE *NONE, Valid option Prompt . . . . . . . . N Y=Yes, N=No Print list . . . . . . . N Y=Yes, N=No

The Option parameter can contain a PDM option that I have defined in my PDM option file! As I have described in a previous post I can create my own PDM options to do whatever I want. I can create an option that will be "executed" every time the string I am searching for is found. So I created the following option in my PDM option file:

Option Command XX CALL PGM(MYLIB/SRCHCMDCL) PARM(&L &F &N &T)

The new option is called XX, and it calls a program in my library. There are many different parameters that PDM has available for me to use, in this case I am using the following parameters:

• &L name of library the source file is in
• &F name of the source file
• &N name of the source file member
• &T type of the source file member

Before I write the program I need a file or table to output the information into, in this case I created a SQL DDL table with columns to match the parameters PDM will be passing:

01 CREATE OR REPLACE TABLE MYLIB.SRCHCMDF 02 (LIBRARY CHAR(10), 03 SRCFILE CHAR(10), 04 SRCMBR CHAR(10), 05 SRCTYPE CHAR(10))

The program to write to the file, SRCHCMDCL, could have been written in RPG, but I decided to keep things simple and write it in CL:

01 PGM PARM(&LIB &FILE &MBR &TYPE) 02 DCL VAR(&LIB) TYPE(*CHAR) LEN(10) 03 DCL VAR(&FILE) TYPE(*CHAR) LEN(10) 04 DCL VAR(&MBR) TYPE(*CHAR) LEN(10) 05 DCL VAR(&TYPE) TYPE(*CHAR) LEN(10) 06 RUNSQL SQL('INSERT INTO MYLIB.SRCHCMDF + VALUES(''' || &LIB |< ''',+ ''' || &FILE |< ''',+ ''' || &MBR |< ''',+ ''' || &TYPE |< ''')') + COMMIT(*NC) 07 ENDPGM

Line 1: I decided to give the parameters passed to the program more meaningful names than the PDM parameters.

Lines 2 – 5: The definitions of the passed parameters.

Line 6: The RUNSQL command allows me to run SQL statements in CL programs. In this case I am using it to insert a row into the table I created earlier. I have to concatenate the values together, I need to use three apostrophes either side of the concatenate value as that will give me a single apostrophe in the final string.

As I am searching for which members contain a file name, I also need to search for any related logical files, SQL Views and Indexes too. Therefore, I created a table to contain the list of files, Views, and Indexes I wanted to search for.

01 CREATE OR REPLACE TABLE MYLIB.SRCHCMDFILES 02 (FILE CHAR(10)) FILE PFILE99 LFILE99A LFILE99B LFILE99C

And now the list of libraries to search. In this example there are only four, but any number could be searched.

01 CREATE OR REPLACE TABLE MYLIB.SRCHCMDLIBS 02 (LIBRARY CHAR(10)) LIBRARY QGPL MYLIB OTHERLIB ANOTHERLIB

I decided to create two CL programs to perform the search. The first would get a list of all the source files in the libraries into a table, and then submit to batch the second program for each library and source file. If I submit the second program to a job queue that allows more than one job at a time to run I could get my results faster. The job queue QPGMR in the subsystem of the same name gives me that ability.

I am going to divide the first program into three parts to make it easier to explain what it is doing.

1. Definitions
2. Create list of source files
3. Submit second program to batch

Let me start with the first section:

01 PGM 02 DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1) 03 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 04 DCL VAR(&COUNT) TYPE(*DEC) 05 DCLF FILE(QTEMP/@SRCFILES) 06 RTVJOBA TYPE(&JOBTYPE) 07 IF COND(&JOBTYPE = '1') THEN(DO) SBMJOB CMD(CALL PGM(PGM1)) + JOB(FINDSRCMBR) + JOBQ(QPGMR) 08 RETURN 09 ENDDO 10 CLRPFM FILE(MYLIB/SRCHCMDF)

Lines 2 – 4: Definitions of the variables I will be using.

Line 5: Definition of a file I will be "reading" in this CL program.

Line 6: I am retrieving the type of the job, which tells me if the program is running interactively or in batch.

Lines 7 – 9: If the job is running interactively, job type = 1, I want the program to submit itself to batch.

Line 10: Clear the table all of the second programs will be writing to.

11 RUNSQL SQL('CREATE TABLE QTEMP.@SRCFILES + 12 (SRCLIB,SRCFILE) + 13 AS + 14 (SELECT CAST(B.TABLE_SCHEMA AS CHAR(10)),+ 15 CAST(B.TABLE_NAME AS CHAR(10)) + 16 FROM MYLIB.SRCHLIBS A + 17 LEFT OUTER JOIN QSYS2.SYSTABLES B + 18 ON A.LIBRARY = CAST(B.TABLE_SCHEMA + 19 AS CHAR(10)) + 20 WHERE B.TABLE_TYPE = ''P'' + 21 AND B.FILE_TYPE = ''S'') + 22 WITH DATA') + 23 COMMIT(*NC)

I have no idea what source files are in the libraries I will be searching. Fortunately I can identify which files are source files by using the SYSTABLES view. If I join my table of libraries with SYSTABLES I will generate a list of all the source files I need to search.

Lines 12 and 13: I am creating a new SQL table in QTEMP with columns for the library and source file.

Line 14 and 15: In SYSTABLES the library and file names as 128 characters, I am casting them to be just 10.

Lines 16 – 19: I am joining the libraries table, MYLIBS.SRCHLIBS to SYSTABLES by the library name.

Lines 20 and 21: I am selecting only those rows where the table type is "P" for a physical file and the file type is "S" for a source file, both columns are from SYSTABLES.

The contents of this new table would look something like this:

SRCLIB SRCFILE ANOTHERLIB QDDSSRC OTHERLIB TRANSSRC ANOTHERLIB QRPGLESRC ANOTHERLIB QTXTSRC ANOTHERLIB QRPGSRC OTHERLIB QRPGSR0001 ANOTHERLIB QMNUSRC OTHERLIB QRPGSRC ANOTHERLIB QUSRSRC

Now I can just "read" the new file and submit program 2 for each source file and library.

24 DOWHILE COND(&LOOP) 25 RCVF 26 MONMSG MSGID(CPF0000) EXEC(LEAVE) 27 SBMJOB CMD(CALL PGM(MYLIB/PGM2) + PARM(&SRCLIB &SRCFILE)) + JOB(&SRCLIB) + JOBQ(QPGMR) 28 CHGVAR VAR(&COUNT) VALUE(&COUNT + 1) 29 IF COND(&COUNT = 5) THEN(DO) 30 DLYJOB DLY(120) 31 CHGVAR VAR(&COUNT) VALUE(0) 32 ENDDO 33 ENDDO 34 ENDPGM

Line 24: Start of the Do loop, that ends on line 33.

Line 25: I perform the equivalent of a read on the source file and library file.

Line 26: If end of file, or some other error occurs, leave this Do loop.

Line 27: Submit the call to program 2 to batch, passing to it the source file and library names.

Lines 28 – 32: I wanted to be careful not to overwhelm the QPGMR subsystem with too many jobs running program 2. Therefore, after every 5 submit jobs the program pauses for 120 seconds, before continuing. By doing that this will allow some of the previously submitted jobs to finish before more are submitted.

Program 2 is:

01 PGM PARM(&SRCLIB &SRCFILE) 02 DCL VAR(&SRCLIB) TYPE(*CHAR) LEN(10) 03 DCL VAR(&SRCFILE) TYPE(*CHAR) LEN(10) 04 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 05 DCLF FILE(MYLIB/SRCHFILES) 06 DOWHILE COND(&LOOP) 07 RCVF 08 MONMSG MSGID(CPF0000) EXEC(LEAVE) 09 FNDSTRPDM STRING(&FILE) + 10 FILE(&SRCLIB/&SRCFILE) + 11 MBR(*ALL) + 12 OPTION(XX) 13 MONMSG MSGID(PDM0055) 14 ENDDO 15 ENDPGM

Line 1: The source file and library are passed to this program.

Lines 2 – 4: Definitions of the variables that this program will be using.

Line 5: This program will be "reading" the file that contains the list of files I am searching for.

Line 6: Start of the Do loop, that finishes on line 14.

Line 7: The equivalent of a "read" of the table SRCHFILES, this contains all of the files I want to search for.

Lines 9 – 12: The find string PDM command is searching for the file name in &FILE in the source members of the source file &SRCFILE in the library &SRCLIB, and when a match is found PDM option XX is performed, and a row is inserted into the output file.

Line 13: This MONMSG for message PDM055 stops the command erroring if the source file is empty.

When all of the jobs have finished the output file, SRCHCMDF, will contain the list of members like this:

LIBRARY SRCFILE SRCMBR SRCTYPE ANOTHERLIB QDDSSRC PFILE1 PF ANOTHERLIB QDDSSRC PFILE2 PF ANOTHERLIB QDDSSRC PFILE3 PF ANOTHERLIB QRPGSRC PGM1 RPGLE ANOTHERLIB QRPGSRC PGM2 SQLRPGLE ANOTHERLIB QDDSSRC VIEW1 SQL ANOTHERLIB QRPGSRC PGM3 SQLRPGLE ANOTHERLIB QRPGSRC PGM4 SQLRPGLE ANOTHERLIB QRPGSRC PGM5 RPGLE

If I am going to change the objects that was generated from these source members I need to know what object types these source members would have created. I can create a View over the table to do that:

01 CREATE OR REPLACE VIEW MYLIB.SRCHCMDFV 02 (LIBRARY,SRCFILE,SRCMBR,SRCTYPE,OBJTYPE) 03 AS 04 SELECT A.*, 05 CASE WHEN A.SRCTYPE = 'RPGLE' THEN '*PGM' 06 WHEN A.SRCTYPE = 'CLP' THEN '*PGM' 07 WHEN A.SRCTYPE = 'LF' THEN '*FILE' 08 WHEN A.SRCTYPE = 'CLLE' THEN '*PGM' 09 WHEN A.SRCTYPE = 'PF' THEN '*FILE' 10 WHEN A.SRCTYPE = 'SQLRPGLE' THEN '*PGM' 11 WHEN A.SRCTYPE = 'DSPF' THEN '*FILE' 12 ELSE NULL 13 END 14 FROM MYLIB.SRCHCMDF A ;

Lines 5 – 12: I am creating what I call a derived column created based upon data in another column, in this case I am mapping the source type to an object type. It is not perfect as I am not mapping source to modules. Below are some sample results.

LIBRARY SRCFILE SRCMBR SRCTYPE OBJTYPE ANOTHERLIB QDDSSRC PFILE1 PF *FILE ANOTHERLIB QDDSSRC PFILE2 PF *FILE ANOTHERLIB QDDSSRC PFILE3 PF *FILE ANOTHERLIB QRPGSRC PGM1 RPGLE *PGM ANOTHERLIB QRPGSRC PGM2 SQLRPGLE *PGM ANOTHERLIB QDDSSRC VIEW1 SQL - ANOTHERLIB QRPGSRC PGM3 SQLRPGLE *PGM ANOTHERLIB QRPGSRC PGM4 SQLRPGLE *PGM ANOTHERLIB QRPGSRC PGM5 RPGLE *PGM

I can then join this View to the Object Statistics table function to get the last used date of the object I have assumed in the previous View.

01 CREATE OR REPLACE VIEW MYLIB.SRCHCMDFV1 02 (LIBRARY,SRCFILE,SRCMBR,SRCTYPE,OBJTYPE,LASTUSED) 03 AS 04 SELECT A.*, 05 CASE WHEN A.OBJTYPE IS NULL THEN NULL 06 ELSE (SELECT CAST(B.LAST_USED_TIMESTAMP AS DATE) FROM 07 TABLE(QSYS2.OBJECT_STATISTICS(A.LIBRARY,A.OBJTYPE,OBJECT_NAME => A.SRCMBR)) B) 08 END 09 FROM MYLIB.SRCHCMDFV A ;

This View is an example of building Views of Views as I am joining the View I has previous built to the OBJECT_STATISTICS table function. I making another derived column to contain the date the object was last used.

Line 5: If the Object type from the previous View is null, then the value of the Last Used date will be null.

Lines 6 and 7: If the Object Type is not null then I get the last used timestamp from the OBJECT_STATISTICS table function. I am passing the library, object type, and source member name to the function and get the last used timestamp returned, which I cast to a date.

The results are what I want:

LIBRARY SRCFILE SRCMBR SRCTYPE OBJTYPE LASTUSED ANOTHERLIB QDDSSRC PFILE1 PF *FILE 2018-11-09 ANOTHERLIB QDDSSRC PFILE2 PF *FILE 2018-11-09 ANOTHERLIB QDDSSRC PFILE3 PF *FILE 2018-11-04 ANOTHERLIB QRPGSRC PGM1 RPGLE *PGM 2014-05-30 ANOTHERLIB QRPGSRC PGM2 SQLRPGLE *PGM 2018-11-09 ANOTHERLIB QDDSSRC VIEW1 SQL - - ANOTHERLIB QRPGSRC PGM3 SQLRPGLE *PGM 2018-11-09 ANOTHERLIB QRPGSRC PGM4 SQLRPGLE *PGM 2017-02-15 ANOTHERLIB QRPGSRC PGM5 RPGLE *PGM -

The Last Used dates can be used to determine which objects would need to be modified if the original file is to be changed. If an object has not been used in four years does it need to be changed?

If I so desired I could copy the View to an output file in QTEMP, use the CPYTOIMPF to copy the data to the IFS, and then Email the IFS file to interested people.

A great advantage of having the second View is that if I want to see what objects have been used since I created the data I can just query the View again and as the Last Used Date comes from the OBJECT_STATISTICS it will be current.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.