Code:
- -- category: SPL
- -- description: Purge Spool Files. Uses QCMDEXC to process entries from OUTPUT_QUEUE_ENTRIES_BASIC
- --
-
- -- Test our logic first
- SELECT
- SPOOLED_FILE_NAME,
- JOB_NAME,
- FILE_NUMBER,
- USER_NAME,
- CREATE_TIMESTAMP,
- 'DLTSPLF FILE(' CONCAT SPOOLED_FILE_NAME CONCAT ') JOB(' CONCAT JOB_NAME CONCAT ') SPLNBR(' CONCAT TRIM(CHAR(FILE_NUMBER)) CONCAT ')'
- FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
- WHERE USER_NAME='ORDINQ'
- AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35
- ORDER BY length(trim(job_name)) ASC
- ;
- SELECT COUNT(*)
- FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
- WHERE USER_NAME='ORDINQ'
- AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35
- ;
-
-
- SET schema = 'ROB';
-
- drop procedure rob.PURGE_ORDINQ_SPOOL_FILES;
-
-
- CREATE PROCEDURE PURGE_ORDINQ_SPOOL_FILES
- LANGUAGE SQL MODIFIES SQL DATA
- SPECIFIC PROC000001
- SET OPTION DATFMT = *ISO
- P1: BEGIN
- DECLARE w_SPOOLED_FILE_NAME CHAR(10);
- DECLARE w_JOB_NAME CHAR(28);
- DECLARE w_FILE_NUMBER INT;
- DECLARE COMMAND CHAR(200); -- Ensure this is large enough.
- DECLARE END_TABLE INT DEFAULT 0;
- DECLARE C1 CURSOR FOR
-
- SELECT
- SPOOLED_FILE_NAME,
- JOB_NAME,
- FILE_NUMBER
- FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
- WHERE USER_NAME='ORDINQ'
- AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35;
- DECLARE CONTINUE HANDLER FOR NOT FOUND
- SET END_TABLE = 1;
- -- DECLARE EXIT HANDLER FOR SQLEXCEPTION
- -- SET DEPT_SALARY = NULL;
- OPEN C1;
- FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;
-
- WHILE END_TABLE = 0 DO
- SET COMMAND = 'DLTSPLF FILE(' CONCAT w_SPOOLED_FILE_NAME CONCAT ') JOB(' CONCAT w_JOB_NAME CONCAT ') SPLNBR(' CONCAT TRIM(CHAR(w_FILE_NUMBER)) CONCAT ')';
- -- set command = 'sndmsg msg(' concat x'7D' concat command concat x'7D' concat ') tousr(rob)';
- CALL QSYS2.QCMDEXC(COMMAND);
- FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;
- END WHILE;
- CLOSE C1;
- END P1
- ;
- CALL ROB.PURGE_ORDINQ_SPOOL_FILES;
-
-
-
|
|