Last Updated on 11 September 2025 by Roberto De Pedrini
In my last article “Remove old spooled files to keep track of the number of jobs in the system” I published the source of a CLLE program that removes spooled files selected by JOBNAME, USERNAME, DAYS (Retention days), FILENAME, USRDTA and OUTQ in use on the systems I administer. Two readers pointed out to me that the same purpose can be achieved using the DELETE_OLD_SPOOLED_FILES IBM procedure but only after customizing it.
In this article we see how to modify this IBM SQL procedure contained in the SYSTOOLS library by adding the missing filters namely JOBNAME, FILENAME and USRDTA as suggested by IBM itself:
The SQL source of Db2 for i provided tools within SYSTOOLS can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.
First we need to retrieve the source of the DELETE_OLD_SPOOLED_FILES procedure, and we do this using another IBM procedure called GENERATE_SQL found in the QSYS2 library.
The GENERATE_SQL procedure generates the SQL data definition language statements required to recreate a database object. The results are returned in the specified database source file member, source stream file, or as a result set.
From Run SQL scripts of Access Client Solutions we generate the stream file DELETE_OLD_SPOOLED_FILES.sql in the dir /tmp in IFS with this SQL statement:
CALL
QSYS2.GENERATE_SQL(
DATABASE_OBJECT_NAME => 'DELETE_OLD_SPOOLED_FILES',
DATABASE_OBJECT_LIBRARY_NAME => 'SYSTOOLS',
DATABASE_OBJECT_TYPE => 'PROCEDURE',
DATABASE_SOURCE_FILE_NAME => '*STMF',
SOURCE_STREAM_FILE => '/tmp/DELETE_OLD_SPOOLED_FILES.sql',
SOURCE_STREAM_FILE_CCSID => 1208,
CREATE_OR_REPLACE_OPTION => 1,
REPLACE_OPTION => '1'
)
;Having extracted the source to a file with CCSID 1208 (or 1252) we can edit it from the PC with Run SQL Scripts from Access Client Solutions or, if the dir where the file is located has been shared with NetServer, with any Windows text editor.
We modify the source as highlighted below being careful to change the target library so that the procedure is created in a user library.
In brief, the changes to be made are as follows:
- Changing the target library from SYSTOOLS to a user library of your own;
- Definition of the new parameters P_JOBNAME, P_SPOOLED_FILE_NAME and P_USER_DATA;
- declaration of the new variable V_USER_DATA;
- modification of the WHERE statement by adding the constraint on the new parameters;
- Inclusion of the new variable V_USER_DATA in FETCH statements;
- Modification of commentary and parameter commentary.
If you prefer, you can also customize the name to make the difference with the official procedure further highlighted.
-- Generazione SQL
-- Versione: V7R4M0 190621
-- Generata su: 05/09/25 15:47:40
-- Database relazionale: TREBIRDB
-- Opzioni standard: Db2 for i
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM" ;
CREATE OR REPLACE PROCEDURE MYLIB.DELETE_OLD_SPOOLED_FILES (
IN DELETE_OLDER_THAN TIMESTAMP DEFAULT ( CURRENT_TIMESTAMP - 3 MONTHS ) ,
IN P_OUTPUT_QUEUE_LIBRARY_NAME VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN P_OUTPUT_QUEUE_NAME VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN P_USER_NAME VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN P_JOB_NAME VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN P_SPOOLED_FILE_NAME VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN P_USER_DATA VARCHAR(10) FOR SBCS DATA DEFAULT '*ALL' ,
IN PREVIEW VARCHAR(3) FOR SBCS DATA DEFAULT 'NO' )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MYLIB.DLTOLDSPL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = QSYS2 ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX ,
USRPRF = *USER
BEGIN
-- Example: PROCEDURE SYSTOOLS.DELETE_OLD_SPOOLED_FILES
--
-- Disclaimer:
-- This example is being provided by IBM to allow IBM i users to understand how to use
-- SQL to manage spooled files.
--
-- While efforts were made to verify the completeness and accuracy of this sample procedure,
-- this sample is provided 'as is' without any warranty whatsoever and to the maximum extent permitted,
-- IBM disclaims all implied warranties.
--
-- Parameters:
-- 1) DELETE_OLDER_THAN - A timestamp indicating to delete spooled files that were created BEFORE this point
-- 5) PREVIEW - When set to YES, the list of spooled files that would be deleted are returned, but not deleted.
-- If the value is not YES, all identified spooled files are deleted
--
DECLARE NOT_FOUND CONDITION FOR '02000' ;
DECLARE AT_END INTEGER DEFAULT 0 ;
DECLARE V_SPOOLED_FILE_NAME VARCHAR ( 10 ) ;
DECLARE V_JOB_NAME VARCHAR ( 28 ) ;
DECLARE V_FILE_NUMBER BIGINT ;
DECLARE V_USER_NAME VARCHAR ( 10 ) ;
DECLARE V_USER_DATA VARCHAR ( 10 ) ;
DECLARE V_CMDSTMT VARCHAR ( 200 ) ;
DECLARE V_CRT_DATETIME TIMESTAMP ;
DECLARE V_CRTDATE CHAR ( 15 ) ;
DECLARE DATE_FMT CHAR ( 4 ) ;
--
-- Find spooled files to delete
--
DECLARE V_SPOOLED_FILE_STMT_TEXT VARCHAR ( 3000 ) DEFAULT
'SELECT SPOOLED_FILE_NAME, JOB_NAME, FILE_NUMBER, USER_NAME, CREATE_TIMESTAMP, USER_DATA
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE CREATE_TIMESTAMP < ?' ;
DECLARE SPOOLED_FILE_CURSOR CURSOR WITH RETURN TO CALLER FOR V_SPOOLED_FILE_STMT ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET AT_END = 1 ;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET AT_END = 1 ;
IF ( PREVIEW = 'YES' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT =
'SELECT SPOOLED_FILE_NAME, JOB_NAME, FILE_NUMBER, USER_NAME,
SIZE, OUTPUT_QUEUE_NAME, OUTPUT_QUEUE_LIBRARY_NAME, CREATE_TIMESTAMP,
USER_DATA, STATUS, TOTAL_PAGES, COPIES, FORM_TYPE,
DEVICE_TYPE, OUTPUT_PRIORITY, SYSTEM
FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
WHERE CREATE_TIMESTAMP < ?' ;
END IF ;
IF ( P_OUTPUT_QUEUE_LIBRARY_NAME <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND OUTPUT_QUEUE_LIBRARY_NAME =''' CONCAT P_OUTPUT_QUEUE_LIBRARY_NAME CONCAT '''' ;
END IF ;
IF ( P_OUTPUT_QUEUE_NAME <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND OUTPUT_QUEUE_NAME =''' CONCAT P_OUTPUT_QUEUE_NAME CONCAT '''' ;
END IF ;
IF ( P_USER_NAME <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND USER_NAME =''' CONCAT P_USER_NAME CONCAT '''' ;
END IF ;
IF ( P_JOB_NAME <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND REGEXP_SUBSTR(JOB_NAME, ''[^/]+$'') =''' CONCAT P_JOB_NAME CONCAT '''' ;
END IF ;
IF ( P_SPOOLED_FILE_NAME <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND SPOOLED_FILE_NAME =''' CONCAT P_SPOOLED_FILE_NAME CONCAT '''' ;
END IF ;
IF ( P_USER_DATA <> '*ALL' ) THEN
SET V_SPOOLED_FILE_STMT_TEXT = V_SPOOLED_FILE_STMT_TEXT CONCAT
' AND USER_DATA =''' CONCAT P_USER_DATA CONCAT '''' ;
END IF ;
PREPARE V_SPOOLED_FILE_STMT FROM V_SPOOLED_FILE_STMT_TEXT ;
OPEN SPOOLED_FILE_CURSOR USING DELETE_OLDER_THAN ;
IF ( PREVIEW = 'YES' ) THEN
RETURN ;
END IF ;
SELECT DATE_FORMAT INTO DATE_FMT
FROM TABLE ( QSYS2 . ACTIVE_JOB_INFO ( JOB_NAME_FILTER => '*' , DETAILED_INFO => 'ALL' ) ) ;
FETCH FROM SPOOLED_FILE_CURSOR INTO V_SPOOLED_FILE_NAME , V_JOB_NAME , V_FILE_NUMBER , V_USER_NAME , V_CRT_DATETIME , V_USER_DATA ;
WHILE ( AT_END = 0 ) DO
--
-- Add 500 microseconds to keep the identical date and time value with spool file's.
--
SET V_CRT_DATETIME = V_CRT_DATETIME + 500 MICROSECONDS ;
--
-- Convert date and time ISO format to current job's date and time format
--
IF DATE_FMT = '*DMY' THEN
SET V_CRTDATE = VARCHAR_FORMAT ( V_CRT_DATETIME , 'DDMMYYYY HH24MISS' ) ;
ELSEIF DATE_FMT = '*JUL' THEN
SET V_CRTDATE = VARCHAR_FORMAT ( V_CRT_DATETIME , 'YYYYDDD HH24MISS' ) ;
ELSEIF DATE_FMT = '*MDY' THEN
SET V_CRTDATE = VARCHAR_FORMAT ( V_CRT_DATETIME , 'MMDDYYYY HH24MISS' ) ;
ELSEIF DATE_FMT = '*YMD' THEN
SET V_CRTDATE = VARCHAR_FORMAT ( V_CRT_DATETIME , 'YYYYMMDD HH24MISS' ) ;
END IF ;
SET V_CMDSTMT = 'QSYS/DLTSPLF FILE(' CONCAT V_SPOOLED_FILE_NAME CONCAT
') JOB(' CONCAT V_JOB_NAME CONCAT
') SPLNBR(' CONCAT V_FILE_NUMBER CONCAT
') CRTDATE(' CONCAT V_CRTDATE CONCAT
') JOBSYSNAME(*ANY) SELECT(' CONCAT V_USER_NAME CONCAT ')' ;
CALL QSYS2 . QCMDEXC ( V_CMDSTMT ) ;
FETCH FROM SPOOLED_FILE_CURSOR INTO V_SPOOLED_FILE_NAME , V_JOB_NAME , V_FILE_NUMBER , V_USER_NAME , V_CRT_DATETIME , V_USER_DATA ;
END WHILE ;
CLOSE SPOOLED_FILE_CURSOR ;
END ;
COMMENT ON SPECIFIC PROCEDURE MYLIB.DLTOLDSPL
IS 'USER VERSION CREATED FROM DB2 FOR IBM i SUPPLIED OBJECT VERSION 07400110003' ;
COMMENT ON PARAMETER SPECIFIC PROCEDURE MYLIB.DLTOLDSPL
( DELETE_OLDER_THAN IS 'timestamp - Default: CURRENT TIMESTAMP - 3 MONTHS' ,
P_OUTPUT_QUEUE_LIBRARY_NAME IS 'name, *ALL - Default: *ALL' ,
P_OUTPUT_QUEUE_NAME IS 'name, *ALL - Default: *ALL' ,
P_USER_NAME IS 'name, *ALL - Default: *ALL' ,
P_JOB_NAME IS 'name, *ALL - Default: *ALL' ,
P_SPOOLED_FILE_NAME IS 'name, *ALL - Default: *ALL' ,
P_USER_DATA IS 'name, *ALL - Default: *ALL' ,
PREVIEW IS 'NO, YES - Default: NO' ) ;
GRANT EXECUTE
ON SPECIFIC PROCEDURE MYLIB.DLTOLDSPL
TO PUBLIC ;
GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE MYLIB.DLTOLDSPL
TO QSYS WITH GRANT OPTION ;At this point we are ready to create our SQL procedure.
We can do this directly from QShell or from PASE with the command:
db2 -tvf /tmp/DELETE_OLD_SPOOLED_FILES.sqlOr, by calling the same command from the command line:
QSH CMD('db2 -tvf /tmp/DELETE_OLD_SPOOLED_FILES.sql')Where:
- -t = uses “;” as the terminator of the statement
- -v = verbose (prints statements before executing them)
- -f = input file
Or we can more conveniently open the file and run it directly from Run SQL Scripts from Access Client Solutions.
Once the SQL procedure is available we can try it. Below is an example of a call that does not clear anything but only shows the result set (parameter PREVIEW => ‘YES’):
CALL
MYLIB.DELETE_OLD_SPOOLED_FILES(
DELETE_OLDER_THAN => CURRENT DATE - 60 DAYS,
P_OUTPUT_QUEUE_LIBRARY_NAME => 'QUSRSYS', -- '*ALL'
P_OUTPUT_QUEUE_NAME => 'QEZJOBLOG', -- '*ALL'
P_USER_NAME => 'QUSER', -- '*ALL'
P_JOB_NAME => 'QRWTSRVR', -- '*ALL'
P_SPOOLED_FILE_NAME => 'QPJOBLOG', -- '*ALL'
P_USER_DATA => 'QRWTSRVR', -- '*ALL'
PREVIEW => 'YES')
;To remove the procedure, just execute this SQL statement:
DROP PROCEDURE
MYLIB.DELETE_OLD_SPOOLED_FILES
;At this point, the example CLLE program provided in the previous article becomes:
/* Before compiling: +
CREATE TABLE QTEMP.SQLOUT AS ( +
SELECT TOTAL_JOBS_IN_SYSTEM, MAXIMUM_JOBS_IN_SYSTEM +
FROM QSYS2.SYSTEM_STATUS_INFO_BASIC) +
WITH NO DATA */
PGM
DCLF FILE(SQLOUT) OPNID(SQLOUT) ALWVARLEN(*YES)
DCL VAR(&E) TYPE(*LGL) VALUE('0')
DCL VAR(&ERROR) TYPE(*LGL) VALUE('0')
/* RUNSQL */
DCL VAR(&SQL) TYPE(*CHAR) LEN(5000)
DCL VAR(&OUTQ) TYPE(*CHAR) LEN(21)
DCL VAR(&SQLLIB) TYPE(*CHAR) LEN(10) VALUE('QTEMP')
DCL VAR(&SQLFIL) TYPE(*CHAR) LEN(10) VALUE('SQLOUT')
DCL VAR(&SQLMBR) TYPE(*CHAR) LEN(10) VALUE('SQLOUT')
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
CALLSUBR SUBR(RTVTOTJOBS)
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''*ALL'', +
P_JOB_NAME => ''QP0ZSPWT'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''*ALL'', +
P_JOB_NAME => ''QP0ZSPWP'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''*ALL'', +
P_JOB_NAME => ''QJVACMDSRV'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''*ALL'', +
P_JOB_NAME => ''QZSHSH'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''QUSER'', +
P_JOB_NAME => ''QRWTSRVR'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RUNSQL SQL('CALL MYLIB.DELETE_OLD_SPOOLED_FILES(+
DELETE_OLDER_THAN => CURRENT DATE - 7 DAYS, +
P_USER_NAME => ''*ALL'', +
P_JOB_NAME => ''QPRTJOB'', +
P_USER_DATA => ''QZRCSRVS'')') +
COMMIT(*NONE)
MONMSG MSGID(SQL0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
CALLSUBR SUBR(RTVTOTJOBS)
GOTO CMDLBL(ENDCLP)
ERROR:
CHGVAR VAR(&ERROR) VALUE('1')
MONMSG MSGID(CPF0000)
GOTO CMDLBL(ENDCLP)
ENDCLP:
IF COND(&ERROR) THEN(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('Rilevato errore durante +
l''esecuzione della procedura, vedere +
messaggi precedenti') MSGTYPE(*ESCAPE)
MONMSG MSGID(CPF0000)
ENDDO
ELSE CMD(IF COND(&E) THEN(DO))
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('Spooled files rimossi +
parzialmente, vedere messaggi +
precedenti') MSGTYPE(*ESCAPE)
MONMSG MSGID(CPF0000)
ENDDO
RETURN
RTVTOTJOBS: SUBR SUBR(RTVTOTJOBS)
DLTF FILE(&SQLLIB/&SQLFIL)
MONMSG MSGID(CPF2105)
CHGVAR VAR(&SQL) VALUE('SELECT TOTAL_JOBS_IN_SYSTEM, +
MAXIMUM_JOBS_IN_SYSTEM FROM +
QSYS2.SYSTEM_STATUS_INFO_BASIC')
CHGVAR VAR(&SQL) VALUE('CREATE TABLE' *BCAT &SQLLIB +
*TCAT '/' *CAT &SQLFIL *BCAT 'AS (' *CAT &SQL +
*TCAT ') WITH DATA')
RUNSQL SQL(&SQL) COMMIT(*NONE) NAMING(*SYS)
MONMSG MSGID(SQL0000) EXEC(DO)
ENDDO
OVRDBF FILE(SQLOUT) TOFILE(&SQLLIB/&SQLFIL) +
MBR(&SQLMBR) LVLCHK(*NO) OVRSCOPE(*CALLLVL)
RCVF OPNID(SQLOUT)
CLOSE OPNID(SQLOUT) /* RVCF */
DLTOVR FILE(SQLOUT) LVL(*) /* OVRDBF */
SNDPGMMSG MSGID(CPI8859) MSGF(QCPFMSG) MSGDTA('Jobs in +
system:' *BCAT %CHAR(&SQLOUT_TOTAL_JOBS) +
*BCAT '/' *BCAT %CHAR(&SQLOUT_MAX_JOBS)) +
TOPGMQ(*PRV) TOMSGQ(*TOPGMQ) MSGTYPE(*INFO)
DLTF FILE(&SQLLIB/&SQLFIL)
MONMSG MSGID(CPF0000)
ENDSUBR RTNVAL(0)
ENDPGM:
ENDPGMReferences
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
