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:
01 VALUES SYSTOOLS.GENERATE_PDF
02 (JOB_NAME => ‘504185/SIMON/SIMON_1’,
03 SPOOLED_FILE_NAME => ‘QSYSPRT’,
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:
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
(‘*’,’QSYSPRT’,’*LAST’,’/home/simon/file1.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.
