I was recently asked how it is possible to search all the source members in QCLSRC to find which program called a particular Cobol program. I am sure most of you reading this will immediately shout "Find string PDM!", and you are right.
I thought that this would be a good excuse to explain the Find String PDM command, FNDSTRPDM, to those who are not familiar with it. And I will then give an example CL program to show of how I could search one or all of the source files in a library, which is really a reminder of things I have written about before: SQL, do loop, subroutine, changing name of spool files.
I know that there are third party tools that do this too. I have used several of them, but I am not going to mention them here. This post is going to just use the native IBM i features and commands.
I think most developers are aware that if you want to scan a source member or members you can just go into PDM and put '25', "Find string", next to the members you want search, and press Enter. You can do the same using the FNDSTRPDM command, see below. In this example I want to print a list of members with the string "cobol" in them. In the command prompts I have entered my input in lower case so you can see what is mine and what is the command's default values.
Find String Using PDM (FNDSTRPDM)
Find 'string' . . . . . . . . . STRING cobol
File . . . . . . . . . . . . . . FILE mysrc
Library . . . . . . . . . . . *LIBL
Member . . . . . . . . . . . . . MBR *all
+ for more values
Operation to perform: OPTION
Option . . . . . . . . . . . . *none
Prompt . . . . . . . . . . . . *NOPROMPT
Additional Parameters
Columns to search: COL
From column . . . . . . . . . 1
To column . . . . . . . . . . *RCDLEN
Kind of match . . . . . . . . . CASE *IGNORE
Print list . . . . . . . . . . . PRTMBRLIST *yes
Print records: PRTRCDS
Number to find . . . . . . . . *NONE
Print format . . . . . . . . .
Mark record . . . . . . . . .
Record overflow . . . . . . .
Parameters . . . . . . . . . . . PARM
|
When I press Enter a spool file is created, QPUOPRTF, that contains the members that match the selection criteria I chose.
Having given the person who asked this question the answer, use FNDSTRPDM, I began to think how I could make it easier to use. I would want to have the option to search all the source files in a library for a string, and be able to easily distinguish which spool file came from which source file. I could write a fairly simple CL program that would include the following that I written about before:
- SQL within CL
- Creating temporary table in SQL
- SQL view SYSTABLES
- Do loop in CL
- Subroutine in CL
- Changing name of spool file
The "top" of the program looks very straight forward. There are four parameters that would need to be passed to this program:
01 PGM PARM(&STRING &SRCFILE &SRCFLIB &RTNCDE)
02 DCL VAR(&STRING) TYPE(*CHAR) LEN(30)
03 DCL VAR(&SRCFILE) TYPE(*CHAR) LEN(10)
04 DCL VAR(&SRCFLIB) TYPE(*CHAR) LEN(10)
05 DCL VAR(&RTNCDE) TYPE(*CHAR) LEN(1)
06 DCL VAR(&SQL) TYPE(*CHAR) LEN(200)
07 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')
08 DCLF FILE(QTEMP/WORKFILE)
09 CHGVAR VAR(&RTNCDE) VALUE(' ')
|
- &STRING - Search string
- &SRCFILE - If only one source file to be searched then will contain the source file name. If I want to search all source files in a library then will contain "*ALL"
- &SRCFLIB - The library containing the source file(s)
- &RTNCDE - This is the return code passed back to whatever call this program. If it is not blank then an error was encountered
The next step is to validate that the library name entered does exist. This is simply done using the Check Object command, CHKOBJ, is used on line 10. If the library does not exist the program ends and returns a value of '1'.
10 CHKOBJ OBJ(&SRCFLIB) OBJTYPE(*LIB)
11 MONMSG MSGID(CPF0001 CPF9801) EXEC(DO)
12 CHGVAR VAR(&RTNCDE) VALUE('1')
13 RETURN
14 ENDDO
|
Then I need to validate the source file name, if a name and not "*ALL was passed. The CHKOBJ command is used again. If the a file of the passed name is not found the program ends and returns a value of '2', lines 18 – 19.
15 IF COND(&SRCFILE *NE '*ALL') THEN(DO)
16 CHKOBJ OBJ(&SRCFLIB/&SRCFILE) OBJTYPE(*FILE)
17 MONMSG MSGID(CPF0001 CPF9801) EXEC(DO)
18 CHGVAR VAR(&RTNCDE) VALUE('2')
19 RETURN
20 ENDDO
21 CALLSUBR SUBR(FINDSTRING)
22 RETURN
23 ENDDO
|
If the file was found, the subroutine FINDSTRING is called, line 21, and then the program ends.
What remains is what to do if "*ALL" has been entered as the source file. What I am going to do is to make a work file that contains all of the source files in the source file library, and then read the work file.
I am going to use SQL to create a table and extract the data from the SQL View SYSTABLES into this table, called WORKFILE.
24 RUNSQL SQL('DROP TABLE QTEMP.WORKFILE') +
COMMIT(*NONE) NAMING(*SQL)
25 MONMSG MSGID(SQL9010)
26 CHGVAR VAR(&SQL) +
VALUE('CREATE TABLE QTEMP.WORKFILE AS +
(SELECT SYSTEM_TABLE_NAME,+
SYSTEM_TABLE_SCHEMA,+
FILE_TYPE +
FROM QSYS2.SYSTABLES +
WHERE FILE_TYPE = ''S'' +
AND SYSTEM_TABLE_SCHEMA = ''' +
|| &SRCFLIB |< ''') +
WITH DATA')
27 RUNSQL SQL(&SQL) COMMIT(*NONE) NAMING(*SQL)
|
Line 24: I always like to try and delete a work file/table to make sure it is gone before I try and build a new one. SQL's DROP TABLE is the equivalent of DTLF. I have also chosen to use the SQL naming convention, therefore, the separator between the library and file is a period/full stop/dot (depending on where you are in the world).
Line 26: As I cannot use substitution variables in the Run SQL command, RUNSQL, I am building my SQL statement in a variable, &SQL, where I can insert my source library name into the WHERE clause.
Line 27: The statement is executed. I have not monitored this line because if it fails I want to know about it.
Having created the work file now I need to read it.
28 DOWHILE COND(&LOOP) 29 RCVF 30 MONMSG MSGID(CPF0000) EXEC(LEAVE) 31 CHGVAR VAR(&SRCFILE) VALUE(&SYS_TNAME) 32 CHGVAR VAR(&SRCFLIB) VALUE(&SYS_DNAME) 33 CALLSUBR SUBR(FINDSTRING) 34 ENDDO |
Line 28: For those of you not familiar with the Do loop in CL I have to condition it with a logical variable, which is defined on line 7.
Line 29: Work file is read.
Lines 31 and 32: Contents of the file fields are moved into the variables that are used in the subroutine.
Line 33: Subroutine is called.
The subroutine is simple it just contains two commands:
36 SUBR SUBR(FINDSTRING)
37 OVRPRTF FILE(QPUOPRTF) USRDTA(&SRCFLIB) +
SPLFNAME(&SRCFILE) OVRSCOPE(*CALLLVL)
38 FNDSTRPDM STRING(&STRING) +
FILE(&SRCFLIB/&SRCFILE) +
MBR(*ALL) OPTION(*NONE) +
PRTMBRLIST(*YES)
39 ENDSUBR
|
Line 37: The first command overrides the printer file name so that the spool file name is the name of the source file and the user data is the source file library name.
Line 38: Here it is, finally, the FNDSTRPDM command.
I ran the program with the following parameters:
CALL FINDSTRING ('COBOL' '*ALL' 'MYLIB' &RTNCDE)
|
And four spool files were produced, each name after the source file:
Work with All Spooled Files
Device or Total
Opt File User Queue User Data Sts Pages
_ DEMOSRC SIMON MYOUTQ MYLIB RDY 1
_ DEVSRC SIMON MYOUTQ MYLIB RDY 2
_ EXAMPLES SIMON MYOUTQ MYLIB RDY 2
_ OLDSRC SIMON MYOUTQ MYLIB RDY 2
|
To be accurate you have to be sure that the source files match the objects. If they do not you can misleading results.
Having shown the program in parts this is what it looks like in its entirety:
01 PGM PARM(&STRING &SRCFILE &SRCFLIB &RTNCDE)
02 DCL VAR(&STRING) TYPE(*CHAR) LEN(30)
03 DCL VAR(&SRCFILE) TYPE(*CHAR) LEN(10)
04 DCL VAR(&SRCFLIB) TYPE(*CHAR) LEN(10)
05 DCL VAR(&RTNCDE) TYPE(*CHAR) LEN(1)
06 DCL VAR(&SQL) TYPE(*CHAR) LEN(200)
07 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')
08 DCLF FILE(QTEMP/WORKFILE)
09 CHGVAR VAR(&RTNCDE) VALUE(' ')
10 CHKOBJ OBJ(&SRCFLIB) OBJTYPE(*LIB)
11 MONMSG MSGID(CPF0001 CPF9801) EXEC(DO)
12 CHGVAR VAR(&RTNCDE) VALUE('1')
13 RETURN
14 ENDDO
15 IF COND(&SRCFILE *NE '*ALL') THEN(DO)
16 CHKOBJ OBJ(&SRCFLIB/&SRCFILE) OBJTYPE(*FILE)
17 MONMSG MSGID(CPF0001 CPF9801) EXEC(DO)
18 CHGVAR VAR(&RTNCDE) VALUE('2')
19 RETURN
20 ENDDO
21 CALLSUBR SUBR(FINDSTRING)
22 RETURN
23 ENDDO
24 RUNSQL SQL('DROP TABLE QTEMP.WORKFILE') +
COMMIT(*NONE) NAMING(*SQL)
25 MONMSG MSGID(SQL9010)
26 CHGVAR VAR(&SQL) +
VALUE('CREATE TABLE QTEMP.WORKFILE AS +
(SELECT SYSTEM_TABLE_NAME,+
SYSTEM_TABLE_SCHEMA,+
FILE_TYPE +
FROM QSYS2.SYSTABLES +
WHERE FILE_TYPE = ''S'' +
AND SYSTEM_TABLE_SCHEMA = ''' +
|| &SRCFLIB |< ''') +
WITH DATA')
27 RUNSQL SQL(&SQL) COMMIT(*NONE) NAMING(*SQL)
28 DOWHILE COND(&LOOP)
29 RCVF
30 MONMSG MSGID(CPF0000) EXEC(LEAVE)
31 CHGVAR VAR(&SRCFILE) VALUE(&SYS_TNAME)
32 CHGVAR VAR(&SRCFLIB) VALUE(&SYS_DNAME)
33 CALLSUBR SUBR(FINDSTRING)
34 ENDDO
35 RUNSQL SQL('DROP TABLE QTEMP.WORKFILE') +
COMMIT(*NONE) NAMING(*SQL)
/*====================================================*/
36 SUBR SUBR(FINDSTRING)
37 OVRPRTF FILE(QPUOPRTF) USRDTA(&SRCFLIB) +
SPLFNAME(&SRCFILE) OVRSCOPE(*CALLLVL)
38 FNDSTRPDM STRING(&STRING) +
FILE(&SRCFLIB/&SRCFILE) +
MBR(*ALL) OPTION(*NONE) +
PRTMBRLIST(*YES)
39 ENDSUBR
/*====================================================*/
40 ENDPGM
|
You can learn more about the FNDSTRPDM command from the IBM website here.
This article was written for IBM i 7.2, and should work for earlier releases too.
Another great tool for our tool kit, thanks Simon.
ReplyDeleteThank you , I am trying to implement it similarly but definitely very good post
ReplyDeletewhat is CPF0001 and how do we get SQL9010 monmsg for the various SQL commands run .
ReplyDeleteFor CL commands and its CPFXXXX, we can very well get from the IBM website or F1 on the command. - J
For any error message you can use the following command to see the message text for it: DSPMSGD RANGE(CPF0001) MSGF(QCPFMSG)
DeleteFor messages with that start with 'SQL' you can find the text using the same command as above, bit with a different message file: DSPMSGD RANGE(SQL9001) MSGF(QSQLMSG)
If you really want to get to the root of what the SQL error is then I recommend you add GET DIAGNOSTICS SQL statements, see here.
Or add code to check the SQLCOD field after every SQL statement you will have to go to IBM's KnowledgeCenter for their meaning, see here. You will probably have to look in the SQLCA data structure for more information too.
Thank you Simon - J
DeleteHi Simon, thanks for the great article; I am using it, but, is there any way that in the procedure that I could generate a file instead of a report.
ReplyDeleteFNDSTRPDM will only produce a spool file.
DeleteI guess it would depend upon what information you want in your file. You could just take the spool file, copy it to the IFS as a TXT and use that as your file.
Or you could write your own tool, trust me it is not that difficult, to scan source members looking for a string, and then you have total control of what you want as output.
Hi, First thank you.
ReplyDeleteI have on error with CL Compilation: &SYS_TNAME and &SYS_DNAME are not defined
Thank you.
&SYS_TNAME and &SYS_DNAME are from the created table QTEMP/WORKFILE.
DeleteIf you have created QTEMP/WORKFILE before you compile then the definition of these two variables will be found by the compiler as WORKFILE in defined in the DCLF command.