Equivalent of DSPPGM and DSPSRVPGM using SQL part 2

At least once a day I use the Display Program command, DSPPGM, and the Display Service Program command, DSPSRVPGM, to look at the service programs and modules bound to ILE programs and service programs I am analyzing. While the command give me the information I need the latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, gives me another way to get the same information from two new SQL views:

1. BOUND_MODULE_INFO
2. BOUND_SRVPGM_INFO

In this post I am going to describe how I would use them to replace what I do with DSPPGM and DSPSRVPGM commands, and a few other pieces of information I have found that these Views make it easy for me to get to.

I have a small simple collection of eight modules (*MODULE), two service programs (*SRVPGM), and two programs (*PGM) that I will be using in these examples.

 

SRVPGM1 Service Program 1

This service program includes the following modules and service program:

• SRVPGM1
  • MODULE1 module
  • MODULE2 module
  • MODULE3 module
  • MODULE4 module
  • SRVPGM2 service program

I created this service program using the following command:

CRTSRVPGM SRVPGM(MYLIB/SRVPGM1) MODULE(MODULE1 MODULE2 MODULE3 MODULE4) EXPORT(*ALL) SRCFILE(DEVSRC) TEXT('Service Program 1') BNDSRVPGM((SRVPGM2))

Once created I can now use the DSPSRVPGM command to display which modules and service programs are in this service program:

DSPSRVPGM SRVPGM(SRVPGM1) DETAIL(*MODULE *SRVPGM)

This shows:

Display Service Program Information Service program . . . . . : SRVPGM1 Library . . . . . . . . : MYLIB Owner . . . . . . . . . . : SIMON Service program attribute : RPGLE Detail . . . . . . . . . . : *MODULE Opt Module Library Attribute _ MODULE1 MYLIB RPGLE _ MODULE2 MYLIB RPGLE _ MODULE3 MYLIB RPGLE _ MODULE4 MYLIB RPGLE Display Service Program Information Service program . . . . . : SRVPGM1 Library . . . . . . . . : MYLIB Owner . . . . . . . . . . : SIMON Service program attribute : RPGLE Detail . . . . . . . . . . : *SRVPGM Service Opt Program Library _ SRVPGM1 *LIBL _ QRNXIE QSYS _ QRNXUTIL QSYS _ QLEAWI QSYS

Now for the SQL Select statements using the new Views. As there are two Views, one for Modules and the other for Service programs, in this example I am going to give two separate SQL Select statements and results. I know I can combine them into one statement and one set of results, but I want to explain the separate statements and results. The first statement is to list the Modules in the Service Program:

01 SELECT BOUND_MODULE_LIBRARY AS "Mod lib", 02 BOUND_MODULE AS "Module", 03 MODULE_ATTRIBUTE AS "Type", 04 MODULE_CREATE_TIMESTAMP AS "Created", 05 SOURCE_FILE_LIBRARY AS "Src lib", 06 SOURCE_FILE AS "Src file", 07 SOURCE_FILE_MEMBER AS "Member" 08 FROM QSYS2.BOUND_MODULE_INFO 09 WHERE PROGRAM_LIBRARY = 'MYLIB' 10 AND PROGRAM_NAME = 'SRVPGM1' 11 AND OBJECT_TYPE = '*SRVPGM'

Lines 1 – 7: As I am using the long column names there is no need to describe what each of these columns will contain when I run this statement. I have defined different column headings to reduce the width of the result's titles when they are returned.

Line 8: The View I will be getting the results from.

Lines 9 – 11: It may seem a bit redundant but this View contains the information for all Modules in this IBM i partition, and that is lot of Modules! Therefore I have narrowed the selection to my library and the specific service program. I have add the OBJECT_TYPE selection as I could have a program (*PGM ) called SRVPGM1 in my library too. The results returned look like:

Mod lib Module Type Created Src lib Src file Member ------- ------- ----- ------------------- ------- -------- ------- MYLIB MODULE1 RPGLE 2019-12-18 00:18:17 MYLIB DEVSRC MODULE1 MYLIB MODULE2 RPGLE 2019-12-17 21:08:55 MYLIB DEVSRC MODULE2 MYLIB MODULE3 RPGLE 2019-12-17 21:26:28 MYLIB DEVSRC MODULE3 MYLIB MODULE4 RPGLE 2019-12-17 21:26:29 MYLIB DEVSRC MODULE4

The four modules I bound into the service program when I created it are listed.

Now for the list of service programs:

01 SELECT BOUND_SERVICE_PROGRAM_LIBRARY AS "Lib", 02 BOUND_SERVICE_PROGRAM AS "Srvpgm" 03 FROM QSYS2.BOUND_SRVPGM_INFO 04 WHERE PROGRAM_LIBRARY = 'MYLIB' 05 AND PROGRAM_NAME = 'SRVPGM1' 06 AND OBJECT_TYPE = '*SRVPGM'

Lines 1 and 2: I am only interested in these two columns. And like my previous example I want to use different column headings in the results.

Line 3: The View that the results are returned from.

Lines 4 – 6: Same selection criteria as I used in the previous example, and used for the same reasons.

The results are:

Lib Srvpgm ----- ------ *LIBL SRVPGM2 QSYS QRNXIE QSYS QRNXUTIL QSYS QLEAWI

The first service program I included in the command to create this service program. The other three, from the library QSYS, are included in every service program and program that contains RPG modules.

 

SRVPGM2 Service Program 2

The second service program is simpler than the first as it only contains modules.

• SRVPGM2
  • MODULE7 module
  • MODULE8 module

I used the following command to create this service program:

CRTSRVPGM SRVPGM(MYLIB/SRVPGM2) MODULE(MODULE7 MODULE8) EXPORT(*ALL) SRCFILE(DEVSRC) TEXT('Service Program 2')

Having created the service program I can now view the bound modules using the following command:

DSPSRVPGM SRVPGM(SRVPGM2) DETAIL(*MODULE)

The results show:

Display Service Program Information Service program . . . . . : SRVPGM2 Library . . . . . . . . : MYLIB Owner . . . . . . . . . . : SIMON Service program attribute : RPGLE Detail . . . . . . . . . . : *MODULE Opt Module Library Attribute _ MODULE7 MYLIB RPGLE _ MODULE8 MYLIB CLLE

The equivalent SQL statement would be:

01 SELECT BOUND_MODULE_LIBRARY AS "Mod lib", 02 BOUND_MODULE AS "Module", 03 MODULE_ATTRIBUTE AS "Type", 04 MODULE_CREATE_TIMESTAMP AS "Created" 05 FROM QSYS2.BOUND_MODULE_INFO 06 WHERE PROGRAM_LIBRARY = 'MYLIB' 07 AND PROGRAM_NAME = 'SRVPGM2' 08 AND OBJECT_TYPE = '*SRVPGM'

This SQL statement is pretty much the same as the one I used the list the modules in SRVPGM1, the only difference is the program name in the WHERE clause, line 7.

The results list the two modules I bound to create this service program:

Mod lib Module Type Created ------- ------- ----- ------------------- MYLIB MODULE7 RPGLE 2019-12-17 16:17:10 MYLIB MODULE8 CLLE 2019-12-17 21:26:07

 

PROGRAM1 Program 1

The first program, I have called PROGRAM1, contains the following objects:

• PROGRAM1
  • SRVPGM1 service program
  • MODULE5 module
  • MODULE6 module

I always place the objects I want to bind to a program in a binding directory. I created a binding directory for this program too, it contains the following:

Work with Binding Directory Entries Binding Directory: BNDDIR Library: MYLIB Object Type Library SRVPGM1 *SRVPGM *LIBL MODULE5 *MODULE *LIBL MODULE6 *MODULE *LIBL

Rather than have to remember to give the binding directory when I create program I always include a line in my RPG source code's control option for the binding directory:

01 **free 02 ctl-opt dftactgrp(*no) bnddir('BNDDIR') ;

I am going to create this program using the following command:

CRTBNDRPG PGM(MYLIB/PROGRAM1) SRCFILE(MYLIB/DEVSRC) OPTION(*SRCSTMT) DBGVIEW(*ALL)

Now the program has been created I can use the following command to list the modules and service programs it uses:

DSPPGM PGM(PROGRAM1) DETAIL(*MODULE *SRVPGM)

This displays:

Display Program Information Program . . . . . . : PROGRAM1 Library . . : MYLIB Owner . . . . . . . : RPGPGM Program attribute . : RPGLE Detail . . . . . . . : *MODULE Opt Module Library Attribute _ PROGRAM1 QTEMP RPGLE _ MODULE5 MYLIB RPGLE _ MODULE6 MYLIB RPGLE Display Program Information Program . . . . . . : PROGRAM1 Library . . : MYLIB Owner . . . . . . . : RPGPGM Program attribute . : RPGLE Detail . . . . . . . : *SRVPGM Service Opt Program Library _ SRVPGM1 *LIBL _ QRNXIE QSYS _ QRNXUTIL QSYS _ QLEAWI QSYS

Now the equivalents using the SQL Views. First the statement to list the Modules:

01 SELECT BOUND_MODULE_LIBRARY AS "Mod lib", 02 BOUND_MODULE AS "Module", 03 MODULE_ATTRIBUTE AS "Type" 04 FROM QSYS2.BOUND_MODULE_INFO 05 WHERE PROGRAM_LIBRARY = 'MYLIB' 06 AND PROGRAM_NAME = 'PROGRAM1' 07 AND OBJECT_TYPE = '*PGM'

Only the selection criteria changed from when I was retrieving the information about service programs. I give the program name on line 6, and the object type "*PGM", line 7, just in case there is a service program in my library with the same name.

The results are:

Mod lib Module Type ------- -------- ----- QTEMP PROGRAM1 RPGLE MYLIB MODULE5 RPGLE MYLIB MODULE6 RPGLE

As this program was created using the CRTBNDRPG command the main module of the program, which has the same name as the final program, is created in QTEMP before it is bound to the program.

I did find a difference between using the DSPPGM and DSPSRVPGM commands versus the BOUND_MODULE_INFO and BOUND_SRVPGM_INFO Views. The commands list all the Modules and Service Programs that have been bound to the program. The SQL Views only include the Module or Service Program if they have been bound and a procedure within them is called. If a Module has been bound and none of its procedures are called, the Module will not be returned in the Views' results.

And now the service programs:

01 SELECT BOUND_SERVICE_PROGRAM_LIBRARY AS "Lib", 02 BOUND_SERVICE_PROGRAM AS "Srvpgm" 03 FROM QSYS2.BOUND_SRVPGM_INFO 04 WHERE PROGRAM_LIBRARY = 'MYLIB' 05 AND PROGRAM_NAME = 'PROGRAM1' 06 AND OBJECT_TYPE = '*PGM'

The results are as I expected:

Lib Srvpgm ----- ------ *LIBL SRVPGM1 QSYS QRNXIE QSYS QRNXUTIL QSYS QLEAWI

 

PROGRAM2 Program 2

This program has the following objects bound to it:

• PROGRAM2
  • SRVPGM2 service program
  • MODULE7 module
  • MODULE8 module

This time I create the program using the CRTPGM command:

CRTPGM PGM(MYLIB/PROGRAM2) MODULE(MODULE5 MODULE6) ENTMOD(MAINMOD) BNDDIR(BNDDIR2)

BNDDIR2 contains SRVPGM2, which will be bound into the program when the command is executed.

This time with the SQL Selects I am going to combine the results from both of the SQL Views to give one set of results.

01 SELECT BOUND_MODULE_LIBRARY AS "Library", 02 BOUND_MODULE AS "Object", 03 '*MODULE' AS "Type" 04 FROM QSYS2.BOUND_MODULE_INFO 05 WHERE PROGRAM_LIBRARY = 'MYLIB' 06 AND PROGRAM_NAME = 'PROGRAM2' 07 AND OBJECT_TYPE = '*PGM' 08 UNION ALL 09 SELECT BOUND_SERVICE_PROGRAM_LIBRARY, 10 BOUND_SERVICE_PROGRAM, 11 '*SRVPGM' 12 FROM QSYS2.BOUND_SRVPGM_INFO 13 WHERE PROGRAM_LIBRARY = 'MYLIB' 14 AND PROGRAM_NAME = 'PROGRAM2' 15 AND OBJECT_TYPE = '*PGM'

By using the UNION clause I join the two sets of results into one "list":

Library Object Type ------- ------- ------- MYLIB MODULE5 *MODULE MYLIB MODULE6 *MODULE MYLIB MAINMOD *MODULE *LIBL SRVPGM2 *SRVPGM QSYS QRNXIE *SRVPGM QSYS QRNXUTIL *SRVPGM QSYS QLEAWI *SRVPGM

 

In which program is this Module used?

All the results in the examples above with these Views can just as easily be got using the equivalent commands, as I have shown. But this is something that you cannot do with the commands: which Programs and Service programs is this module used?

In this example I want to know where the modules MODULE1 and MODULE5 have been bound.

01 SELECT BOUND_MODULE_LIBRARY AS "Mod lib", 02 BOUND_MODULE AS "Module", 03 PROGRAM_LIBRARY AS "Pgm Lib", 04 PROGRAM_NAME AS "Program", 05 OBJECT_TYPE AS "Pgm type" 06 FROM QSYS2.BOUND_MODULE_INFO 07 WHERE BOUND_MODULE IN ('MODULE1','MODULE5') 08 ORDER BY 1,2

This statement took several minutes to run as it has to check the information for every Program and Service Program in this partition. The results are:

Mod lib Module Pgm lib Program Pgm type ------- ------- ------- ------- -------- MYLIB MODULE1 MYLIB SRVPGM1 *SRVPGM MYLIB MODULE5 MYLIB PROGRAM1 *PGM MYLIB MODULE5 MYLIB PROGRAM2 *PGM

 

Other information in the results

BOUND_MODULE_INFO and BOUND_SRVPGM_INFO Views contain a lot more information that I have given in the examples above. In the following example I want to know which Modules:

1. Allow debugging
2. Optimization level
3. If it is CL module does it allow the source to be retrieved using the RTVCLSRC command?

01 SELECT PROGRAM_LIBRARY AS "Lib", 02 PROGRAM_NAME AS "Pgm", 03 OBJECT_TYPE AS "Type", 04 BOUND_MODULE AS "Module", 05 OPTIMIZATION_LEVEL "Opt lvl", 06 DEBUG_DATA AS "Debug", 07 ALLOW_RTVCLSRC AS "RTVCLSRC" 08 FROM QSYS2.BOUND_MODULE_INFO 09 WHERE PROGRAM_LIBRARY = 'MYLIB'

The results are:

Lib Pgm Type Module Mod type Opt lvl Debug RTVCLSRC ------- -------- ------- -------- -------- ------- ----- -------- MYLIB PROGRAM1 *PGM PROGRAM1 RPGLE 10 *YES *NO MYLIB PROGRAM1 *PGM MODULE5 RPGLE 30 *YES *NO MYLIB PROGRAM1 *PGM MODULE6 RPGLE 30 *YES *NO MYLIB PROGRAM2 *PGM MODULE5 RPGLE 30 *YES *NO MYLIB PROGRAM2 *PGM MODULE6 RPGLE 30 *YES *NO MYLIB PROGRAM2 *PGM MAINMOD RPGLE 30 *YES *NO MYLIB SRVPGM1 *SRVPGM MODULE1 RPGLE 30 *YES *NO MYLIB SRVPGM1 *SRVPGM MODULE2 RPGLE 30 *YES *NO MYLIB SRVPGM1 *SRVPGM MODULE3 RPGLE 30 *YES *NO MYLIB SRVPGM1 *SRVPGM MODULE4 RPGLE 30 *YES *NO MYLIB SRVPGM2 *SRVPGM MODULE7 RPGLE 30 *YES *NO MYLIB SRVPGM2 *SRVPGM MODULE8 CLLE 10 *YES *YES

The higher the optimization level the faster the object run. For a description of the values click on the link at the bottom of this post to the documentation for this View.

All of the modules allow debug.

No surprise that the only CL module allows the use of the RTVCLSRC command.

 

There are many more columns returned in these Views' results than I have listed. Check out IBM's documentation to see what other information you can get to.

You can learn more about this from the IBM website:

• BOUND_MODULE_INFO view
• BOUND_SRVPGM_INFO view

 

This article was written for IBM i 7.4 TR1 and 7.3 TR7.