QCMDEXC scalar function can write to job log

The QCMDEXC SQL scalar function is one of my favorite SQL features. I have used it many times in my work, and here in articles on this website. As part of the latest Technology Refresh, IBM i 7.6 TR2 and 7.5 TR8, it is further enhanced by now being able to write the command performed to the job log.

QCMDEXC scalar function now has two parameters:

1. COMMAND:  The CL command to perform
2. PRINT:  Whether the CL command should be written to the job log. This can contain the following:
   • ERROR:  Only write to the job log if the command fails
   • NONE:  No command is written to the job log. This is the default
   • VERBOSE:  The command is always written to the job log

Before I start describing in details what this change does, I need to have something I can do using the QCMDEXC scalar function. In this scenario I have a DDL table, TESTTABLE, that contains a list of files that I want to delete using a Delete File command, DTLF, in this scalar function.

I can show the contents of this table with the following:

01 SELECT * FROM TESTTABLE

Which returns:

LIBRARY FILE ------- --------- QTEMP WORKFILE1 QTEMP NOT_THERE QTEMP WORKFILE2

As the name of the second row hints, there is not a file called NOT_THERE. It is here to demonstrate what happens when the command in the QCMDEXC cannot be performed.

Let me show my first example of the enhanced scalar function:

01 SELECT LIBRARY,FILE, 02 CASE 03 WHEN QSYS2.QCMDEXC( 04 COMMAND => 'DLTF ' || LIBRARY || '/' || FILE, 05 PRINT => 'VERBOSE') = 1 THEN 'Yes' 06 ELSE 'No' 07 END "Deleted?" 08 FROM TESTTABLE

Line 1: I want to show the library and file names.

Lines 2 – 7: Rather than show the return codes from the QCMDEXC scalar function, "1" completed successfully and "-1" for failed, I am using a Case statement to translate those values to "Yes" and "No".

Line 5: I am using the verbose mode for the Print parameter, therefore, a message will be written to the job log after every execution of the scalar function.

When the above statement is executed, the following results are displayed:

LIBRARY FILE Deleted? ------- --------- -------- QTEMP WORKFILE1 Yes QTEMP NOT_THERE No QTEMP WORKFILE2 Yes

As I expected the two work files were deleted, and NOT_THERE errored as the file does not exist.

I used the SQL table function JOBLOG_INFO to display the entries in the Job Log for the current job:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('*'))

Line 2: The asterisk ( * ) is used to denote the current job.

The above displays:

MESSAGE_ID MESSAGE_TYPE MESSAGE_TEXT ---------- ------------ ------------------------------------------------------ CPC2191 COMPLETION Object WORKFILE1 in QTEMP type *FILE deleted. <NULL> INFORMATIONAL QCMDEXC success: DLTF QTEMP/WORKFILE1 CPF2105 ESCAPE Object NOT_THERE in QTEMP type *FILE not found. SQL0443 DIAGNOSTIC Trigger program or external routine detected an error. <NULL> INFORMATIONAL QCMDEXC failure: DLTF QTEMP/NOT_THERE CPC2191 COMPLETION Object WORKFILE2 in QTEMP type *FILE deleted. <NULL> INFORMATIONAL QCMDEXC success: DLTF QTEMP/WORKFILE2

I have "cheated" and inserted a blank line between the entries for each file to make it easier for us to see the job log entries for each of the delete commands.

The lines with the null message ids are the entries written by QCMDEXC. Notice that they all start with "QCMDEXC", followed by whether the command executed successfully, and lastly the CL command that was executed.

In this next example I only want to write to the job log when the QCMDEXC fails:

01 SELECT LIBRARY,FILE, 02 CASE 03 WHEN QSYS2.QCMDEXC('DLTF ' || LIBRARY || '/' || FILE,'ERROR') 04 = 1 THEN 'Yes' 05 ELSE 'No' 06 END "Deleted?" 07 FROM TESTTABLE

Line 3: Rather than list the parameter names, as I did before, I can list the values in the order of the parameters, Command followed by Print. Notice that the second parameter, print, is for "ERROR".

The results of this statement are the same as before:

LIBRARY FILE Deleted? ------- --------- -------- QTEMP WORKFILE1 Yes QTEMP NOT_THERE No QTEMP WORKFILE2 Yes

When I use the statement to view the job log entries, I get different results:

MESSAGE_ID MESSAGE_TYPE MESSAGE_TEXT ---------- ------------ ------------------------------------------------------ CPC2191 COMPLETION Object WORKFILE1 in QTEMP type *FILE deleted. CPF2105 ESCAPE Object NOT_THERE in QTEMP type *FILE not found. SQL0443 DIAGNOSTIC Trigger program or external routine detected an error. <NULL> INFORMATIONAL QCMDEXC failure: DLTF QTEMP/NOT_THERE CPC2191 COMPLETION Object WORKFILE2 in QTEMP type *FILE deleted.

As I used the "ERROR" value for the Print parameter only the error entry was written to the job log.

What I really like about these new QCMDEXC job log entries are that I can select them all to view how many of my commands were successful or not. This is not a big deal with just three entries, but if I had a larger number, say a hundred, then the following statement would be very useful:

01 SELECT MESSAGE_ID,MESSAGE_TYPE,MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('*')) 03 WHERE MESSAGE_ID IS NULL 04 AND MESSAGE_TEXT LIKE 'QCMDEXC%'

Line 3: The entries that QCMDEXC write to the job log all have a message id of null.

Line 4: And the message text all start with "QCMDEXC".

As I said above, by all means three results are not many. But you can understand why this would be useful for a larger number of results.

MESSAGE_ID MESSAGE_TYPE MESSAGE_TEXT ---------- ------------ ------------------------------------------------------ <NULL> INFORMATIONAL QCMDEXC success: DLTF QTEMP/WORKFILE1 <NULL> INFORMATIONAL QCMDEXC failure: DLTF QTEMP/NOT_THERE <NULL> INFORMATIONAL QCMDEXC success: DLTF QTEMP/WORKFILE2

IMHO this addition is something I am going to start using today, with the verbose Print option, as I will be able to see the exact command that was executed. This will make any error analysis so much simpler.

 

You can learn more about the QCMDEXC SQL scalar function from the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.