QCMDEXC scalar function added to SQL

I am sure everyone who has programmed using IBM i has encountered QCMDEXC. Most of us have used the QCMDEXC API, or the QCMDEXC procedure in SQL to execute CL commands from another program or a SQL statement. Included within the latest round of Technology Refreshes for 7.4 and 7.3 is a QCMDEXC scalar function.

A scalar function has to be executed as part of a SQL statement, a Select for example, rather than called directly. Like all the other scalar functions I know, it will return "1" when it executed successfully, and "-1" when it did not.

I have to admit I had to think for a while of a situation I could use this in. Then I remembered one where this could be a perfect fit.

Many years ago I came across a couple of programs that were part of a month end job to release and hold job queues that were used by the month end. There was a file that contained a list of job queues, the library they are found in, and what was called the "flag" field. All the flag was used for if it was not "1" the job queue was not processed.

I have built a SQL table to mimic that file:

01 CREATE OR REPLACE TABLE MYLIB.MONTH_END_JOBQS 02 FOR SYSTEM NAME "ME_JOBQ" 03 (JOBQ_LIBRARY FOR COLUMN "LIBRARY" CHAR(10), 04 JOBQ_NAME FOR COLUMN "JOBQ" CHAR(10), 05 JOBQ_FLAG FOR COLUMN "FLAG" CHAR(1)) 06 ON REPLACE DELETE ROWS ;

Lines 2 – 5: I have used long SQL names and given system compatible names for the Table and its columns.

Line 6: As the Table is created using the CREATE OR REPLACE I need to tell it what to do with any existing rows when it is replaced.

And I inserted the details of three job queues:

01 SELECT * FROM MONTH_END_JOBQS ; JOBQ_LIBRARY JOBQ_NAME JOBQ_FLAG ------------ --------- --------- QGPL QBATCH_MA 1 QGPL QBATCH_MB 1 QGPL QBATCH_MC 1

I can check the status of these three job queues by using the JOB_QUEUE_INFO View:

01 SELECT JOB_QUEUE_LIBRARY,JOB_QUEUE_NAME, 02 JOB_QUEUE_STATUS,NUMBER_OF_JOBS 03 FROM QSYS2.JOB_QUEUE_INFO 04 WHERE (JOB_QUEUE_LIBRARY,JOB_QUEUE_NAME) 05 IN (SELECT JOBQ_LIBRARY,JOBQ_NAME 06 FROM MONTH_END_JOBQS 07 WHERE JOBQ_FLAG = '1') ;

Lines 1 - 3: All pretty standard stuff. These are the four columns I want in the results, and this is where to get them from.

Lines 4 – 7: I only want to return the results for the job queues in the table I created, MONTH_END_JOBQS. There are only three so I could have hard coded their details into the statement. But I wanted to make this flexible. If add a fourth job queue to the table this statement will still work without needing to be changed.

Line 4: These are the two fields from the JOB_QUEUE_INFO View I can use to match to MONTH_END_JOBS. As there is more than one column I need to enclose them in parentheses ( ( ) ), and separate them with a comma.

Lines 5 – 7: The IN on line 5 means that to be included in the results the library and job queue name from line 4 must be included in the results. The two columns listed on line 5 are the two that are used to match with the ones defined on line 4. Line 7 means that only those rows where the flag is "1" will the row be included in the subselect.

The results are:

JOB_QUEUE_ JOB_QUEUE_ JOB_QUEUE_ NUMBER_ LIBRARY NAME STATUS OF_JOBS ---------- ---------- ---------- ------- QGPL QBATCH_MA HELD 10 QGPL QBATCH_MB HELD 23 QGPL QBATCH_MC HELD 18

The first program, that used the month end job queues file, ran near the start of the month end program. It was a CL program that would read the file (using RCVF command), if the flag was "1" is would change the change the status of the job queue to released using the Release Job Queue command, RLSJOBQ.

I can do the same with this statement:

01 SELECT A.*, 02 CASE 03 WHEN QSYS2.QCMDEXC('RLSJOBQ ' || 04 RTRIM(JOBQ_LIBRARY) || '/' || 05 JOBQ_NAME) = 1 THEN 'Jobq released' 06 ELSE 'Jobq not released' 07 END AS "Cmd status" 08 FROM MONTH_END_JOBQS A 09 WHERE JOBQ_FLAG = '1' ;

Line 1: I want all the columns from MONTH_END_JOBQS.

Lines 2 - 7: I am using this case statement to create string that will be returned in the results on whether the command with the QCMDEXC scalar function executed successfully.

Lines 3 – 5: The RLSJOBQ command is created by substring RLSJOBQ with the library name and job queue name. I know I have not used CONCAT, I used the double pipe ( || ) which means the same in Db2 for i. If the statement is successful a value of "1" is returned by the scalar function.

Line 6: If "1" is not returned then this line of the statement is executed.

When run the results are:

JOBQ_LIBRARY JOBQ_NAME JOBQ_FLAG Cmd status ------------ --------- --------- ------------- QGPL QBATCH_MA 1 Jobq released QGPL QBATCH_MB 1 Jobq released QGPL QBATCH_MC 1 Jobq released

And at the end of the month end job there is another program that holds the job queues. This is so similar to the previous statement. The only differences are that the Hold Job Queue command, HLDJOBQ, is used and the returned messages from the CASE statements are appropriate for what happened.

01 SELECT A.*, 02 CASE 03 WHEN QSYS2.QCMDEXC('HLDJOBQ ' || 04 RTRIM(JOBQ_LIBRARY) || '/' || 05 JOBQ_NAME) = 1 THEN 'Jobq held' 06 ELSE 'Jobq not held' 07 END AS "Cmd status" 08 FROM MONTH_END_JOBQS A 09 WHERE JOBQ_FLAG = '1' ;

The results are as expected:

JOBQ_LIBRARY JOBQ_NAME JOBQ_FLAG Cmd status ------------ --------- --------- ------------- QGPL QBATCH_MA 1 Jobq held QGPL QBATCH_MB 1 Jobq held QGPL QBATCH_MC 1 Jobq held

While playing with this scenario I found there were times when the releasing or holding would fail for a valid reason. For example, I cannot release a job queue that is already released or hold a job queue that is already held. To help determine if that is the cause of the failure of the scalar function I added an additional column to these statements. I added the JOB_QUEUE_STATUS from the JOB_QUEUE_INFO view:

01 SELECT A.*, 02 CASE 03 WHEN QSYS2.QCMDEXC('RLSJOBQ ' || 04 RTRIM(JOBQ_LIBRARY) || '/' || 05 JOBQ_NAME) = 1 THEN 'Jobq released' 06 ELSE 'Jobq not released' 07 END AS "Cmd status", 08 B.JOB_QUEUE_STATUS AS "Prv status" 09 FROM MONTH_END_JOBQS A 10 LEFT OUTER JOIN QSYS2.JOB_QUEUE_INFO B 11 ON A.JOBQ_LIBRARY = B.JOB_QUEUE_LIBRARY 12 AND A.JOBQ_NAME = B.JOB_QUEUE_NAME 13 WHERE A.JOBQ_FLAG = '1' ;

What did I have to change?

Line 8: Add the status column. I have called "Prv status" as it is the status before the QCMDEXC scalar function is executed.

Line 10 – 12: I have added the JOB_QUEUE_INFO as a left outer join just in case an entry in the MONTH_END_JOBQS does not exist. Lines 11 and 12 are the matching criteria, joining the data from the two data sources.

The newly added column shows why the releasing of the job queue QBATCH_MC failed:

JOBQ_ JOBQ_ JOBQ_ LIBRARY NAME FLAG Cmd status Prv status ------- --------- ----- ----------------- ---------- QGPL QBATCH_MA 1 Jobq released HELD QGPL QBATCH_MB 1 Jobq released HELD QGPL QBATCH_MC 1 Jobq not released RELEASED

And now the opposite, holding all of the job queues:

01 SELECT A.*, 02 CASE 03 WHEN QSYS2.QCMDEXC('RLSJOBQ ' || 04 RTRIM(JOBQ_LIBRARY) || '/' || 05 JOBQ_NAME) = 1 THEN 'Jobq held' 06 ELSE 'Jobq not held' 07 END AS "Cmd status", 08 B.JOB_QUEUE_STATUS AS "Prv status" 09 FROM MONTH_END_JOBQS A 10 LEFT OUTER JOIN QSYS2.JOB_QUEUE_INFO B 11 ON A.JOBQ_LIBRARY = B.JOB_QUEUE_LIBRARY 12 AND A.JOBQ_NAME = B.JOB_QUEUE_NAME 13 WHERE A.JOBQ_FLAG = '1' ;

The results show that the three job queues were held without issue:

JOBQ_ JOBQ_ JOBQ_ LIBRARY NAME FLAG Cmd status Prv status ------- --------- ----- ----------------- ---------- QGPL QBATCH_MA 1 Jobq held RELEASED QGPL QBATCH_MB 1 Jobq held RELEASED QGPL QBATCH_MC 1 Jobq held RELEASED

 

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

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.