Wednesday, June 2, 2021

Creating PDF using SQL

generate pdf file from spool file using new 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.

21 comments:

  1. wonderful, I tried it and it works very fast
    Thanks Simon !!!

    ReplyDelete
  2. Thanks Simon, very didactic article. The first article I've seen regarding VALUES. I was wondering if a SQL statement stored in a SQL variable can be run using VALUES. The contains in the variable was built using a SQL compound statement. Thanks.

    ReplyDelete
  3. Jesus Radames LeonJune 2, 2021 at 1:09 PM

    Thanks for posting

    ReplyDelete
  4. Thank you for posting great tips!

    ReplyDelete
  5. Quick question:
    Should this line – “I can also use it with giving the parameters' names:”
    say instead – “I can also use it without giving the parameters' names:”?
    THANKS much for posting this!!!

    ReplyDelete
  6. Excellent post, really appreciated!

    ReplyDelete
  7. Reynaldo Dandreb MedillaJune 6, 2021 at 10:02 AM

    Another useful one Simon, thanks

    ReplyDelete
  8. Love this

    ReplyDelete
  9. Executing a cpysplf under the covered means that that qcmdexec sql function could also do the trick.

    ReplyDelete
  10. Excelente Simon, very fast thanks for sharing 👍

    ReplyDelete
  11. Thanks for posting Simon..

    ReplyDelete
  12. Thanks for sharing

    ReplyDelete
  13. Thank you Simon Hutchinson ..it is really helpful.

    ReplyDelete
  14. Looks like we'll have to wait for the upgrade to 7.4, so for now we're still using several other tools to create PDFs. Just depends on how fancy!

    ReplyDelete
  15. I'm curious, I'll tinker with this one.

    ReplyDelete
  16. Reynaldo Dandreb MedillaSeptember 26, 2021 at 9:12 AM

    thanks Simon, again a complete tips on the subject, good to know that the scalar function generate_pdf() is also running the cpysplf cmd under the hood.

    ReplyDelete
  17. Simon, Thank you for sharing! Great to have another tool!

    ReplyDelete
  18. Wonderful article. I have learned a lot. Very clear.
    I can share from my side the use of the SQL variable "user" the and use of a subquery:
    SELECT SYSTOOLS.GENERATE_PDF(JOB_NAME, SPOOLED_FILE_NAME, FILE_NUMBER, PATH), q.* FROM (SELECT JOB_NAME, SPOOLED_FILE_NAME, FILE_NUMBER, '/home/' CONCAT user CONCAT '/' CONCAT REPLACE(JOB_NAME,'/','-') CONCAT '_' CONCAT SPOOLED_FILE_NAME CONCAT '_' CONCAT FILE_NUMBER CONCAT '_' CONCAT IFNULL(USER_DATA,'noUSRDTA') CONCAT'.pdf' as PATH FROM OUTPUT_QUEUE_ENTRIES_BASIC WHERE STATUS = 'READY' AND USER_NAME = user) as q

    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.