Creating PDF using SQL

Update Novmber 23, 2022: Simplification of the values I can use for the parameters, see here.

---

Several years ago I wrote about how to convert spool files to PDF in the IFS. It still remains one of the most popular posts in this blog. With the latest round of Technology Refreshes for 7.4 and 7.3 there is now a way I can generate PDF from spool files using SQL.

I have several spool files I can see using the Work With Spool Files command, WRKSPLF:

Work with All Spooled Files Opt File User Queue User Data Sts _ QPRTSPLQ SIMON MYOUTQ RDY _ QSYSPRT SIMON MYOUTQ Testing RDY _ QPQUPRFIL SIMON MYOUTQ RDY _ QSYSPRT SIMON MYOUTQ RDY

I can get the same information using SQL too using the View OUTPUT_QUEUE_ENTRIES_BASIC, which gives me a subset of the columns that are available in OUTPUT_QUEUE_ENTRIES, which makes it faster to return the results:

01 SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 02 USER_DATA,STATUS,TOTAL_PAGES 03 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 04 WHERE USER_NAME = 'SIMON'

The information I need can be found using WRKSPLF, but with this SQL statement I have everything I need in one set of results:

SPOOLED_ FILE_ USER_ FILE_NAME JOB_NAME NUMBER DATA STATUS --------- ------------------------ ------ ------ ------ QPRTSPLQ 305753/SIMON/QPADEV0003 1 <NULL> READY QSYSPRT 305753/SIMON/QPADEV0003 2 Testing READY QPQUPRFIL 305753/SIMON/QPADEV0003 3 <NULL> READY QSYSPRT 306398/SIMON/QPADEV0003 1 <NULL> READY

I have configured my "Run SQL Scripts" to display <NULL> when a value is null.

GENERATE_PDF is a Db2 for i Scalar function that resides in the schema/library SYSTOOLS. Scalar functions cannot be called directly. In the example, below, I am using VALUES to execute it.

01 VALUES SYSTOOLS.GENERATE_PDF 02 (JOB_NAME => '9999999/QUSER/MYJOBNAME', 03 SPOOLED_FILE_NAME => 'QSYSPRT', 04 SPOOLED_FILE_NUMBER => 9, 05 PATH_NAME => '/home/SIMON/file.pdf') ;

It has four parameters I need to pass to it:

1. JOB_NAME:  Name of the job that generated the spool file
2. SPOOLED_FILE_NAME:  Spool file's name
3. SPOOLED_FILE_NUMBER:  Spool file's number
4. PATH_NAME:  Name of generated PDF, and the path name of the directory it is to be put into

And there is a value that is returned:

• 1 = Statement completed successfully
• -1 = Statement did not complete successfully

If the statement is really wrong you can get zero or null returned too.

All four of the input parameters need to be given for the Function to work.

I can also use it with giving the parameters' names:

VALUES SYSTOOLS.GENERATE_PDF('9999999/QUSER/MYJOBNAME','QSYSPRT',9, '/home/SIMON/file.pdf') ;

To make it easier for my own testing I have defined a Global Variable to contain the name of the spool files' job. If I want to use another job's spool files all I need to do is change the value in the Global Variable.

01 CREATE OR REPLACE VARIABLE MYLIB.TEST_JOB_NAME VARCHAR(28) ; 02 SET TEST_JOB_NAME = '305753/SIMON/QPADEV0003' ;

Line 1: Statement to create the Global Variable, TEST_JOB_NAME, in my library.

Line 2: Set the value to be contained with the Global Variable. This is the job name of the job that generated the first of my spool files.

And after all of that here is my first time using this Function:

01 VALUES SYSTOOLS.GENERATE_PDF 02 (JOB_NAME => TEST_JOB_NAME, 03 SPOOLED_FILE_NAME => 'QPRTSPLQ', 04 SPOOLED_FILE_NUMBER => 1, 05 PATH_NAME => '/home/SIMON/test1.pdf')

The following is displayed in the bottom half of my "Run SQL Scripts" so I know the statement completed successfully:

00001 ------ 1

I can interrogate the IFS to check if the file was generated by using the IFS_OBJECT_STATISTICS table function:

SELECT PATH_NAME,OBJECT_TYPE FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/SIMON')) ;

The results show that the PDF was created:

PATH_NAME OBJECT_TYPE --------------------- ----------- /home/SIMON *DIR /home/SIMON/test1.pdf *STMF

When I was "playing" with this Function I managed to generate an error message that included the CL command that is executed to copy the spool file to the IFS:

Message Generate_PDF: FAILED WITH SQLCODE=-443 SQLSTATE=38501 MESSAGE=Trigger program or external routine detected an error. COMMAND=QSYS/CPYSPLF FILE(QPRTSPLQ) TOFILE(*TOSTMF) JOB(*) SPLNBR(3) TOSTMF('/home/SIMON') WSCST(*PDF) STMFOPT(*REPLACE) returned from SIGNAL, RESIGNAL, or RAISE_ERROR.

If I want to copy a spool file that I generated in the current job I can replace the job name with "*":

VALUES SYSTOOLS.GENERATE_PDF('*','QPRTSPLQ',1,'/home/SIMON/test1.pdf') ;

I found something that, in my humble opinion, reduces the usefulness of this function. I always need to give the file number. Most spool file commands allow me to enter "*LAST" or "LAST" and it will use the last spool file of that name generated by my job. Alas, I cannot use either in GENERATE_PDF.

VALUES SYSTOOLS.GENERATE_PDF('*','QPRTSPLQ','*LAST','/home/SIMON/test1.pdf') ;

When I do I get an error. Searching my job log, using the JOBLOG_INFO I can see why the statement failed:

SELECT MESSAGE_SECOND_LEVEL_TEXT FROM TABLE(QSYS2.JOBLOG_INFO('*')) ORDER BY ORDINAL_POSITION DESC LIMIT 1 ; MESSAGE_SECOND_LEVEL_TEXT ------------------------------------------------------------------------- &N Cause . . . . . : A character in the argument for a casting function was not correct or the argument could not be converted to a numeric value. &N Recovery . . . : Change the argument to contain a valid representation of a value for the result data type. If the argument must be a numeric value, make sure the value can be implicitly cast to a numeric data type. If this is a cast, change the result data type to one that recognizes the characters in the argument. Try the request again.

If I am writing a program and I need to copy the generated spool file to a PDF I will be using the Copy Spool File command, CPYSPLF, to do this as it allows me to use "*LAST" in the file number field.

I have to admit that this next example is my version of one IBM gave for this Function. Here I want to copy all of my spool files to PDF in my IFS folder:

01 SELECT JOB_NAME,SPOOLED_FILE_NAME,FILE_NUMBER, 02 SYSTOOLS.GENERATE_PDF(JOB_NAME, 03 SPOOLED_FILE_NAME, 04 FILE_NUMBER, 05 '/home/SIMON/' || 06 REPLACE(JOB_NAME,'/','-') || 07 '_' || 08 SPOOLED_FILE_NAME || 09 '_' || 10 FILE_NUMBER || 11 '_' || 12 IFNULL(USER_DATA,'noUSRDTA') || 13 '.pdf') 14 AS "Return code", 15 '/home/RPGPGM/' || REPLACE(JOB_NAME,'/','-') || '_' || 16 SPOOLED_FILE_NAME || '_' || FILE_NUMBER || '_' || 17 IFNULL(USER_DATA,'noUSRDTA') || '.pdf' 18 AS "Path name" 19 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 20 WHERE STATUS = 'READY' 21 AND USER_NAME = 'SIMON' ;

This statement takes the results from the OUTPUT_QUEUE_ENTRIES_BASIC View for all of my spool files that are ready to print, and uses the GENERATE_PDF to generate a PDF in my IFS folder, and list the path name of that file.

Line 1: Columns from the OUTPUT_QUEUE_ENTRIES_BASIC View for the particular spool file. These are used by the GENERATE_PDF.

Lines 2 – 13: This may look complicated, but it is not. Lines 2 – 4 are the first three parameters needed by this function. Lines 5 – 13 is creating the path name for the PDF file. As the slashes ( / ) in the job name cannot be in the path name I am replacing them with the hyphen ( - ), and I am using the underscore ( _ ) as a separator. I am also adding the user data information to the file name, line 12, if there is no user data the value is null. I am using the IFNULL to change that to something else that I think is easier for someone to understand.

As I explained earlier when the GENERATE_PDF is used it returns a return code that will be in these results.

Lines 15 – 18: Is the same as lines 5 – 13 just not formatted nicely.

Lines 20 and 21: These selection criteria will only return in the results any spool files that are ready to print and have been generated by me.

The results look like:

SPOOLED_ FILE_ Return JOB_NAME FILE_NAME NUMBER Code ----------------------- --------- ------ ------ 305753/SIMON/QPADEV0003 QPRTSPLQ 1 1 305753/SIMON/QPADEV0003 QSYSPRT 2 1 305753/SIMON/QPADEV0003 QPQUPRFIL 3 1 306398/SIMON/QPADEV0003 QSYSPRT 1 1 Path name -------------------------------------------------------------- /home/SIMON/305753-SIMON-QPADEV0003_QPRTSPLQ_1_noUSRDTA.pdf /home/SIMON/305753-SIMON-QPADEV0003_QSYSPRT_2_Testing.pdf /home/SIMON/305753-SIMON-QPADEV0003_QPQUPRFIL_3_noUSRDTA.pdf /home/SIMON/306398-SIMON-QPADEV0003_QSYSPRT_1_noUSRDTA.pdf

All of the return codes are "1", and I can confirm that the files were copied to my IFS folder using the following SQL statement:

SELECT PATH_NAME,OBJECT_TYPE FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/SIMON')) ; OBJECT PATH_NAME _TYPE ------------------------------------------------------------- ------ /home/SIMON *DIR /home/SIMON/305753-SIMON-QPADEV0003_QPRTSPLQ_1_noUSRDTA.pdf *STMF /home/SIMON/305753-SIMON-QPADEV0003_QPQUPRFIL_3_noUSRDTA.pdf *STMF /home/SIMON/306398-SIMON-QPADEV0003_QSYSPRT_1_noUSRDTA.pdf *STMF /home/SIMON/305753-SIMON-QPADEV0003_QSYSPRT_2_Testing.pdf *STMF

If I wanted to copy all of the spool files in an output queue I would just need to change the WHERE clause:

20 WHERE STATUS = 'READY' 21 AND OUTPUT_QUEUE_LIBRARY_NAME = 'QGPL' 22 AND OUTPUT_QUEUE_NAME = 'MYOUTQ' ;

The rest of the statement remains unchanged.

 

You can learn more about the GENERATE_PDF 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.