Equivalent of DSPPGM and DSPSRVPGM using SQL part 1

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:

• PROGRAM_INFO
• PROGRAM_EXPORT_IMPORT_INFO

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:

• PROGRAM_INFO View
• PROGRAM_EXPORT_IMPORT_INFO View

 

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