Wednesday, December 28, 2022

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.

1 comment:

  1. Useful...Now if only PRTCMDUSG worked with CLLE's so those of us who don't have X-Analysis or other "where used" tools could get that info without brute force coding...

    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.