If you prefer to specify the folder name directly, change the set PDF statement in the procedure’s code to avoid using the OUTQ_NAME in the concatenation process. Note that I also append the output queue name to this directory to force the PDF images into a subfolder within this directory. The PDF_DIR parameter identifies the IFS folder/directory where the PDF files should be stored. This procedure accepts up to five parameters.
So below I have the code for the CPYOUTQ2PDF SQL procedure. After all, functions aren’t referred to as stored functions, are they? Since user-defined procedure isn’t a thing, I prefer to use the simpler SQL procedure nomenclature that seems to have become pervasive. The term stored procedure has rubbed me the wrong way since the first time I heard it. To integrate this CPYSPLF command with the previous OUTPUT_QUEUE_ENTRIES UDTF, we can create a stored procedure, which is just an SQL way of saying an SQL script that gets compiled into a callable object. First let’s stub-out a simple copy-to-PDF statement using one of the spooled files from the above list. The easiest way to do that is with the CPYSPLF CL Command included with your system. Once we have a method of generating a list of spooled file names, we need a way to convert those spooled files to PDF. Turns out user-created functions also support this syntax, so naming your parameters clearly, as we do with CL Commands, is important. The OUTPUT_QUEUE_ENTRIES UDTF accepts the library and output queue names using either positional syntax or named parameter syntax (shown above). When I run this SQL statement, I might see something like the following: There are a number of other fields returned by the UDTF that you can use, but to copy to PDF, I only need the spool file name, job name, and spool file number. QSYS2.OUTPUT_QUEUE_ENTRIES(OUTQ_LIB => 'QUSRSYS', To produce a list of spooled files for an OUTQ named COZZI in library QUSRSYS, using this UDTF, I would code the following: SELECT SPOOLED_FILE_NAME, JOB_NAME, FILE_NUMBER To do that today, I would use the SQL “Service” OUTPUT_QUEUE_ENTRIES() table function (UDTF) located in the QSYS2 library. The CPYOUTQ CL command uses the QGYOLSPL “list” API to generate a list of spooled file names found on the OUTQ. About the only thing I really miss when using SQL and SQL Services vs legacy CL commands is the command prompter. While CL commands are easy to use, I find more and more processing is being done with SQL directly from within RPG IV or one of the SQL script environments.
If I were to write this command today, I would use SQL to do it. Although IBM has access to more efficient and lower-level interfaces, I find the benefit of simply coding “VALUES iQuery.SYSVAL(‘QCCSID’) INTO :sysCCSID” within RPG so much easier and less error prone.
The “SQL Services” effectively does the same thing.
Today we have the ability to create SQL Functions that can run just about any program or API, and instead of having to include a /COPY statement and a Binding Directory and install a separate *SRVPGM with a signature that matches, I’ve been creating SQL Functions to interface with our lovely IBM i APIs. API usage, even today, meets a lot of resistance. One challenge has been getting others to use those APIs or my own API wrappers. You may have it on your own system.īeing one of the handful of original advocates for the so called “Openness APIs” for IBM OS/400 (now IBM i), I quickly embraced the system APIs and have used them extensively throughout the decades. My customers use it all the time for monthly archiving of spooled files and redistribution of output. This command allowed you to selectively copy spooled files from one OUTQ to either another OUTQ or to the IFS as a PDF or text file. A long time ago I created a CL command named Copy from OUTQ (CPYOUTQ).