Pages

Tuesday, December 17, 2019

Equivalent of DSPPGM and DSPSRVPGM using SQL part 1

dsppgm and dspsrvpgm command data from sql views

I am sure we are all familiar with the Display Program, DSPPGM, and with the Display Service Program commands, DSPSRVPGM as a way to get all sorts of information about the program or service program entered. My two biggest complaints about these commands are:

  1. I can only get the information for one program at a time
  2. The only output options offered are display or print, no output to file

With the latest Technology Refreshes, IBM i 7.4 TR1 and 7.3 TR7, there are a number of new Db2 for i, or SQL, Views to give me the same information I was getting from the commands.

In this post I will be describing two of these Views:

I will describe the other two in a later post.

In this post I am going to describe the information I get by using the DSPPGM and DSPSRVPGM commands, and the equivalent using these two SQL Views.

 

PROGRAM_INFO View

This View contains the information for all programs and service programs, yes all, in the IBM i partition I happen to be using. I had to remember to always use a WHERE clause to only select the data about the programs in my library, MYLIB, rather than take minutes gathering and returning the results from all libraries.

This View will returns the equivalent of the DSPPGM and DSPSRVPGM commands used with the following DETAIL parameters:

  • *BASIC
  • *SIZE
  • *SIGNATURE

I must have used the following commands several millions of times in my career of working with this operating system:

DSPPGM PGM(MYLIB/PROGRAM1) DETAIL(*BASIC)

Which displays:

                     Display Program Information

Program  . . . . . : PROGRAM1      Library  . . . : MYLIB
Owner  . . . . . . : SIMON
Program attribute  : RPGLE
Detail . . . . . . : *BASIC

Program creation information:
  Type of program  . . . . . . . . . . . . : ILE
  Program entry procedure module . . . . . : PROGRAM1
    Library  . . . . . . . . . . . . . . . :   QTEMP
  Activation group attribute . . . . . . . : QILE

  Number of modules  . . . . . . . . . . . : 3
  Number of service programs . . . . . . . : 4

Most of the time I am only interested in the information I show above:

  • The program entry procedure, which I call the "PEP"
  • Activation group
  • Number of modules and service programs bound to this program

By using the PROGRAM_INFO I can get this information for all of the programs in my library by using the following statement:

01  SELECT PROGRAM_NAME AS "Name",
02         PROGRAM_ATTRIBUTE AS "Attrib",
03         OBJECT_TYPE AS "Obj typ",
04         PROGRAM_TYPE AS "Type",
05         PROGRAM_ENTRY_PROCEDURE_MODULE AS "PEP module",
06         PROGRAM_ENTRY_PROCEDURE_MODULE_LIBRARY As "PEP lib",
07         ACTIVATION_GROUP AS "Act Grp",
08    FROM QSYS2.PROGRAM_INFO
09    WHERE PROGRAM_LIBRARY= 'MYLIB'
10      AND OBJECT_TYPE = '*PGM'

Lines 1 – 7: the long names of the column provide a good description of what each column in the View contains. I am giving the columns shorter headings so that they will fit in the width of this post.

Line 8: The View is found in the library, or schema, QSYS2.

Lines 9 and 10: As I mentioned before I only what the results from the objects in my library, line 9. And I am only interested in program objects in this example.

The results look like:

Name       Attrib Obj typ Type PEP Module PEP lib Act Grp
---------- ------ ------- ---- ---------- ------- ----------
PROGRAM1   RPGLE  *PGM    ILE  PROGRAM1   QTEMP   QILE
PROGRAM2   RPGLE  *PGM    ILE  MAINMOD    MYLIB   QILE
RPG3PGM    RPG    *PGM    OPM  -          -       -
SQLRPGPGM  RPGLE  *PGM    ILE  SQLRPGPGM  QTEMP   *DFTACTGRP

RPG3PGM is an old fashioned RPG3 program, therefore, it belongs in the OPM rather than the modern ILE. It does not have a "PEP" or activation group, which is why these columns show a value of null.

If I had excluded line 10 from the SQL statement then the results would have included information about the service programs too.

One thing that service programs contain, that programs do not, are export signatures. There are two ways I could use the DSPSRVPGM command to get to this information:

DSPSRVPGM SRVPGM(MYLIB/SRVPGM1) DETAIL(*BASIC)

Which returns the information of the current signature:

                Display Service Program Information

Service program  . . . . . : SRVPGM1
  Library  . . . . . . . . :   MYLIB
Owner  . . . . . . . . . . : SIMON
Service program attribute  : RPGLE
Detail . . . . . . . . . . : *BASIC

Service program creation information:

 Current export signature . : 000000D3F73456595021AA5518407548

Or I can get a list of the signatures, past and present.

DSPSRVPGM SRVPGM(MYLIB/SRVPGM1) DETAIL(*SIGNATURE)

                Display Service Program Information

Service program  . . . . . : SRVPGM1
  Library  . . . . . . . . :   MYLIB
Owner  . . . . . . . . . . : SIMON
Service program attribute  : RPGLE
Detail . . . . . . . . . . : *SIGNATURE

                    Signatures:

 000000D3F73456595021AA5518407548

I can get to the same information using the View like this:

01  SELECT PROGRAM_NAME AS "Name",
02         OBJECT_TYPE AS "Obj typ",
03         PROGRAM_TYPE AS "Typ",
04         ACTIVATION_GROUP AS "ActGrp",
05         SIGNATURES AS "S",
06         EXPORT_SIGNATURES AS "Export signatures"
07    FROM QSYS2.PROGRAM_INFO
08   WHERE PROGRAM_LIBRARY= 'MYLIB'
09     AND OBJECT_TYPE = '*SRVPGM'

Line 9: Here I have only selected the object type of service program.

The results look like:

Name    Obj typ Typ ActGrp  S Export signatures
------- ------- --- ------- - --------------------------------
SRVPGM1 *SRVPGM ILE *CALLER 1 000000D3F73456595021AA5518407548
SRVPGM2 *SRVPGM ILE *CALLER 1 0000000000C5D4C9F251361420124216

The export signatures column is a BLOB of 557038 characters, and it contains a list of all the 16 character signatures, separated by the hexadecimal value of '00', for the service program. In this example each service program only has one signature.

I could use the DSPPGM and DSPSRVPGM to get the size of the program or service program object, and the size limits for them.

DSPPGM PGM(MYLIB/PROGRAM1) DETAIL(*SIZE)

DSPSRVPGM SRVPGM(MYLIB/PROGRAM1) DETAIL(*SIZE)

The information displayed is basically the same, no matter which command is used. In this example I have used the DSPPGM command:

                     Display Program Information

Program  . . . . . : PROGRAM1      Library  . . . : MYLIB
Owner  . . . . . . : SIMON
Program attribute  : RPGLE
Detail . . . . . . : *SIZE

Program size (decompressed) and limit information:
 Total program size (KB):
   Current  . . . . . . . : 188
   Maximum  . . . . . . . : 268435456
 Number of modules:
   Current  . . . . . . . : 3
   Maximum  . . . . . . . : 32767
 Number of service programs:
   Current  . . . . . . . : 4
   Maximum  . . . . . . . : 32766

The equivalent SQL statement using the PROGRAM_INFO View would look like:

01  SELECT PROGRAM_NAME AS "Name",
02         OBJECT_TYPE AS "Obj typ",
03         PROGRAM_SIZE AS "Pgm size",  
04         MAXIMUM_PROGRAM_SIZE AS "Max size",
05         MODULES AS "Mods",
06         MAXIMUM_MODULES AS "Max mods",
07         SERVICE_PROGRAMS AS "Srvpgms",
08         MAXIMUM_SERVICE_PROGRAMS AS "Max SP",
09    FROM QSYS2.PROGRAM_INFO
10   WHERE PROGRAM_LIBRARY = 'MYLIB'

The returned results look like:

Name      Obj typ Size Max size  Mods Max mods Srvpgms Max SP
--------- ------- ---- --------- ---- -------- ------- ------
PROGRAM1  *PGM     188 268435456    3    32767       4  32766
PROGRAM2  *PGM     164 268435456    3    32767       4  32766
RPG3PGM   *PGM     -   -            -    -           -  -
SQLRPGPGM *PGM     196 268435456    1    32767       3  32766
SRVPGM1   *SRVPGM  196 268435456    4    32767       4  32766
SRVPGM2   *SRVPGM  128 268435456    2    32767       5  32766

If I had to get the object size's for any of the objects in a library I prefer to use the OBJECT_STATISTICS SQL table function:

SELECT OBJNAME,OBJSIZE
FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB',
                                   '*PGM *SRVPGM'))

From my examples it looks like there is not much information in this View for OPM programs. There is a whole series of columns in the results which will only contain information for OPM programs. For example if I wanted to know which source file the program was compiled from I could just use the following SQL statement:

01  SELECT PROGRAM_NAME AS "Name",
02         PROGRAM_ATTRIBUTE AS "Attrib",
03         SOURCE_FILE_LIBRARY AS "Src lib",
04         SOURCE_FILE AS "Src file",
05         SOURCE_FILE_MEMBER AS "Src mbr"
06    FROM QSYS2.PROGRAM_INFO
07   WHERE PROGRAM_LIBRARY= 'MYLIB'
08     AND PROGRAM_TYPE = 'OPM'

Line 8: I have limited these results to just OPM objects.

Name    Atrrib Src lib Src file Src mbr
------- ------ ------- -------- -------
RPG3PGM RPG    MYLIB   T2SRC    RPG3PGM

 

PROGRAM_EXPORT_IMPORT_INFO View

This View returns the equivalent of the DSPPGM and DSPSRVPGM commands used with the following DETAIL parameters:

  • *ACTGRPEXP
  • *ACTGRPIMP
  • *PROCEXP - DSPSRVPGM only
  • *DTAEXP - DSPSRVPGM only

The command I would use for all of these details for a service program would be:

DSPSRVPGM SRVPGM(RPGPGM2/SRVPGM1) DETAIL(*ACTGRPEXP
                                         *ACTGRPIMP
                                         *DTAEXP
                                         *PROCEXP)

The equivalent SQL statement using this View would be:

01  SELECT PROGRAM_NAME AS "Name",
02         OBJECT_TYPE AS "Obj typ",
03         SYMBOL_NAME AS "Symbol name",
04         SYMBOL_USAGE AS "Sym usage",
05         ARGUMENT_OPTIMIZATION AS "Arg opt"
06    FROM QSYS2.PROGRAM_EXPORT_IMPORT_INFO
07   WHERE PROGRAM_LIBRARY = 'RPGPGM2'

The results are:

Name    Obj typ Symbol name   Sym usage Arg opt
------- ------- ------------- --------- -------
SRVPGM1 *SRVPGM NOTHINGHERE   *PROCEXP  *NO
SRVPGM1 *SRVPGM SNOOZEVILLE   *PROCEXP  *NO
SRVPGM1 *SRVPGM WHATAWASTE    *PROCEXP  *NO
SRVPGM1 *SRVPGM YESITISBORING *PROCEXP  *NO
SRVPGM2 *SRVPGM MODULE8       *PROCEXP  *NO
SRVPGM2 *SRVPGM ONEMORETIME   *PROCEXP  *NO

The symbol name column illustrates the weird and wonderful names I come up for procedures at 11:30 PM.

 

You can learn more about this from the IBM website:

 

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

5 comments:

  1. I cannot find the PROGRAM_INFO file on my system. Is there a different name for it?

    ReplyDelete
    Replies
    1. If you are searching for the object using something like PDM then the View is not called PROGRAM_INFO to the system. Its system name is PGM_INFO in the library QSYS2.

      If you cannot find that object in your IBM i partition then you either are not using release 7.3 or 7.4, or you are not up to date with PTFs for those releases.

      Delete
  2. Simon,

    First of all, your articles are very helpful!

    Do you know if there is a way to get a list of procedures a program references? For example, I have Program A which uses procedures in a service program. Program A uses 3 procedures in a service program that contains 8 procedures.

    Is there some SQL view that will show me Program A uses procedures 2, 3, and 7, along with their names, and hopefully the service program name?

    Thank you,

    Doug

    ReplyDelete
    Replies
    1. I cannot think of something that comes as part of "vanilla" IBM i that would give you a list of the actually procedures executed.

      Delete
  3. EXCELLENT info in both articles.

    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.