Thursday, June 24, 2021

QCMDEXC scalar function added to SQL

qcmdexc scalar function to run cl commands in select statements etc

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.

16 comments:

  1. wow, great work!

    ReplyDelete
  2. Thanks for sharing

    ReplyDelete
  3. Fantastic! Can I run an external *PGM ?!

    ReplyDelete
    Replies
    1. I don't see why you would not be able to.

      Delete
    2. I built my own named CMDEXC that does this same thing and I used it to manage IFS items using shell commands.

      -Matt

      Delete
  4. WHERE (JOB_QUEUE_LIBRARY,JOB_QUEUE_NAME) 05 IN (SELECT JOBQ_LIBRARY,JOBQ_NAME 06 FROM MONTH_END_JOBQS 07 WHERE JOBQ_FLAG = '1')

    I didn't know one could just use parentheses to combine two or more columns like that. I would have used concatenation before learning this.

    ReplyDelete
  5. Thanks for posting

    ReplyDelete
  6. SQL pareciera ir evolucionando a ser un lenguaje multiproposito

    ReplyDelete
    Replies
    1. Sí, hoy en día se puede hacer mucho más con SQL

      Delete
  7. ¡Excelente! Esta publicación es muy útil. A sacarle provecho.

    ReplyDelete
  8. I have been looking for ways to do this multiple select as well. Maybe this warrants a blog post of its own! Bill Pahl

    ReplyDelete
    Replies
    1. Is this what you are looking for here.

      Delete
  9. Simon, great article, as usual. How are you guaranteed jobq_queue_info.jobq_queue_status is retrieved before QCMDEXC is run?

    ReplyDelete
    Replies
    1. I have been using this a lot recently with all kinds of things.
      I have only ever seen it return the before values, if you think about it that is the way the statement must work. It retrieves the results from the table/view/whatever and then the QCMDEXC table function is executed.

      Delete
  10. Thank you for the article, it has been most usfull to me
    I used QCMDEXC in a query where I select old files in an IFS folder and delete them at the same time

    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.