Pages

Wednesday, December 5, 2018

Find String PDM to an outfile

fndstrpdm to an output file

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.

5 comments:

  1. Very helpful when you don't have xref software

    ReplyDelete
    Replies
    1. This is useful whether or not your have xref software.

      Years ago worked on a payroll conversion project and depended on a well known xref product to identify files we would need to change. We later found that about 33% of the files, fields, etc were missed by this well known tool. When we telephoned their support line and described the scenarios they admitted their tool would not work in those situations.

      My recommendation to everyone is even if you have a xref tool check the source members too.

      Delete
  2. Simon, these are very useful.. this is what I use the most.. thanks for sharing , great examples..

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.