Move Spooled Files with SQL

This stored procedure has limited application, but it was fun.? We use FormSprint to process several types of spooled files.? For a rewrite of a process, we split the current too-many-pages spooled file into a separate report per key, in this case owner.? The current massive spooled file does have some use and could not be restructured to allow using the built-in FormSprint tools to split the file. The new process often results in with more than 10K spooled files for a single run.? Since we still need the reformatting tools from FormSprint to give the PDF the desired appearance, we still need to deal with each spooled file.? Once this is in production, processing time is not an issue. It can take all day in the background. While testing, though, I want to improve my throughput. I needed an easy way to move spooled files from the normal virtual out queue to another to speed up the process. Some of Scott Forstie’s example showed me the basics.

After running the basic SQL a few times, I decided to wrap it up as a stored procedure. After I ran that a few times, I decided to add a parameter for how many files to move.? The default is set to 200, so you can skip that value on the call.?

From the source to the target using the default 200 files.

CALL?MOVE_SPOOLED_FILES?(
  IN_SOURCE_QUEUE?=>?'SOURCEV',??
  IN_TARGET_QUEUE?=>?'TARGETV'???);        

From the source to the target using the optional parameter.

 CALL?MOVE_SPOOLED_FILES?( ??   
  IN_SOURCE_QUEUE?=>?'SOURCEV',???????????     
  IN_TARGET_QUEUE?=>?'TARGETV',?   
  IN_HOW_MANY?=> 300);        

?Stored Procedure Source

?CREATE OR REPLACE PROCEDURE QGPL.MOVE_SPOOLED_FILES (
                IN IN_Source_Queue CHAR(10) DEFAULT ' ',
                IN IN_Target_Queue CHAR(10) DEFAULT ' ',
                IN IN_How_Many INTEGER DEFAULT 200
            )
        DYNAMIC RESULT SETS 1
        LANGUAGE SQL
        SPECIFIC QGPL.MOVE_SPOOLED_FILES
        NOT DETERMINISTIC
        MODIFIES SQL DATA
        CALLED ON NULL INPUT
        SET OPTION ALWBLK = *ALLREAD,
                   ALWCPYDTA = *OPTIMIZE,
                   COMMIT = *NONE,
                   DBGVIEW = *SOURCE,
                   DECRESULT = (31,31,00),
                   DLYPRP = *NO,
                   DYNDFTCOL = *NO,
                   DYNUSRPRF = *USER,
                   SRTSEQ = *HEX
BEGIN
    DECLARE thisSource CHAR(10);
    DECLARE thisTarget CHAR(20);
    DECLARE howManyFiles INTEGER; 
    DECLARE LineItems CURSOR WITH RETURN FOR
      WITH MAX_NUMBER AS (
        SELECT MAX(SPOOLED_FILE_NUMBER) maxNumber
            FROM TABLE (QSYS2.SPOOLED_FILE_INFO(OUTPUT_QUEUE => 
                        'QUSRSYS/' CONCAT thisSource CONCAT ''))
            )
      SELECT SPOOLED_FILE_NAME, QUALIFIED_JOB_NAME, SPOOLED_FILE_NUMBER,
             QSYS2.QCMDEXC('CHGSPLFA FILE(' ||STRIP(SPOOLED_FILE_NAME)  
             ||') JOB(' || STRIP(QUALIFIED_JOB_NAME) || ') SPLNBR(' 
             || SPOOLED_FILE_NUMBER || ') OUTQ(QUSRSYS/' 
             CONCAT thisTarget CONCAT ')')
        FROM TABLE (QSYS2.SPOOLED_FILE_INFO(OUTPUT_QUEUE => 
                   'QUSRSYS/' CONCAT thisSource CONCAT '')),                  
             MAX_NUMBER 
       WHERE SPOOLED_FILE_NUMBER BETWEEN maxNumber - howManyFiles 
            AND maxNumber;
                
    SET thisSource = IN_Source_Queue;
    SET thisTarget = IN_Target_Queue;
    SET howManyFiles = IN_How_Many; 
    
    OPEN LineItems;
END;        

?

?