Guru: Copy OUTQ To PDF

February 3, 2020 Bob Cozzi

A long time ago I created a CL command named Copy from OUTQ (CPYOUTQ). 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. My customers use it all the time for monthly archiving of spooled files and redistribution of output. You may have it on your own system.

Being 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. One challenge has been getting others to use those APIs or my own API wrappers. API usage, even today, meets a lot of resistance. 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. The “SQL Services” effectively does the same thing. 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.

If I were to write this command today, I would use SQL to do it. 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. About the only thing I really miss when using SQL and SQL Services vs legacy CL commands is the command prompter.

The CPYOUTQ CL command uses the QGYOLSPL “list” API to generate a list of spooled file names found on the OUTQ. To do that today, I would use the SQL “Service” OUTPUT_QUEUE_ENTRIES() table function (UDTF) located in the QSYS2 library.

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
  FROM TABLE (
        QSYS2.OUTPUT_QUEUE_ENTRIES(OUTQ_LIB => 'QUSRSYS', 
                                   OUTQ_NAME => 'COZZI')
             ) outq;

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. When I run this SQL statement, I might see something like the following:

The OUTPUT_QUEUE_ENTRIES UDTF accepts the library and output queue names using either positional syntax or named parameter syntax (shown above). Turns out user-created functions also support this syntax, so naming your parameters clearly, as we do with CL Commands, is important.

Converting To PDF

Once we have a method of generating a list of spooled file names, we need a way to convert those spooled files to PDF. The easiest way to do that is with the CPYSPLF CL Command included with your system. First let’s stub-out a simple copy-to-PDF statement using one of the spooled files from the above list.

CPYSPLF FILE(RUNIQRY) 
         JOB(777933/COZZI/MACPR0)
         SPLNBR(77)
         TOFILE(*TOSTMF) WSCST(*PDF)
         STMFOPT(*REPLACE)
       TOSTMF('/home/cozzi/runiqry.pdf')

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.

CPYOUTQ2PDF Stored Procedure

The term stored procedure has rubbed me the wrong way since the first time I heard it. 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. So below I have the code for the CPYOUTQ2PDF SQL procedure. This procedure accepts up to five parameters.

The PDF_DIR parameter identifies the IFS folder/directory where the PDF files should be stored. Note that I also append the output queue name to this directory to force the PDF images into a subfolder within this directory. 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. Otherwise, just make sure you’ve already created the subdirectory or create it within the procedure using a second call to QCMDEXC with the MKDIR or MD command.

To call the procedure, start SQL or run an SQL script processor — STRSQL, RUN SQL Scripts in ACS or RUNiQRY — and run a variation of the following:

call cpyOutQ2PDF( OUTQ_NAME => 'COZZI', OUTQ_LIB => 'QUSRSYS');

This would copy all existing SPOOLED Files currently on the COZZI output queue to the IFS as a PDF file.

Specify the JOB_NAME or SPOOLED_FILE parameters to control which jobs and spooled files are copied otherwise all SPOOLED File Entries on the OUTQ are copied. SPOOLED_FILE accepts generic names, so SPOOLED_FILE => ‘ORD*’ is supported along with the SQL generic syntax of ‘ORD%’.

There is also a REPLACE (Replace the PDF file) parameter that helps you control whether or not to replace an existing PDF file if detected. The default for REPLACE is YES.

When I do a “release” build of one of my software products (not an interim build) I like to archive the compiler listings as a quasi-backup, just in case. To do that using this new CPYOUTQ2PDF Procedure, I might run the following:

call cpyOutQ2PDF( OUTQ_NAME=>'IQUERY', OUTQ_LIB=>'QUSRSYS', 
                  SPOOLED_FILE => 'IQRY*', REPLACE=>'yes');

This would copy all spooled files from the IQUERY output queue to the IFS as PDF files. Only spooled files whose names start with ‘IQRY’ are copied. (Others are skipped over.) Note that the procedure accepts asterisk or percent signs as the generic symbol, so both SQL and CL syntax are supported for generic spooled file names.

One enhancement you may want to make is to create the output PDF Directory if it doesn’t exist. For example, if I were to run the above call, then the default output directory would be ‘/home/cozzi/iquery’, but what if that directory doesn’t exist? Using the QSYS2.QCMDEXC procedure, I could include a statement like the following, in the CPYOUTQ2PDF procedure before the FOR LOOP is started:

call qsys2.qcmdexc('MD ' || pdf_dir);

A few notes. Stored procedures are called only from within SQL or embedded SQL, so you can’t call this routine directly from the Command Entry screen or CL. To run it in a CL environment, you’ll need to wrap it in a CL command that runs it as an SQL statement. Several options are available to do this, including RUNSQL, RUNSQLSTM, and RUNiQRY commands. To call this procedure using RUNSQL, from within a CL program or Command Entry:

RUNSQL SQL('CALL cpyoutq2pdf(OUTQ_NAME => ''IQUERY'', OUTQ_LIB => ''QUSRSYS'') ')

You could also embed this in RPG IV and simply prefix it with EXEC SQL as follows:

EXEC SQL CALL cpyoutq2pdf(OUTQ_NAME => :MYOUTQ, OUTQ_LIB => :MYOUTQLIB);

I’ve included the source code for CPYOUTQ2PDF at the bottom of this article. To compile it:

1. Copy it to a source file member
2. Compile that member using RUNSQLSTM

Tips

Make sure the code does not exceed position 79 of the source file member source line. RUNSQLSTM ignores text beyond position 79.

You may want to include OUTPUT(*PRINT) on the RUNSQLSTM command to print the compiler listing.

Qualify the CPYOUTQ2PDF name on the “CREATE or REPLACE” statement or change your current library to the library where you want the procedure to be created.

The procedure will be created as a *SRVPGM.

Let me know your thoughts on this procedure and if you’d like to see any others like it. You can reach me on Twitter at @SQLiQuery or by email at cozzi@rpgiv.com

CREATE or REPLACE PROCEDURE CPYOUTQ2PDF(
                              in OUTQ_LIB  varchar(10),
                              in OUTQ_NAME varchar(10),
                              in JOB_NAME  varchar(28) default '*ALL',
                              in PDF_DIR   varchar(640) default '/home',
                              in REPLACE varchar(10) default '*YES'
                            )
  LANGUAGE SQL
  SPECIFIC CPYOUTQ2PDF

A1: BEGIN  ATOMIC
   DECLARE STMFOPT varchar(10) not null default '*REPLACE';
   DECLARE PDF     varchar(2048) not null default '';
   DECLARE JOB varchar(28) not null default '*ALL';
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
     begin end;  -- Do nothing (i.e., "MONMSG CPF0000")


   if (CPYOUTQ2PDF.REPLACE is not null and 
       CPYOUTQ2PDF.REPLACE <> '') then
      set STMFOPT = ltrim(upper(CPYOUTQ2PDF.REPLACE),'* ');
      if (STMFOPT = 'YES' or STMFOPT = 'REPLACE') then
        set stmfOpt = '*REPLACE';
      else
        set stmfOpt = '*NONE';
      end if;
   end if;

   set job = upper(CPYOUTQ2PDF.job_Name);

forEach: FOR V1 AS C1 CURSOR FOR
           SELECT SPOOLED_FILE_NAME, JOB_NAME, FILE_NUMBER
             FROM TABLE (QSYS2.OUTPUT_QUEUE_ENTRIES(
                           OUTQ_LIB => OUTQ_LIB,
                           OUTQ_NAME => OUTQ_NAME)
                        ) outq
           WHERE JOB_NAME = CASE WHEN JOB = '*ALL'
                                 then outq.JOB_NAME
                                 else JOB
                                 end
      DO
         set PDF = rtrim(pdf_Dir,'/ ') || '/' || user || '/' || 
                   OUTQ_NAME || '/' ||    -- OUTPUT QUEUE subdir
                   FILE_NUMBER || '_' || SPOOLED_FILE_NAME || '.pdf';

         CALL QSYS2.QCMDEXC('CPYSPLF FILE(' || SPOOLED_FILE_NAME || ') ' ||
                      ' TOFILE(*TOSTMF) WSCST(*PDF) ' ||
                      ' STMFOPT(' || STMFOPT || ') ' ||
                      ' JOB(' || JOB_NAME || ') ' ||
                      ' SPLNBR(' || FILE_NUMBER || ') ' ||
                      ' TOSTMF(''' || PDF || ''')');
      end for forEach;

 END A1;