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.