Spooled File Automation with CoolSpools Spool Admin

Extend CoolSpools Automation: Extend CoolSpools Automation With Your Own CL Programs: CoolSpools Monitors, Rules and Scripts allow you to automatically perform a variety of actions upon your spooled file reports, such as CoolSpools conversions to Excel, PDF, XML or text files. This process is described in another article on the steps needed to achieve spooled file automation.

Extend CoolSpools Automation: Integrating Custom CL Programs

Did you know that you can also use CoolSpools automation with the *CMD option to call a custom CL program containing your own site-specific functionality?

CoolSpools substitution variables can be used to pass your CL program all of the attributes needed to identify the spooled file being processed. The full list of substitution variables is provided at the end of this page, but the minimum required are the spooled file name &F, the job number &N, the job user &U, the job name &J and the spooled file number &S, as shown in the example rule below:

CRTSPLRUL RULE(REPORTS)
OPTION(*CMD)
CMD(CALL PGM(MYPGM) PARM(‘&F’ ‘&N’ ‘&U’ ‘&J’ ‘&S’))

Your CL program (named MYPGM in this example) needs to contain a matching set of parameters to receive these spooled file attributes, as in the example below:

0001.00              PGM        PARM(&F &N &U &J &S)
0002.00
0003.00              DCL        VAR(&F) TYPE(*CHAR) LEN(10) /* Spoolfile Name */
0004.00              DCL        VAR(&N) TYPE(*CHAR) LEN(6)  /* Job Number */
0005.00              DCL        VAR(&U) TYPE(*CHAR) LEN(10) /* Job User */
0006.00              DCL        VAR(&J) TYPE(*CHAR) LEN(10) /* Job Name */
0007.00              DCL        VAR(&S) TYPE(*CHAR) LEN(6)  /* Spoolfile Number */

Within your CL program you can use the passed parameters to perform standard CoolSpools conversion commands, such as the command below, which will convert the spooled file to a text file and send it to a remote server using FTP.

0009.00           /* Convert to text and FTP to file server */
0010.00              CVTSPLTXT  FROMFILE(&F) TOSTMF(*FTP) JOB(&N/&U/&J) +
0011.00                           SPLNBR(&S) FTP(RMTSERVER ‘filename.txt’ *FTP +
0012.00                           *NONE *DFT RMTUSER RMTPWD *NO *NO)
0013.00

Adding Site-Specific Functionality

Where calling a custom CL program really adds value is in the ability to invoke site-specific functionality beyond CoolSpools conversions.

In the example below, the CL code checks the name of the spooled file being processed to identify whether it is an invoice. If the spooled file is an invoice then the invoice number is extracted from within the spooled file content using CoolSpools command GETSPLDTA, and an application program is called to update the status of the invoice record to “sent” within the business application.

0014.00           /* Is this an INVOICE spooled file? */
0015.00              IF         COND(&F *EQ ‘INVOICE’) THEN(DO)
0016.00
0017.00           /* Get the invoice number from line 5 column 50 on first page */
0018.00              GETSPLDTA  FROMFILE(&F) JOB(&N/&U/&J) SPLNBR(&S) +
0019.00                           DATA(&INVNO) GETSPLPOS(*FIRST 5 50 9)
0020.00
0021.00           /* Update invoice status to SENT */
0022.00              CALL       PGM(UPDINVSTS) PARM(&INVNO ‘S’)
0023.00
0024.00              ENDDO

At the end of processing you might want to use the IBM i system command CHGSPLFA to mark the spooled file as procesed, for example by moving it to a different output queue.

0026.00           /* Move to PROCESSED output queue */
0027.00              CHGSPLFA   FILE(&F) JOB(&N/&U/&J) SPLNBR(&S) +
0028.00                           OUTQ(PROCESSED)

RUNSPLFCMD

As well as running your CL program automatically when a new spooled file arrives on a monitored output queue, you can also use CoolSpools command RUNSPLFCMD to process spooled files on demand. This is useful if you want to perform spooled file conversions at a set time of the day, rather than as each spooled file is created. It can also be useful to process a backlog of reports that already existed prior to the introduction of automation using monitors and rules.

In the example below, RUNSPLFCMD will call the program MYPGM to process each spooled file that exists in the output queue NEWINV.

RUNSPLFCMD USER(*ALL)
OUTQ(NEWINV)
OPTION(*CMD)
CMD(CALL PGM(MYPGM) PARM(‘&F’ ‘&N’ ‘&U’ ‘&J’ ‘&S’))

Conditioning Spooled File Processing Based on Report Content

The selection parameters of Spooled Admin Rules and the RUNSPLFCMD command allow you to filter the spooled files to be processed based upon such parameters as the spooled file name, user data, output queue, job name and user, but what if you need to condition which spooled files to process, and how to process them, based upon a data value embedded within the content of the spooled file?

An example might be a IBM i site where a variety of different reports are generated using the same spooled file name (e.g. REPORT, QPRINT, QSYSPRT, QPQUPRFIL) and can only be identified by a Report ID printed at the top of each page. Below is an example rule that will process all spooled files on output queue QPRINT named REPORT, calling CL program REPORT in order to decide how they are to be processed:

CRTSPLRUL RULE(REPORT)
SPLFNAME(REPORT)
OUTQ(QPRINT)
OPTION(*CMD)
CMD(CALL PGM(REPORT) PARM(‘&F’ ‘&N’ ‘&U’ ‘&J’ ‘&S’))

Below is the CL program REPORT. This program uses the CoolSpools GETSPLDTA command to retrieve the Report ID from line 1, column 2 of the report output. Different CoolSpools conversion commands are then run depending upon the Report ID value:

  • Stock Report STK005 is emailed to the Warehouse team as a PDF
  • Stock Replenishment Report STK020 is emailed to the Purchasing team in Excel
  • Sales Report SLS010 is split into multiple PDF documents by Sales Team and sent to the appropriate email address
  • Any other reports that do not match the listed Report ID values are converted to text and sent to an FTP site

At the end of processing the spooled file is moved to output queue SUCCESS (or to output queue FAILED if any errors were trapped during processing).

0001.00              PGM        PARM(&F &N &U &J &S)
0002.00
0003.00              DCL        VAR(&F) TYPE(*CHAR) LEN(10) /* Spoolfile Name */
0004.00              DCL        VAR(&N) TYPE(*CHAR) LEN(6)  /* Job Number */
0005.00              DCL        VAR(&U) TYPE(*CHAR) LEN(10) /* Job User */
0006.00              DCL        VAR(&J) TYPE(*CHAR) LEN(10) /* Job Name */
0007.00              DCL        VAR(&S) TYPE(*CHAR) LEN(6)  /* Spoolfile Number */
0008.00
0009.00              DCL        VAR(&REPORTID) TYPE(*CHAR) LEN(30)
0010.00              DCL        VAR(&DFTFNAM) TYPE(*CHAR) LEN(50)
0011.00
0012.00           /* Trap all errors and treat as processing failure */
0013.00              MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(FAILED))
0014.00
0015.00           /* This program is only for spoolfiles named REPORT */
0016.00              IF         COND(&F *NE ‘REPORT’) THEN(GOTO CMDLBL(END))
0017.00
0018.00           /* Get the Report ID */
0019.00              GETSPLDTA  FROMFILE(&F) JOB(&N/&U/&J) SPLNBR(&S) +
0020.00                           DATA(&REPORTID) GETSPLPOS(*FIRST 1 2 10)
0021.00
0022.00           /* STK005 Stock Report – Send as PDF to WAREHOUSE team */
0023.00              IF (&REPORTID *EQ ‘STK005’) THEN(DO)
0024.00              CVTSPLPDF  FROMFILE(&F) TOSTMF(‘Stock Report.pdf’) +
0025.00                           JOB(&N/&U/&J) SPLNBR(&S) +
0026.00                           STMFOPT(*REPLACE) EMAIL(*YES) +
0027.00                           EMAILOPT(*NO ‘Stock Report’) +
0028.00                           EMAILTO((WAREHOUSE *ADRL *ADRL)) +
0029.00                           EMAILMSG(‘Please see attached the Stock +
0030.00                           Report.’)
0031.00              GOTO       CMDLBL(SUCCESS)
0032.00              ENDDO
0033.00
0034.00           /* STK020 Stock Replenishment Report – Send as Excel to Purchasing */
0035.00              IF (&REPORTID *EQ ‘STK020’) THEN(DO)
0036.00              CVTSPLXLS  FROMFILE(&F) +
0037.00                           TOSTMF(‘Stock Replenishment.xlsx’) +
0038.00                           JOB(&N/&U/&J) SPLNBR(&S) EMAIL(*YES) +
0039.00                           EMAILOPT(*NO ‘Stock Replenishment Report’) +
0040.00                           EMAILTO((PURCHASING *ADRL *ADRL)) +
0041.00                           EMAILMSG(‘Please see attached the Stock +
0042.00                           Replenishment Report.’) EXCEL(*XLSX)
0043.00              GOTO       CMDLBL(SUCCESS)
0044.00              ENDDO
0045.00
0046.00           /* SLS010 Sales Report – Split & Distribute as PDF to Sales Teams */
0047.00              IF (&REPORTID *EQ ‘SLS010’) THEN(DO)
0048.00              CVTSPLPDF  FROMFILE(&F) TOSTMF(‘Sales Report for +
0049.00                           <:TEAM:>.xlsx’) JOB(&N/&U/&J) SPLNBR(&S) +
0050.00                           EMAIL(*YES) EMAILOPT(*NO ‘Sales Report +
0051.00                           for <:TEAM:>’) EMAILTO((*EMAILFILE)) +
0052.00                           EMAILFILE(SALESTEAMS EMAIL NAME *NONE +
0053.00                           ((TEAMID *EQ ‘<:TEAM:>’))) +
0054.00                           EMAILMSG(‘Please see attached the Sales +
0055.00                           Report for your Team.’) SPLIT(*POS) +
0056.00                           SPLITPOS((5 15 10 *ROWCOL *NE *PRV)) +
0057.00                           EXITPGM(*VAR) EXITPGMPRM(*POS) +
0058.00                           EXITPGMPOS((*ALL 5 15 10 TEAM))
0059.00              GOTO       CMDLBL(SUCCESS)
0060.00              ENDDO
0061.00
0062.00           /* All other reports are converted to text and sent to FTP site */
0063.00              CHGVAR     VAR(&DFTFNAM) VALUE(&F *TCAT ‘_’ *TCAT +
0064.00                           &REPORTID *TCAT ‘_’ *TCAT &J *TCAT ‘_’ +
0065.00                           *TCAT &U *TCAT ‘_’ *TCAT &N *TCAT ‘_’ +
0066.00                           *TCAT &S *TCAT ‘.txt’)
0067.00              CVTSPLTXT  FROMFILE(&F) TOSTMF(*FTP) JOB(&N/&U/&J) +
0068.00                           SPLNBR(&S) FTP(RMTSERVER &DFTFNAM *FTP +
0069.00                           *NONE *DFT RMTUSER RMTPWD *NO *NO)
0070.00              GOTO       CMDLBL(SUCCESS)
0071.00
0072.00           /* Move spooled file to FAILED output queue in event of error */
0073.00  FAILED:     CHGSPLFA   FILE(&F) JOB(&N/&U/&J) SPLNBR(&S) OUTQ(FAILED)
0074.00              MONMSG     MSGID(CPF0000)
0075.00              GOTO       CMDLBL(END)
0076.00
0077.00  SUCCESS:    CHGSPLFA   FILE(&F) JOB(&N/&U/&J) SPLNBR(&S) OUTQ(SUCCESS)
0078.00              GOTO       CMDLBL(END)
0079.00
0080.00  END:        ENDPGM

Spool Admin Substitution Variables

If your logic requires the spooled file’s user data value, then you can just add substitution variable &A to your *CMD command string and to the list of CL parameters, and the user data value is now available to your CL program. The full list of substitution variables is as follows:

  • &A The user data attribute of the spooled file.
  • &B Number of copies attribute of the spooled file.
  • &C Product library of the command. For example COOLSPV7R1.
  • &D The date the spooled file was created. The format of this date is determined by the date format attribute of the job running the command.
  • &E The email address of the user that created the spooled file, as derived from the system distribution directory.
  • &F The name of the spooled file.
  • &G Form type of the spooled file.
  • &I The current user profile.
  • &J The name of the job that created the spooled file.
  • &K The message key of the current spooled file message (if the spooled file is waiting for a message to be replied to).
  • &M The name of the user who created the spooled file, as derived from the system distribution directory.
  • &N The job number of the job that created the spooled file.
  • &O The user profile which owns the spooled file.
  • &P The printer device to which the spooled file is allocated.
  • &Q The output queue where the spooled file is currently located.
  • &R The output queue library.
  • &S The spooled file number.
  • &T The time the spooled file was created in *HMS format (HHMMSS).
  • &U The user of the job that created the spooled file.
  • &V The message queue associated with the writer to which the spooled file is currently allocated.
  • &W The library of the message queue associated with the writer to which the spooled file is currently allocated.
  • &X The file extension appropriate to the converted file format, e.g. pdf, xls, txt, etc.
  • &Y Date spooled file was opened in YYYYMMDD format.
  • &Z User-defined data of the spooled file.

Find out more about Extend CoolSpools Automation by contacting us today

*CMD attributes automate automation Command custom customise customize monitor output queue rule script site-specific Spool Admin Spooled file spooled files spoolfile spoolfiles substitution variable user data user exit