SQL View gives ability to see information about all commands

IMHO it has been problematic to get information about more than one IBM i command. The Display Command command, DSPCMD, will only output to display or print one command at a time. If I want to get information about more than one, or all, commands then I would need to use an API.

Fortunately with the fall 2022 Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, comes a SQL View that contains all the information that the DSPCMD command does for all commands.

This new View, COMMAND_INFO, is simple to use. For example, if I want to list all the information about all the commands in this partition I could just use:

SELECT * FROM QSYS2.COMMAND_INFO

This returns too many results to be good for examples here. I am just going to work with the commands in my libraries:

-- Quick check of commands in my libraries 01 SELECT COMMAND_LIBRARY AS "Lib", 02 COMMAND_NAME AS "Cmd", 03 PROXY_COMMAND AS "Proxy" 04 FROM QSYS2.COMMAND_INFO 05 WHERE COMMAND_LIBRARY LIKE 'MYLIB%' 06 ORDER BY 1,2

Lines 1 - 3: I only want these three columns returned:

• COMMAND_LIBRARY:  The library the command is in. I am giving this column the heading 'Lib'
• COMMAND_NAME:  The name of the command. Column heading for this column will be 'Cmd'
• PROXY_COMMAND:  Whether it is a proxy command or not. Column heading will be 'Proxy'

Line 4: This is where I am getting results from, the COMMAND_INFO View.

Line 5: Here I state that I only want commands in my libraries. I do that using a LIKE and the SQL wildcard character, %, at the end of the library name.

Line 6: I want the results sorted by the first column, library, and then the second column, name.

The results show I have five commands in my libraries:

Lib Cmd Proxy ------- ------ ----- MYLIB2 GOTO2 NO MYLIB3 LOOP NO MYLIB3 SP YES MYLIB3 WM YES MYLIB3 WO YES

I have two commands that are not proxies. I can find out more about them with the following statement:

-- Non proxy commands 01 SELECT COMMAND_LIBRARY AS "Lib", 02 COMMAND_NAME AS "Cmd", 03 SOURCE_FILE_LIBRARY AS "Src lib", 04 SOURCE_FILE AS "Src file", 05 SOURCE_FILE_MEMBER AS "Src mbr", 06 COMMAND_PROCESSING_PROGRAM_LIBRARY AS "CPP lib", 07 COMMAND_PROCESSING_PROGRAM AS "CPP pgm", 08 VALIDITY_CHECKING_PROGRAM_LIBRARY AS "Val lib", 09 VALIDITY_CHECKING_PROGRAM AS "Val pgm" 10 FROM QSYS2.COMMAND_INFO 11 WHERE COMMAND_LIBRARY LIKE 'MYLIB%' 12 AND PROXY_COMMAND = 'NO' 13 ORDER BY 1,2

Lines 1 – 9: These are the columns I have selected to appear in my results. I think that the columns describes what information each columns contains. As the column names are so long I have given them shorter column headings.

Line 11: Just commands from my libraries.

Line 12: And they are not proxy commands.

Line 13: Sort by the first two columns: command library and command name.

The results are:

Lib Cmd Src lib Src file Src mbr CPP lib CPP pgm Val lib Val pgm ------- ----- ------- -------- ------- ------- -------- ------- ------- MYLIB2 GOTO2 MYLIB2 DEVSRC GOTO2 MYLIB2 GOTO2CL <NULL> <NULL> MYLIB3 LOOP MYLIB3 TWOSRC LOOP *LIBL LOOPCLLE <NULL> <NULL>

Neither of these commands have a validity checking program, so the validity checking library and program name columns are null.

If I wanted to know which commands by proxy commands are proxies of I could use the following:

-- Proxy commands 01 SELECT COMMAND_LIBRARY AS "Lib", 02 COMMAND_NAME AS "Cmd", 03 PROXY_TARGET_COMMAND_LIBRARY AS "Proxy lib", 04 PROXY_TARGET_COMMAND AS "Proxy" 05 FROM QSYS2.COMMAND_INFO 06 WHERE COMMAND_LIBRARY LIKE 'MYLIB%' 07 AND PROXY_COMMAND = 'YES' 08 ORDER BY 1,2

Lines 1 – 4: These are the columns I have selected. The ones I have shown before are the two that only apply to proxy commands:

• PROXY_TARGET_COMMAND_LIBRARY:  Library that contains the target (original) command
• PROXY_TARGET_COMMAND:  The target command

Line 7: In the AND I have given Yes to stipulate I just want the results for proxy commands.

The results are:

Lib Cmd Proxy lib Proxy ------- --- ---------- --------- MYLIB3 SP *LIBL WRKSPLF MYLIB3 WM *LIBL WRKMBRPDM MYLIB3 WO *LIBL WRKOBJPDM

There are columns within the COMMAND_INFO View that allows me to determine which modes a command can be run in. I chose these three commands for this example:

• DCL  Used within CL programs to define variables. Cannot be used outside of a CL program.
• SIGNOFF  You should only be able to signoff a job if it is running interactively. If I wanted to end a job in batch I would use the End Job command, ENDJOB, instead.
• CLRPFM:  You can use this command everywhere.

My statement looks like:

-- What modes can command be used in 01 SELECT COMMAND_NAME AS "Cmd", 02 ALLOW_RUN_INTERACTIVE AS "A.R.I", 03 ALLOW_RUN_BATCH AS "R.B", 04 ALLOW_RUN_EXEC AS "A.R.E", 05 ALLOW_RUN_BATCH_ENTRY AS "A.R.B.E", 06 ALLOW_RUN_INTERACTIVE_ENTRY AS "A.R.I.E", 07 ALLOW_RUN_BATCH_PGM AS "A.R.B.P", 08 ALLOW_RUN_INTERACTIVE_PGM AS "A.R.I.P" 09 FROM QSYS2.COMMAND_INFO 10 WHERE COMMAND_LIBRARY = 'QSYS' 11 AND COMMAND_NAME IN ('DCL','SIGNOFF','CLRPFM') 12 ORDER BY 1

Lines 1 – 8: The column names for these columns are just too long, therefore, I have given every one of them a new column heading that is are the initials of the name. Below is the description of all these columns' contents:

• COMMAND_NAME:  Name of the command. Column heading = 'Cmd'
• ALLOW_RUN_INTERACTIVE:  Can the command be run interactively, and not in a CL program. Column heading = 'A.R.I'
• ALLOW_RUN_BATCH:  Can the command be run batch, and not in a CL program. Column heading = 'A.R.B'
• ALLOW_RUN_EXEC:  Can the command be run in the QCMDEXC, QCAEXC, and QCAPCMD API programs. Column heading = 'A.R.E'
• ALLOW_RUN_BATCH_ENTRY:  Can the command be used in a CL program running in batch. Command heading = 'A.R.B.E''
• ALLOW_RUN_INTERACTIVE_ENTRY:  Can the command be used in a CL program running interactively. Column heading = 'A.R.I.E'
• ALLOW_RUN_BATCH_PGM:  Can the command be submitted from a CL program running in batch. Column heading = 'A.R.B.P'
• ALLOW_RUN_INTERACTIVE_PGM:  Can the command be submitted from a CL program running interactively. Column heading = 'A.R.I.P'

Line 10: All of these command reside in the QSYS library.

Line 11: I am using the IN so I can list the commands I am interested in on one line.

Line 12: Sort the results by the first column, command name.

The results are as follows:

Cmd A.R.I A.R.B A.R.E A.R.B.E A.R.I.E A.R.B.P A.R.I.P ------- ----- ----- ----- ------- ------- ------- ------- CLRPFM YES YES YES YES YES YES YES DCL NO NO NO YES YES NO NO SIGNOFF YES NO YES NO YES NO NO

CLRPFM can be executed in any scenario.

DCL can only be used within a program, in batch and interactively.

The last scenario I have a situation where some people use a command and others the use the same named command and different things happen. I can use this View to check for all the occurrences of commands with the same name and in different libraries. Here I am going to look for all the occurrences of the SIGNOFF command:

-- Is there more than one version of a command 01 SELECT COMMAND_LIBRARY AS "Lib", 02 COMMAND_NAME AS "Cmd", 03 PROXY_COMMAND AS "Proxy" 04 FROM QSYS2.COMMAND_INFO 05 WHERE COMMAND_NAME = 'SIGNOFF'

Lines 1 – 3: We all know what these columns contain. I have given them all short column headings.

Line 5: I only want to select the rows where the command name is SIGNOFF.

This returns four results:

Lib Cmd Proxy ----------- ------- ----- QSYS SIGNOFF NO QSYSV7R3M0 SIGNOFF NO QSYSV7R4M0 SIGNOFF NO QSYS38 SIGNOFF NO

Having four commands of the same name in different libraries would have me check the library lists of the people have this issue to determine which people are using which version of the command.

This is going to prove useful so I can compare the differences between commands in any library or libraries.

I have only shown a few of the columns in these examples. Check the documentation, linked below, to see what other columns are available.

 

You can learn more about the COMMAND_INFO SQL View from the IBM website here.

 

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