The system value QMAXJOB (valid range: 32,000 – 970,000; default = 163,520) defines the maximum number of jobs that can exist on an IBM i system. This value takes into account active jobs, queued jobs, and jobs that are ended but have spooled files attached (*KEEP is the default value for both the system value QSPLFACN and the job attribute SPLFACN).
Reaching this limit is a critical condition that can impair the system’s operation by making it impossible to start new jobs, even to the point of making it inaccessible except through the console (the system reserves a job table entry for the console to grant access).
The greatest risk is that, in the absence of proactive monitoring and effective management of jobs that are no longer active, QMAXJOB limit exhaustion will suddenly occur, leaving few options for intervention other than an IPL (Initial Program Load).
When the system approaches the maximum number of jobs, the message CPI1468, System job tables nearing capacity, is sent to the QSYSOPR message queue. By default, this message is first sent when the system job tables reach 90% of their capacity according to the QMAXJOB setting. The message is sent again whenever the work tables are extended to add more work structures.
It is therefore essential to constantly monitor the number of active jobs (displayable, for example, through the WRKSYSSTS and DSPSYSSTS commands or through the QSYS2.SYSTEM_STATUS_INFO_BASIC system view or controllable with the QSYS2.SYSLIMITS system service or by monitoring the QSYSOPR message queue) and adopt policies for automatically handling ended jobs with spooled files attached, as well as periodically evaluate the adequacy of the value assigned to QMAXJOB with respect to the growth and complexity of the IBM i system.
The RMVOLDSPLF program, subject of this article, can help limit the growth of jobs in the system by providing help in managing ended jobs with attached spooled files (which occupy entries in the job table and participate in increasing the number of jobs in the system) by removing dated or useless spooled files to attempt to free up entries in the job table (displayable with the DSPJOBTBL command).
Note: To remove a ended job with attached spooled files from the system job table, you must disconnect (CHGJOB JOB(…/…/…) SPLFACN(*DETACH)) or delete (DLTSPLF FILE(…) JOB(…/…/…)) all spooled files attached to it.
This is a CLLE program that makes use of the QSYS2.SPOOLED_FILE_INFO table function to select the spooled files to be removed according to the values passed in the command:
Remove old spooled files (SQL) (RMVOLDSPLF)
Immettere le scelte e premere Invio.
Job name . . . . . . . . . . . . Nome, *ALL
User name . . . . . . . . . . . Nome, *ALL, *CURRENT
File name . . . . . . . . . . . *ALL Nome, *ALL
User data . . . . . . . . . . . *ALL Valore carattere, *ALL
Output queue . . . . . . . . . . *ALL Nome, *ALL
Library . . . . . . . . . . . Nome, *CURLIB, *LIBL
Retention days . . . . . . . . . Numero Command source code:
RMVOLDSPLF: CMD PROMPT('Remove old spooled files (SQL)')
PARM KWD(JOBNAME) TYPE(*NAME) LEN(10) SPCVAL((*ALL)) +
MIN(1) ALWVAR(*YES) EXPR(*YES) PROMPT('Job name' 1)
PARM KWD(USERNAME) TYPE(*NAME) LEN(10) SPCVAL((*ALL) +
(*CURRENT)) MIN(1) ALWVAR(*YES) EXPR(*YES) +
PROMPT('User name' 2)
PARM KWD(DAYS) TYPE(*UINT2) MIN(1) ALWVAR(*YES) +
EXPR(*YES) PROMPT('Retention days' 6)
PARM KWD(FILENAME) TYPE(*NAME) LEN(10) DFT(*ALL) +
SPCVAL((*ALL)) ALWVAR(*YES) EXPR(*YES) +
PROMPT('File name' 3)
PARM KWD(USRDTA) TYPE(*CHAR) LEN(10) DFT(*ALL) +
SPCVAL((*ALL)) ALWVAR(*YES) EXPR(*YES) +
CASE(*MIXED) PROMPT('User data' 4)
PARM KWD(OUTQ) TYPE(QOUTQ) PROMPT('Output queue' 5)
QOUTQ: QUAL TYPE(*NAME) LEN(10) EXPR(*YES) DFT(*ALL) SPCVAL(*ALL)
QUAL TYPE(*NAME) LEN(10) SPCVAL((*CURLIB *CURLIB) +
(*LIBL *LIBL)) EXPR(*YES) PROMPT('Library')
PARM KWD(RMV) TYPE(*CHAR) LEN(4) RSTD(*YES) DFT(*YES) +
SPCVAL((*YES) (*NO)) EXPR(*YES) PMTCTL(*PMTRQS) +
PROMPT('Really remove?' 7)
PARM KWD(VERBOSE) TYPE(*CHAR) LEN(4) RSTD(*YES) +
DFT(*NO) SPCVAL((*YES) (*NO)) EXPR(*YES) +
PMTCTL(*PMTRQS) PROMPT('Verbose' 8)Program source code:
/* Before compiling: +
CREATE TABLE QTEMP.SQLOUT AS ( +
SELECT SFI.CREATION_TIMESTAMP CRTTIMSTM, SFI.SPOOLED_FILE_NAME SPLFNAME, +
SFI.SPOOLED_FILE_NUMBER SPLFNBR, SFI.QUALIFIED_JOB_NAME JOB +
FROM TABLE(QSYS2.SPOOLED_FILE_INFO()) SFI LIMIT 1 ) +
WITH NO DATA */
/* RMVOLDSPLF JOBNAME(QP0ZSPWP) USERNAME(*ALL) DAYS(60) +
[USRDTA(*ALL) OUTQ(*ALL)] */
PGM PARM(&PJOBNAM &PUSRNAM &PDAYS &PFILNAM &PUSRDTA +
&PQOUTQ &PRMV &PVERBOSE)
/* PARM: Start ****************************************************************/
DCL VAR(&PJOBNAM) TYPE(*CHAR) LEN(10)
DCL VAR(&PUSRNAM) TYPE(*CHAR) LEN(10)
DCL VAR(&PDAYS) TYPE(*UINT) LEN(2)
DCL VAR(&PFILNAM) TYPE(*CHAR) LEN(10)
DCL VAR(&PUSRDTA) TYPE(*CHAR) LEN(10)
DCL VAR(&PQOUTQ) TYPE(*CHAR) LEN(20)
DCL VAR(&OUTQNAM) TYPE(*CHAR) STG(*DEFINED) LEN(10) +
DEFVAR(&PQOUTQ 1)
DCL VAR(&OUTQLIB) TYPE(*CHAR) STG(*DEFINED) LEN(10) +
DEFVAR(&PQOUTQ 11)
DCL VAR(&PRMV) TYPE(*CHAR) LEN(4)
DCL VAR(&PVERBOSE) TYPE(*CHAR) LEN(4)
/* PARM: End ******************************************************************/
/* SUBR(RTVENDDAT): Start *****************************************************/
DCL VAR(&RTVENDDAT) TYPE(*INT) LEN(4)
DCL VAR(&CURDATE) TYPE(*CHAR) LEN(20)
DCL VAR(&CURYYMD) TYPE(*CHAR) STG(*DEFINED) LEN(8) +
DEFVAR(&CURDATE 1)
DCL VAR(&CURHHMISS) TYPE(*CHAR) STG(*DEFINED) LEN(6) +
DEFVAR(&CURDATE 9)
DCL VAR(&CURHH) TYPE(*CHAR) STG(*DEFINED) LEN(2) +
DEFVAR(&CURHHMISS 1)
DCL VAR(&CURMI) TYPE(*CHAR) STG(*DEFINED) LEN(2) +
DEFVAR(&CURHHMISS 3)
DCL VAR(&CURSS) TYPE(*CHAR) STG(*DEFINED) LEN(2) +
DEFVAR(&CURHHMISS 5)
DCL VAR(&CURMICSEC) TYPE(*CHAR) STG(*DEFINED) LEN(6) +
DEFVAR(&CURDATE 15)
DCL VAR(&LILDATE) TYPE(*INT)
DCL VAR(&YYYY_MM_DD) TYPE(*CHAR) LEN(10)
DCL VAR(&HH_MI_SS) TYPE(*CHAR) LEN(8)
DCL VAR(&CURTIMSTMP) TYPE(*CHAR) LEN(26)
DCL VAR(&TIMSEP) TYPE(*CHAR) LEN(1) VALUE(':')
/* SUBR(RTVENDDAT): End *******************************************************/
/* SUBR(RUNSQLSEL): Start *****************************************************/
DCL VAR(&RUNSQLSEL) TYPE(*INT) LEN(4)
/* 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')
/* SUBR(RUNSQLSEL): End *******************************************************/
/* SUBR(RMVSPLFIL): Start *****************************************************/
DCL VAR(&RMVSPLFIL) TYPE(*INT) LEN(4)
DCLF FILE(SQLOUT) OPNID(SQLOUT) ALWVARLEN(*YES)
DCL VAR(&SQLROWS) TYPE(*DEC) LEN(10 0)
DCL VAR(&RCVSQLOUT) TYPE(*LGL) VALUE('1')
DCL VAR(&RCDNUM) TYPE(*UINT) LEN(4)
DCL VAR(&CMD_OK) TYPE(*UINT) LEN(4)
DCL VAR(&CMD_KO) TYPE(*UINT) LEN(4)
/* SUBR(RMVSPLFIL): End *******************************************************/
/* stat64 */
DCL VAR(&STATRTNVAL) TYPE(*INT) LEN(4)
DCL VAR(&STATPATH) TYPE(*CHAR) LEN(256)
DCL VAR(&STATBUFFER) TYPE(*CHAR) LEN(4096)
DCL VAR(&STATOBJTYP) TYPE(*CHAR) STG(*DEFINED) LEN(10) +
DEFVAR(&STATBUFFER 61)
/* DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00') */
/* strlen */
DCL VAR(&STRTMP) TYPE(*CHAR) LEN(32767)
DCL VAR(&LENINT) TYPE(*UINT) LEN(4)
/* DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00') */
/* QCMDEXC */
DCL VAR(&CMD) TYPE(*CHAR) LEN(5000)
DCL VAR(&CMDLEN) TYPE(*DEC) LEN(15 5) VALUE(5000)
/* RUNSQL */
/* DCL VAR(&SQL) TYPE(*CHAR) LEN(5000) */
/* API Error */
DCL VAR(&APIERROR) TYPE(*CHAR) LEN(1040)
DCL VAR(&AEBYTPRO) TYPE(*INT) STG(*DEFINED) LEN(4) +
DEFVAR(&APIERROR 1)
DCL VAR(&AEBYTAVL) TYPE(*INT) STG(*DEFINED) LEN(4) +
DEFVAR(&APIERROR 5)
DCL VAR(&AEEXCPID) TYPE(*CHAR) STG(*DEFINED) LEN(7) +
DEFVAR(&APIERROR 9)
DCL VAR(&AEEXCPDTA) TYPE(*CHAR) STG(*DEFINED) +
LEN(1024) DEFVAR(&APIERROR 17)
/* _MATPGMNM */
DCL VAR(&DATA) TYPE(*CHAR) LEN(80)
DCL VAR(&PGMNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&PGMLIB) TYPE(*CHAR) LEN(10)
/* RTVNETA */
DCL VAR(&SYSNAME) TYPE(*CHAR) LEN(8)
DCL VAR(&CUST3) TYPE(*CHAR) STG(*DEFINED) LEN(3) +
DEFVAR(&SYSNAME 4)
/* RTVJOBA */
DCL VAR(&JOBNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBUSER) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBNBR) TYPE(*CHAR) LEN(6)
DCL VAR(&LOGCLPGM) TYPE(*CHAR) LEN(10) VALUE(' ')
/* RCVMSG & SNDPGMMSG */
DCL VAR(&PRCNAME) TYPE(*CHAR) LEN(256) /* CALLPRC */
DCL VAR(&CMDNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&ERROR) TYPE(*LGL) VALUE('0')
DCL VAR(&PGMERROR) TYPE(*CHAR) VALUE('-')
DCL VAR(&DUMP) TYPE(*LGL) VALUE('0')
DCL VAR(&SUBRNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&ERR_PGM) TYPE(*CHAR) LEN(10)
DCL VAR(&ERR_FUN) TYPE(*CHAR) LEN(10)
DCL VAR(&ERR_TEXT) TYPE(*CHAR) LEN(100) /* For handled +
errors only */
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(512)
DCL VAR(&MSGDTALEN) TYPE(*DEC) LEN(5 0)
DCL VAR(&MSG) TYPE(*CHAR) LEN(1024)
DCL VAR(&MSGLEN) TYPE(*DEC) LEN(5 0)
DCL VAR(&SECLVL) TYPE(*CHAR) LEN(1024)
DCL VAR(&SECLVLLEN) TYPE(*DEC) LEN(5 0)
DCL VAR(&MSGF) TYPE(*CHAR) LEN(10)
DCL VAR(&MSGFLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&MSGKEY) TYPE(*CHAR) LEN(4)
DCL VAR(&MSGKEYRQS) TYPE(*CHAR) LEN(4)
DCL VAR(&MSGTYPE) TYPE(*CHAR) LEN(7)
DCL VAR(&RTNTYPE) TYPE(*CHAR) LEN(2)
DCL VAR(&SENDER) TYPE(*CHAR) LEN(80)
DCL VAR(&SD_PGMSDR) TYPE(*CHAR) STG(*DEFINED) LEN(10) +
DEFVAR(&SENDER 27)
/* DCL VAR(&TOMSGQ) TYPE(*CHAR) LEN(10) */
/* DCL VAR(&TOMSGQLIB) TYPE(*CHAR) LEN(10) */
/* Constants */
DCL VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')
DCL VAR("E) TYPE(*CHAR) LEN(1) VALUE(X'7D')
/* Global monitor for error messages not handled */
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
/* Retrieving program name & library */
CHGVAR VAR(%BIN(&DATA 1 4)) VALUE(80)
CHGVAR VAR(%BIN(&DATA 5 4)) VALUE(80)
CHGVAR VAR(%BIN(&DATA 9 4)) VALUE(0)
CHGVAR VAR(%BIN(&DATA 13 4)) VALUE(0)
CALLPRC PRC('_MATPGMNM') PARM((&DATA))
CHGVAR VAR(&PGMNAME) VALUE(%SST(&DATA 51 10))
CHGVAR VAR(&PGMLIB) VALUE(%SST(&DATA 19 10))
/* Retrieving job attributes */
RTVJOBA JOB(&JOBNAME) USER(&JOBUSER) NBR(&JOBNBR) +
LOGCLPGM(&LOGCLPGM)
/* Checking parameters */
IF COND(&OUTQNAM *NE '*ALL') THEN(DO)
IF COND(&OUTQLIB *EQ ' ') THEN(DO)
CHGVAR VAR(&MSGDTA) VALUE('No library name +
specified for output queue.')
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
GOTO CMDLBL(DIAG)
ENDDO /* COND(&OUTQLIB *EQ ' ') */
ELSE CMD(DO)
CHKOBJ OBJ(&OUTQLIB/&OUTQNAM) OBJTYPE(*OUTQ)
ENDDO /* COND(&OUTQLIB *NE ' ') */
ENDDO /* COND(&OUTQNAM *NE '*ALL') */
/*** Start of program ***/
/* Retrieving ending date */
RTVSYSVAL SYSVAL(QDATETIME) RTNVAR(&CURDATE)
CALLSUBR SUBR(RTVENDDAT) RTNVAL(&RTVENDDAT)
SELECT
WHEN COND(&RTVENDDAT *EQ -1) THEN(DO) /* SUBR error */
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
GOTO CMDLBL(DIAG)
ENDDO /* COND(&RTVENDDAT *EQ -1) */
WHEN COND(&RTVENDDAT *EQ 0) THEN(DO) /* Ok */
ENDDO /* COND(&RTVENDDAT *EQ 0) */
OTHERWISE CMD(DO)
CHGVAR VAR(&MSGDTA) VALUE('Invalid return code from +
subroutine' *BCAT &SUBRNAME *TCAT '.')
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
CHGVAR VAR(&DUMP) VALUE('1') /* Dump enabled */
GOTO CMDLBL(DIAG)
ENDDO /* OTHERWISE */
ENDSELECT
/* Retrieving spooled files */
IF COND(&PQOUTQ *EQ '*ALL') THEN(DO)
CHGVAR VAR(&OUTQ) VALUE(&PQOUTQ)
ENDDO /* COND(&PQOUTQ *EQ '*ALL') */
ELSE CMD(DO)
CHGVAR VAR(&OUTQ) VALUE(&OUTQLIB *TCAT '/' *CAT &OUTQNAM)
ENDDO /* COND(&PQOUTQ *NE '*ALL') */
CHGVAR VAR(&SQL) VALUE('SELECT SFI.CREATION_TIMESTAMP, +
SFI.SPOOLED_FILE_NAME, SFI.SPOOLED_FILE_NUMBER, +
SFI.QUALIFIED_JOB_NAME FROM +
TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => ''' +
*CAT &PUSRNAM *TCAT ''', STATUS => ''*HELD +
*READY *SAVED'', OUTPUT_QUEUE => ''' *CAT &OUTQ +
*TCAT ''', USER_DATA => ''' *CAT &PUSRDTA *TCAT +
''', ENDING_TIMESTAMP => ''' *CAT &CURTIMSTMP +
*TCAT ''')) SFI')
IF COND((&PJOBNAM *NE '*ALL') *OR (&PFILNAM *NE +
'*ALL')) THEN(DO)
CHGVAR VAR(&SQL) VALUE(&SQL *BCAT 'WHERE')
ENDDO /* COND((&PJOBNAM *NE '*ALL') *OR (&PFILNAM *NE +
'*ALL')) */
IF COND(&PJOBNAM *NE '*ALL') THEN(DO)
CHGVAR VAR(&SQL) VALUE(&SQL *BCAT 'SFI.JOB_NAME = ''' +
*CAT &PJOBNAM *TCAT '''')
ENDDO /* COND(&PJOBNAM *NE '*ALL') */
IF COND((&PJOBNAM *NE '*ALL') *AND (&PFILNAM *NE +
'*ALL')) THEN(DO)
CHGVAR VAR(&SQL) VALUE(&SQL *BCAT 'AND')
ENDDO /* COND((&PJOBNAM *NE '*ALL') *AND (&PFILNAM *NE +
'*ALL')) */
IF COND(&PFILNAM *NE '*ALL') THEN(DO)
CHGVAR VAR(&SQL) VALUE(&SQL *BCAT +
'SFI.SPOOLED_FILE_NAME = ''' *CAT &PFILNAM +
*TCAT '''')
ENDDO /* COND(&PFILNAM *NE '*ALL') */
CHGVAR VAR(&SQL) VALUE(&SQL *BCAT 'ORDER BY +
SFI.CREATION_TIMESTAMP')
CHGVAR VAR(&MSGDTA) VALUE('Searching for spooled files to +
remove...')
CALLSUBR SUBR(RUNSQLSEL) RTNVAL(&RUNSQLSEL)
/* Removing spooled files */
SELECT
WHEN COND(&RUNSQLSEL *EQ -1) THEN(DO) /* SUBR error */
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
GOTO CMDLBL(DIAG)
ENDDO /* COND(&RUNSQLSEL *EQ -1) */
WHEN COND(&RUNSQLSEL *EQ 0) THEN(DO) /* Ok */
ENDDO /* COND(&RUNSQLSEL *EQ 0) */
OTHERWISE CMD(DO)
CHGVAR VAR(&MSGDTA) VALUE('Invalid return code from +
subroutine' *BCAT &SUBRNAME *TCAT '.')
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
CHGVAR VAR(&DUMP) VALUE('1') /* Dump enabled */
GOTO CMDLBL(DIAG)
ENDDO /* OTHERWISE */
ENDSELECT
CALLSUBR SUBR(RMVSPLFIL) RTNVAL(&RMVSPLFIL)
SELECT
WHEN COND(&RMVSPLFIL *EQ -1) THEN(DO) /* SUBR error */
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
GOTO CMDLBL(DIAG)
ENDDO /* COND(&RMVSPLFIL *EQ -1) */
WHEN COND(&RMVSPLFIL *EQ 0) THEN(DO) /* Ok */
ENDDO /* COND(&RMVSPLFIL *EQ 0) */
OTHERWISE CMD(DO)
CHGVAR VAR(&MSGDTA) VALUE('Invalid return code from +
subroutine' *BCAT &SUBRNAME *TCAT '.')
CHGVAR VAR(&PGMERROR) VALUE('1') /* *ESCAPE */
CHGVAR VAR(&DUMP) VALUE('1') /* Dump enabled */
GOTO CMDLBL(DIAG)
ENDDO /* OTHERWISE */
ENDSELECT
/*** End of program ***/
/* Cleaning up */
CALLSUBR SUBR(CLEANUP)
/* Exit */
GOTO CMDLBL(RETURN)
DIAG:
/* SUBRs don't have to send diagnostic messages, everything has to be done in +
MAIN */
/* Send diagnostic messagge for handled errors */
IF COND(&ERR_TEXT *NE ' ') THEN(DO)
SNDPGMMSG MSGID(CPF9897) MSGF(QCPFMSG) MSGDTA(&ERR_TEXT) +
TOPGMQ(*PRV (*)) TOMSGQ(*TOPGMQ) MSGTYPE(*DIAG)
ENDDO /* COND(&ERR_TEXT *NE ' ') */
IF COND(&MSGID *EQ ' ') THEN(DO)
CHGVAR VAR(&MSGID) VALUE('CPF9897')
ENDDO /* COND(&MSGID *EQ ' ') */
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*SAME (*)) TOMSGQ(*TOPGMQ) MSGTYPE(*DIAG)
IF COND(&PGMERROR *EQ '-') THEN(DO)
CHGVAR VAR(&PGMERROR) VALUE('0') /* *DIAG */
ENDDO /* COND(&PGMERROR *EQ '-') */
ERROR:
IF COND(&ERROR *EQ '1') THEN(RETURN)
CHGVAR VAR(&ERROR) VALUE('1')
RCVMSG PGMQ(*SAME) MSGTYPE(*LAST) RMV(*YES) MSG(&MSG) +
MSGLEN(&MSGLEN) SECLVL(&SECLVL) +
SECLVLLEN(&SECLVLLEN) MSGDTA(&MSGDTA) +
MSGDTALEN(&MSGDTALEN) MSGID(&MSGID) +
RTNTYPE(&RTNTYPE) MSGF(&MSGF) SNDMSGFLIB(&MSGFLIB)
/* MONMSG MSGID(CPF0000) */
IF COND((&PGMERROR *EQ '-') *OR (&DUMP)) THEN(DO) /* +
Global MONMSG or forced dump */
CHGVAR VAR(&ERR_TEXT) VALUE(' ') /* Handled errors only */
OVRPRTF FILE(QPPGMDMP) USRDTA(&PGMNAME) SPLFOWN(*JOB) +
OVRSCOPE(*CALLLVL)
MONMSG MSGID(CPF0000)
DMPCLPGM
MONMSG MSGID(CPF0000)
DLTOVR FILE(QPPGMDMP) LVL(*)
MONMSG MSGID(CPF0000)
ENDDO /* COND((&PGMERROR *EQ '-') *OR (&DUMP)) */
CALLSUBR SUBR(CLEANUP)
/* 02: Diagnostic - 15: Escape (exception already handled at time of RCVMSG) +
- 17: Escape (exception not handled at time of RCVMSG) */
IF COND((&RTNTYPE *EQ '02') *OR (&RTNTYPE *EQ '15') +
*OR (&RTNTYPE *EQ '17')) THEN(DO)
IF COND(&PGMERROR *EQ '0') THEN(DO)
/* Set DIAGNOSTIC message */
CHGVAR VAR(&MSGTYPE) VALUE('*DIAG')
ENDDO /* COND(&PGMERROR *EQ '0') */
ELSE CMD(DO)
/* Set ESCAPE message */
CHGVAR VAR(&MSGTYPE) VALUE('*ESCAPE')
ENDDO /* COND(&PGMERROR *NE '0') */
/* Send error message */
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) TOPGMQ(*PRV (*)) +
TOMSGQ(*TOPGMQ) MSGTYPE(&MSGTYPE)
MONMSG MSGID(CPF0000)
ENDDO /* COND((&RTNTYPE *EQ '02') *OR (&RTNTYPE *EQ +
'15') *OR (&RTNTYPE *EQ '17')) */
/* Normal exit */
RETURN:
RETURN
/* SUBR(RTVENDDAT): Start *****************************************************/
RTVENDDAT: SUBR SUBR(RTVENDDAT)
CHGVAR VAR(&SUBRNAME) VALUE('RTVENDDTA')
CHGVAR VAR(&RTVENDDAT) VALUE(0)
CALLPRC PRC(CEEDAYS) PARM((&CURYYMD) ('YYYYMMDD') +
(&LILDATE) (*OMIT))
MONMSG MSGID(CEE0000) EXEC(DO)
CHGVAR VAR(&ERR_PGM) VALUE('CEEDAYS')
CHGVAR VAR(&ERR_FUN) VALUE('PRC')
CHGVAR VAR(&ERR_TEXT) VALUE(&ERR_FUN *TCAT '(' *CAT +
&ERR_PGM *TCAT ')@SUBR(' *CAT &SUBRNAME +
*TCAT '): An error occurred. Check the job +
log.')
RTNSUBR RTNVAL(-1)
ENDDO /* MSGID(CEE0000) */
CHGVAR VAR(&LILDATE) VALUE(&LILDATE - &PDAYS)
CALLPRC PRC(CEEDATE) PARM((&LILDATE) ('YYYY-MM-DD') +
(&YYYY_MM_DD) (*OMIT))
MONMSG MSGID(CEE0000) EXEC(DO)
CHGVAR VAR(&ERR_PGM) VALUE('CEEDATE')
CHGVAR VAR(&ERR_FUN) VALUE('PRC')
CHGVAR VAR(&ERR_TEXT) VALUE(&ERR_FUN *TCAT '(' *CAT +
&ERR_PGM *TCAT ')@SUBR(' *CAT &SUBRNAME +
*TCAT '): An error occurred. Check the job +
log.')
RTNSUBR RTNVAL(-1)
ENDDO /* MSGID(CEE0000) */
IF COND(&PDAYS *EQ 0) THEN(DO)
CHGVAR VAR(&HH_MI_SS) VALUE(&CURHH *CAT &TIMSEP +
*CAT &CURMI *CAT &TIMSEP *CAT &CURSS)
CHGVAR VAR(&CURTIMSTMP) VALUE(&YYYY_MM_DD *BCAT +
&HH_MI_SS *CAT '.' *CAT &CURMICSEC)
ENDDO
ELSE CMD(DO)
CHGVAR VAR(&CURTIMSTMP) VALUE(&YYYY_MM_DD)
ENDDO
/* CHGVAR VAR(&RTVENDDAT) VALUE(0) */
ENDSUBR RTNVAL(&RTVENDDAT)
/* SUBR(RTVENDDAT): End *******************************************************/
/* SUBR(RUNSQLSEL): Start *****************************************************/
RUNSQLSEL: SUBR SUBR(RUNSQLSEL)
CHGVAR VAR(&SUBRNAME) VALUE('RUNSQLSEL')
CHGVAR VAR(&RUNSQLSEL) VALUE(0)
DLTF FILE(&SQLLIB/&SQLFIL)
MONMSG MSGID(CPF2105) EXEC(DO)
RCVMSG MSGTYPE(*LAST) RMV(*YES)
ENDDO /* MSGID(CPF2105) */
IF COND(&MSGID *EQ ' ') THEN(DO)
CHGVAR VAR(&MSGID) VALUE('CPI8859')
ENDDO /* COND(&MSGID *EQ ' ') */
IF COND(&MSGDTA *EQ ' ') THEN(DO)
CHGVAR VAR(&MSGDTA) VALUE('Running SQL statement...')
ENDDO /* COND(&MSGDTA *EQ ' ') */
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*EXT) TOMSGQ(*TOPGMQ) MSGTYPE(*STATUS)
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)
CHGVAR VAR(&ERR_PGM) VALUE('RUNSQL')
CHGVAR VAR(&ERR_FUN) VALUE('CMD')
CHGVAR VAR(&ERR_TEXT) VALUE(&ERR_FUN *TCAT '(' *CAT +
&ERR_PGM *TCAT ')@SUBR(' *CAT &SUBRNAME +
*TCAT '): An error occurred. Check the job +
log.')
CHGVAR VAR(&MSGID) VALUE('CPF9897')
CHGVAR VAR(&MSGDTA) VALUE('SQL statement: "' *CAT +
&SQL *TCAT '".')
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*EXT) TOMSGQ(*TOPGMQ) MSGTYPE(*STATUS)
RTNSUBR RTNVAL(-1)
ENDDO /* MSGID(CPF0000) */
/* CHGVAR VAR(&RUNSQLSEL) VALUE(0) */
ENDSUBR RTNVAL(&RUNSQLSEL)
/* SUBR(RUNSQLSEL): End *******************************************************/
/* SUBR(RMVSPLFIL): Start *****************************************************/
RMVSPLFIL: SUBR SUBR(RMVSPLFIL)
CHGVAR VAR(&SUBRNAME) VALUE('RMVSPLFIL')
CHGVAR VAR(&RMVSPLFIL) VALUE(0)
RTVMBRD FILE(&SQLLIB/&SQLFIL) MBR(&SQLMBR) +
NBRCURRCD(&SQLROWS)
IF COND(&SQLROWS *GT 0) THEN(DO)
CHGVAR VAR(&MSGID) VALUE('CPI8859')
CHGVAR VAR(&MSGDTA) VALUE('Removing spooled files...')
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*EXT) TOMSGQ(*TOPGMQ) MSGTYPE(*STATUS)
CHGVAR VAR(&RCDNUM) VALUE(0)
CHGVAR VAR(&CMD_OK) VALUE(0)
CHGVAR VAR(&CMD_KO) VALUE(0)
OVRDBF FILE(SQLOUT) TOFILE(&SQLLIB/&SQLFIL) +
MBR(&SQLMBR) LVLCHK(*NO) OVRSCOPE(*CALLLVL)
CHGJOB LOGCLPGM(*NO)
MONMSG MSGID(CPF0000)
DOWHILE COND(&RCVSQLOUT)
RCVF OPNID(SQLOUT)
MONMSG MSGID(CPF0864) EXEC(LEAVE)
CHGVAR VAR(&RCDNUM) VALUE(&RCDNUM + 1)
CHGVAR VAR(&CMD) VALUE('DLTSPLF FILE(' *CAT +
%SST(&SQLOUT_SPLFNAME 3 10) *TCAT ') +
JOB(' *CAT %SST(&SQLOUT_JOB 3 28) *TCAT +
') SPLNBR(' *CAT %CHAR(&SQLOUT_SPLFNBR) +
*TCAT ')')
IF COND(&PVERBOSE *EQ '*YES') THEN(DO)
/* IF COND(&RCDNUM *LE 100) THEN(DO) */
SNDPGMMSG MSG('CMD: "' *CAT &CMD *TCAT '"')
/* ENDDO COND(&RCDNUM *LE 100) */
ENDDO /* COND(&PVERBOSE *EQ '*YES') */
IF COND(&PRMV *EQ '*YES') THEN(DO)
CALL PGM(QCMDEXC) PARM(&CMD &CMDLEN)
MONMSG MSGID(CPF0000) EXEC(DO)
CHGVAR VAR(&MSGID) VALUE('CPF9897')
CHGVAR VAR(&MSGDTA) VALUE('A problem +
occurred while deleting FILE(' +
*CAT %SST(&SQLOUT_SPLFNAME 3 10) +
*TCAT ') JOB(' *CAT +
%SST(&SQLOUT_JOB 3 28) *TCAT ') +
SPLNBR(' *CAT +
%CHAR(&SQLOUT_SPLFNBR) *TCAT ').')
CHGVAR VAR(&MSGTYPE) VALUE('*DIAG')
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) +
MSGDTA(&MSGDTA) TOPGMQ(*PRV) +
TOMSGQ(*TOPGMQ) MSGTYPE(&MSGTYPE)
CHGVAR VAR(&CMD_KO) VALUE(&CMD_KO + 1)
ITERATE
ENDDO /* MSGID(CPF0000) */
ENDDO /* COND(&PRMV *EQ '*YES') */
CHGVAR VAR(&CMD_OK) VALUE(&CMD_OK + 1)
ENDDO /* DOWHILE */
CHGJOB LOGCLPGM(&LOGCLPGM)
MONMSG MSGID(CPF0000)
CLOSE OPNID(SQLOUT) /* RVCF */
DLTOVR FILE(SQLOUT) LVL(*) /* OVRDBF */
CHGVAR VAR(&MSGID) VALUE('CPI8859')
IF COND(&PRMV *EQ '*YES') THEN(DO)
CHGVAR VAR(&MSGDTA) VALUE('Spooled file +
selected:' *BCAT %CHAR(&SQLROWS) *BCAT +
'- Removed:' *BCAT %CHAR(&CMD_OK) *BCAT +
'- Failed:' *BCAT %CHAR(&CMD_KO))
ENDDO /* COND(&PRMV *EQ '*YES') */
ELSE CMD(DO)
CHGVAR VAR(&MSGDTA) VALUE('Spooled file +
selected:' *BCAT %CHAR(&SQLROWS))
ENDDO /* COND(&PRMV *NE '*YES') */
CHGVAR VAR(&MSGTYPE) VALUE('*COMP')
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*PRV) TOMSGQ(*TOPGMQ) MSGTYPE(&MSGTYPE)
ENDDO /* COND(&NBRCURRCD *GT 0) */
ELSE CMD(DO)
CHGVAR VAR(&MSGID) VALUE('CPI8859')
CHGVAR VAR(&MSGDTA) VALUE('No spooled files exist +
which match the selection criteria.')
CHGVAR VAR(&MSGTYPE) VALUE('*COMP')
SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) +
TOPGMQ(*PRV) TOMSGQ(*TOPGMQ) MSGTYPE(&MSGTYPE)
ENDDO /* COND(&NBRCURRCD *EQ 0) */
/* CHGVAR VAR(&RMVSPLFIL) VALUE(0) */
ENDSUBR RTNVAL(&RMVSPLFIL)
/* SUBR(RMVSPLFIL): End *******************************************************/
/* SUBR(CLEANUP): Start *******************************************************/
CLEANUP: SUBR SUBR(CLEANUP)
CHGVAR VAR(&SUBRNAME) VALUE('CLEANUP')
DLTF FILE(&SQLLIB/&SQLFIL)
MONMSG MSGID(CPF2105) EXEC(DO)
RCVMSG MSGTYPE(*LAST) RMV(*YES)
ENDDO /* MSGID(CPF2105) */
MONMSG MSGID(CPF0000)
ENDSUBR RTNVAL(0)
/* SUBR(CLEANUP): End *********************************************************/
ENDPGM:
ENDPGMExample of CLLE program used to delete spooled files older than 7 days of some types of jobs:
/* 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)
RMVOLDSPLF JOBNAME(QP0ZSPWT) USERNAME(*ALL) DAYS(7)
MONMSG MSGID(CPF0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RMVOLDSPLF JOBNAME(QP0ZSPWP) USERNAME(*ALL) DAYS(7)
MONMSG MSGID(CPF0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RMVOLDSPLF JOBNAME(QJVACMDSRV) USERNAME(*ALL) DAYS(7)
MONMSG MSGID(CPF0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RMVOLDSPLF JOBNAME(QZSHSH) USERNAME(*ALL) DAYS(7)
MONMSG MSGID(CPF0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RMVOLDSPLF JOBNAME(QRWTSRVR) USERNAME(QUSER) DAYS(7)
MONMSG MSGID(CPF0000) EXEC(CHGVAR VAR(&E) VALUE('1'))
RMVOLDSPLF JOBNAME(QPRTJOB) USERNAME(*ALL) USRDTA(QZRCSRVS) +
DAYS(7)
MONMSG MSGID(CPF0000) 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
- Guru: Maximum Number Of Jobs And Job Table Warning Customization
- Job Table Capacity (Recovering from SRCB9003610)
- CPI1468 Messages – When and Where Sent
- Detach Spooled Files From a Job
- Admin Alert: When System Job Tables Attack, Part I
- Admin Alert: When System Job Tables Attack, Part II
- Eliminate QZSHSH, QP0ZSPWP/T Job Logs with “No Job Log” (from IBM i 7.5)1
- In IBM i 7.5 a job log will no longer be produced when there are no messages in the job log, unless the user explicitly requests a job log using the DSPJOBLOG OUTPUT(*PRINT) command, in which case a job log will be spooled, and it will contain a single message, CPF2523, “No job log information.” ↩︎
Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 for i – FAQ & Howtos (EN)
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
IBM i 7.4 Announcement (En)
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Generated Always Columns (EN)
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
