Wednesday, November 23, 2022

Improvement to GENERATE_PDF

The GENERATE_PDF scalar function has been a wonderful addition to Db2 for i providing a simple way to convert a spool file to a PDF in the IFS.

My only complaint about it has been that I have to pass the spool file number to it, there was no '*LAST' option for the last spool file with that name. Fortunately with IBM i 7.5 and 7.4 TR6 came an enhancement to GENERATEPDF that allows '*LAST'.

Using ACS's "Run SQL Scripts" I could just do the following:

02         (JOB_NAME => '504185/SIMON/SIMON_1',
04          SPOOLED_FILE_NUMBER => '*LAST',
06          PATH_NAME => '/home/simon/file1.pdf')

I can use the VALUES statement followed by the scalar function, no need for a Select statement. You can see on line 4 that I have used '*LAST', which means it will find the most recent QSYSPRT spool file in that job and use that. The other parameters have not changed.

When I run the statement the returned value of '1' informs me that the statement was successful:


In reality I am more likely to use this is a program or procedure to copy a spool file that the program had just created. This small RPG program is an example of just the part that would convert the spool file to a PDF:

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-s ReturnCode packed(1) ;

04  exec sql CALL QSYS2.QCMDEXC('DEL OBJLNK(''/home/simon/file1.pdf'')') ;

05  exec sql SELECT SYSTOOLS.GENERATE_PDF                     
                    (JOB_NAME => '*',
                     SPOOLED_FILE_NAME => 'QSYSPRT',
                     SPOOLED_FILE_NUMBER => '*LAST',
                     PATH_NAME => '/home/SIMON/file1.pdf')
               INTO :ReturnCode
               FROM SYSIBM.SYSDUMMY1 ;

06  dsply ('ReturnCode = ' + %char(ReturnCode)) ;

07  *inlr = *on ;

Line 1: In 2022 it has to be totally free RPG.

Line 2: I always add this control option to make it easier for me when the program errors.

Line 3: This is the definition of the variable that will contain the value returned from GENERATE_PDF.

Line 4: I am using the QCMDEXC SQL procedure to execute the Delete object command, DEL, to delete the PDF if it already exists in my folder in the IFS.

Line 5: I have used a Select statement, rather than a Values, in this program. Even though I do not have a file I am getting data from I still need to define one. Here I have used Db2's dummy file, SYSDUMMY1 in the library SYSIBM. There is only one difference with parameters passed to the scalar function compared to what I showed above. In this statement I am only looking for spool file belonging to the current job, which is indicated by using '*' for the job name. The INTO moves the results from the Select statement into a variable, ReturnCode. As the scalar function returns a value I need a define a variable for it to be returned in.

Line 6: After the SQL statement is executed I am using RPG's Display operation code, DSPLY, to show the code returned by GENERATE_PDF.

After compiling this program when I call it the value returned by GENERATE_PDF is display on my monitor:

DSPLY  ReturnCode = 1

This means I successfully converted my spool file to PDF and copied it to the IFS.

In both of the SQL statements above I have given the parameter names as well as the values for them. The parameter names are optional, I can just list the values. This would change line 5 of the program to:

05  exec sql SELECT SYSTOOLS.GENERATE_PDF                              
               INTO :ReturnCode                                        
               FROM SYSIBM.SYSDUMMY1 ;

I will leave you to make up your mind which one to use. Personally I prefer the first version as it is "self-documenting", meaning by listing the parameter's names everyone know what the parameters are.


You can learn more about the GENERATE_PDF SQL scalar function from the IBM website here.


This article was written for IBM i 7.5 and 7.4 TR6.


  1. I have found that if I specify a spooled file that doesn't exist via incorrect spooled file name, number, etc., I don't get a result of -1. I get an ugly error message SQL State 38501, Vendor Code -438.

  2. Please don't propagate the use of SYSIBM.SYSDUMMY1 ! I still see a lot of code even new in systems with this notation.... just use a plain exec SQL SET :returncode ... or a values into ...


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.