Wednesday, December 18, 2019

Equivalent of DSPPGM and DSPSRVPGM using SQL part 2

using bound_module_info and bound_srvpgm_info sql views to list objects bound to programs

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:

 

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

2 comments:

  1. This is really useful ...

    ReplyDelete
  2. This is exactly what I was looking for. Thanks.

    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.