Pages

Wednesday, September 14, 2022

Creating a reusable SQL View for a spool file

For the last few years I have been asked more often for a download, that can be loaded into Microsoft Excel, rather than paper report. Custom reports can be easily modified to include a "download file" option. Reports from within the ERP is more complicated. If I modify the ERP's report then I become responsible for it. Most ERP vendors will not provide support for any object I have modified. And then if an updated version of the object becomes available from the ERP, I would have to add my changes again. The solution I use is to generate the report, then "slice and dice" the spool file into a "download file".

I could copy the spool file into a physical file and then "slice and dice" using a RPG program. I don't have to do that anymore. I can build a SQL View using the SPOOLED_FILLE_DATA table function for my report, and then use standard SQL to "slice and dice" the contents into the columns I desire.

In this example I am going to use a report everyone with IBM i can generate, the output from the WRKOUTQ command, and as we all have this command you can copy my example to understand what I am doing.

I am using ACS's Run SQL Scripts to run all of these statements.

First I need a spool file, which I will generate in Run SQL Scripts with the following statement:

CL:WRKOUTQ OUTPUT(*PRINT) ;

As this statement starts with CL: Run SQL Scripts know that this is a CL command, and will execute it, generating my spool file.

I will be getting the information I need for this spool file from the OUTPUT_QUEUE_ENTRIES_BASIC View. It is quicker to get information from this View rather than the full OUTPUT_QUEUE_ENTRIES, as the basic view only contains a subset of the data that is returned from the full View.

I will be needing my profile to retrieve the data I need from the basic view. Rather than hard code in my user profile name, I can use the USER SQL environmental variable. I can see what it contains with this simple statement:

VALUES USER ;

This returns:

00001
-----
SIMON

Now I can use that environmental variable in place of hard coding my user profile in the following statement:

01  SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER 
02    FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 
03   WHERE USER_NAME = USER
04   ORDER BY CREATE_TIMESTAMP DESC
05   LIMIT 1 ;

Line 1: I only need the spool file name, job name, and file number of the spool file.

Line 3: Only select rows for the current user.

Line 4: Ordering the results by the create timestamp in descending order will ensure that the most recent spool file will be first in the list.

Line 5: By selecting only one row I get the information for the latest spool file, the one I just generated using WRKOUTQ.

The results are:

SPOOLED_FILE_NAME  JOB_NAME               FILE_NUMBER
-----------------  ---------------------  -----------
QPRTSPLQ           651713/RPGPGM/QPRTJOB            3

Rather than me have to copy the job name and file number into all the following statements I am going to move those values into SQL global variables. Let me start with the global variable I will be using for the job name, MY_JOB_NAME.

01  CREATE OR REPLACE VARIABLE MYLIB.MY_JOB_NAME FOR SYSTEM NAME MYJOBNAME
       VARCHAR(28) ;

02  SET MY_JOB_NAME = (SELECT JOB_NAME FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 
                        WHERE SPOOLED_FILE_NAME = 'QPRTSPLQ'
                          AND USER_NAME = USER 
                        ORDER BY CREATE_TIMESTAMP DESC LIMIT 1) ;

03  VALUES MY_JOB_NAME ;

Line 1: Creates the global variable in my library.

Line 2: I am setting the job name from my last WRKOUTQ spool file into the global variable.

Line 3: Show the value in the global variable, see below.

00001
---------------------
651713/RPGPGM/QPRTJOB

Now I am going to do the same for the file number creating a global variable called MY_FILE_NUMBER.

01  CREATE OR REPLACE VARIABLE MYLIB.MY_FILE_NUMBER FOR SYSTEM NAME MYFILENBR
      DEC(6,0) ;

02  SET MY_FILE_NUMBER = (SELECT FILE_NUMBER FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 
                           WHERE SPOOLED_FILE_NAME = 'QPRTSPLQ'
                             AND USER_NAME = USER 
                           ORDER BY CREATE_TIMESTAMP DESC LIMIT 1) ;

03  VALUES MY_FILE_NUMBER ;

When I check what the global variable contains I see:

00001
-----
    3

Now I can use the global variables to view the contents of the spool file I generated using the SPOOLED_FILE_DATA table function:

01  SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(
02                      JOB_NAME => MY_JOB_NAME,
03                      SPOOLED_FILE_NAME => 'QPRTSPLQ',
04                      SPOOLED_FILE_NUMBER => MY_FILE_NUMBER)) ;

The results are:

ORDINAL_
POSITION  SPOOLED_DATA
--------  --------------------------------------------------------------
       1  5770SS1  V7R5M0  220415            Work With All Output Queues
       2  Queue        Library       Files    Writer       Status
       3  QFAXOUTQ     QFAX              0                  RLS
       4  QFQOUTQ      QFAX              0                  RLS
       5  MKUNTZ       QGPL              5                  RLS
       6  MKUNTZ2      QGPL              0                  RLS
       7  NOKO         QGPL              0                  RLS
       8  NOKO2        QGPL              0                  RLS
       9  QDKT         QGPL              0                  RLS
      10  QPFROUTQ     QGPL              0                  RLS

I need to find what I call a "uniqueness", this allows me to identify the rows I want. In this case the Files column must contain a value 0 – 9. I know that is in the 33rd column, so my statement for just the rows I am interested in is:

SELECT SPOOLED_DATA 
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(MY_JOB_NAME,'QPRTSPLQ',MY_FILE_NUMBER))
 WHERE SUBSTR(SPOOLED_DATA,33,1) BETWEEN '0' AND '9' ;

The Where clause is where I define the values I am looking for my "uniqueness".

The results are:

SPOOLED_DATA
--------------------------------------------------------------
QFAXOUTQ     QFAX              0                  RLS
QFQOUTQ      QFAX              0                  RLS
MKUNTZ       QGPL              5                  RLS
MKUNTZ2      QGPL              0                  RLS
NOKO         QGPL              0                  RLS
NOKO2        QGPL              0                  RLS
QDKT         QGPL              0                  RLS
QPFROUTQ     QGPL              0                  RLS

Now I have identified the rows I need I can substring out the four columns of information I need:

  1. Output queue name
  2. Output queue library
  3. Number of spool files
  4. Output queue status

My new statement looks like:

SELECT SUBSTR(SPOOLED_DATA,2,10) AS "Outq",  
       SUBSTR(SPOOLED_DATA,15,10) AS "Library",
       DEC(SUBSTR(SPOOLED_DATA,29,5),6,0) AS "No. files",       
       SUBSTR(SPOOLED_DATA,52,3) AS "Status"
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(MY_JOB_NAME,'QPRTSPLQ',MY_FILE_NUMBER))
 WHERE SUBSTR(SPOOLED_DATA,33,1) BETWEEN '0' AND '9' ;

The results show the columns I created.

Outq      Library  No. files  Status
--------  -------  ---------  ------
QFAXOUTQ  QFAX             0  RLS
QFQOUTQ   QFAX             0  RLS
MKUNTZ    QGPL             5  RLS
MKUNTZ2   QGPL             0  RLS
NOKO      QGPL             0  RLS
NOKO2     QGPL             0  RLS
QDKT      QGPL             0  RLS
QPFROUTQ  QGPL             0  RLS

Having the data I want from the spool file I can now create View that I can use again:

CREATE OR REPLACE VIEW MYLIB.WORK_OUTPUT_QUEUE
  FOR SYSTEM NAME "MYWRKOUTQ"
(LIBRARY,OUTPUT_QUEUE,FILES,STATUS) AS
(SELECT SUBSTR(SPOOLED_DATA,15,10),SUBSTR(SPOOLED_DATA,2,10),       
       DEC(SUBSTR(SPOOLED_DATA,29,5),6,0),SUBSTR(SPOOLED_DATA,52,3)
  FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(MY_JOB_NAME,'QPRTSPLQ',MY_FILE_NUMBER))
 WHERE SUBSTR(SPOOLED_DATA,33,1) BETWEEN '0' AND '9') ;

Notice that I gave this View a short system name of MYWRKOUTQ. This will be needed later.

What happens if this spool file is deleted? I do so using the QCMDEXC SQL procedure as I need to create the command string with variables in it.

CALL QSYS2.QCMDEXC('DLTSPLF FILE(QPRTSPLQ)
                              JOB(' || MY_JOB_NAME || ') 
                              SPLNBR(' || CHAR(MY_FILE_NUMBER) || ')') ;

Warning: This statement needs to be on one line to work, not split apart are shown above.

Can I use this View with another spool file generated by WRKOUTQ?

I moved a spool file to the output queue QFAX/QFAXOUTQ so that we could see a difference between the two reports.

I create a new spool file:

CL:WRKOUTQ OUTPUT(*PRINT) ;

Then I am going to have to set my global variables with the job name and file number for the new spool file:

SET MY_JOB_NAME = (SELECT JOB_NAME FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 
                    WHERE SPOOLED_FILE_NAME = 'QPRTSPLQ'
                      AND USER_NAME = USER 
                    ORDER BY CREATE_TIMESTAMP DESC LIMIT 1) ;

SET MY_FILE_NUMBER = (SELECT FILE_NUMBER FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 
                       WHERE SPOOLED_FILE_NAME = 'QPRTSPLQ'
                         AND USER_NAME = USER 
                       ORDER BY CREATE_TIMESTAMP DESC LIMIT 1) ;

I can now check these global variables:

VALUES (MY_JOB_NAME,MY_FILE_NUMBER) ;

The results show that the job name has not changed, and I have a new file number:

00001                  00002
---------------------  -----
651713/RPGPGM/QPRTJOB      8

As I used the global variables in the View then the View will now show the contents of the new spool file:

SELECT * FROM WORK_OUTPUT_QUEUE LIMIT 1;

I only need to return the first row of the results as that is for the output queue QFAX/QFAXOUTQ.

Outq      Library  No. files  Status
--------  -------  ---------  ------
QFAXOUTQ  QFAX             1  RLS

The results show that there is now one spool file in this output queue, the one I created before running WRKOUTQ for the second time, showing that the View contains the latest results.

I can put all of the pertinent parts into a program, in this example a CL program:

01  PGM

02  DCL VAR(&PATH) TYPE(*CHAR) LEN(70) +
          VALUE('/home/SIMON/work_output_queue.csv')

    /* Set global variables to new values */
03  RUNSQL SQL('+
      SET MY_JOB_NAME = (SELECT JOB_NAME +
                           FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC +
                          WHERE SPOOLED_FILE_NAME = ''QPRTSPLQ'' +
                            AND USER_NAME = USER +
                          ORDER BY CREATE_TIMESTAMP DESC +
                          LIMIT 1)')
04  RUNSQL SQL('+
      SET MY_FILE_NUMBER = (SELECT FILE_NUMBER +
                              FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC +
                             WHERE SPOOLED_FILE_NAME = ''QPRTSPLQ'' +
                               AND USER_NAME = USER +
                             ORDER BY CREATE_TIMESTAMP DESC +
                             LIMIT 1)')

    /* Copy to IFS as CSV */
05  DEL OBJLNK(&PATH)
06  MONMSG MSGID(CPF0000)

07  CPYTOIMPF FROMFILE(MYWRKOUTQ) +
                TOSTMF(&PATH) +
                FROMCCSID(37) +
                STMFCCSID(*PCASCII) +
                RCDDLM(*CRLF) +
                ADDCOLNAM(*SQL)

08  ENDPGM

Line 2: I am declaring a variable for the path name.

Lines 3 and 4: Changing the values of the global values used by the View.

Lines 5 and 6: Delete the file in the IFS, if it exists.

Line 7: CL cannot handle the long SQL View name, therefore, I have given the system name, MYWRKOUTQ. The CPYTOIMPF command will create a CSV file in the IFS in the place and with the name given in the path variable. CSV files can be opened in Microsoft Excel, and being a lot smaller than a XLSX it takes up takes up less space in the IFS.

Thanks to SQL this is a simple process to transform a spool file to a CSV file. Now you need to go and try this with spool files your ERP generates that the users want to have as a spreadsheet.

If you want go get to the same information that is displayed by WRKOUTQ do not use the above, use the OUTPUT_QUEUE_INFO View instead.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

No comments:

Post a Comment

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.