midrange.com code scratchpad
Name:
PURGE_ORDINQ_SPOOL_FILES
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
03/12/2021 12:07:42 pm
IP:
Logged
Description:
The user was to lazy to give a description
Code:
  1. --  category:  SPL
  2. --  description:  Purge Spool Files.  Uses QCMDEXC to process entries from OUTPUT_QUEUE_ENTRIES_BASIC
  3. --
  4.  
  5. -- Test our logic first
  6. SELECT 
  7.     SPOOLED_FILE_NAME,
  8.     JOB_NAME,
  9.     FILE_NUMBER,
  10.     USER_NAME,
  11.     CREATE_TIMESTAMP,
  12.     'DLTSPLF FILE(' CONCAT SPOOLED_FILE_NAME CONCAT ')  JOB(' CONCAT JOB_NAME CONCAT ') SPLNBR(' CONCAT TRIM(CHAR(FILE_NUMBER)) CONCAT ')'
  13.     FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
  14.     WHERE USER_NAME='ORDINQ'
  15.     AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35
  16.     ORDER BY length(trim(job_name)) ASC
  17. ;
  18. SELECT COUNT(*)
  19.     FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
  20.     WHERE USER_NAME='ORDINQ'
  21.     AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35
  22. ;
  23.  
  24.  
  25. SET schema = 'ROB';
  26.  
  27. drop procedure rob.PURGE_ORDINQ_SPOOL_FILES;
  28.  
  29.  
  30. CREATE PROCEDURE PURGE_ORDINQ_SPOOL_FILES
  31.          LANGUAGE SQL MODIFIES SQL DATA
  32.          SPECIFIC PROC000001
  33.         SET OPTION DATFMT = *ISO
  34.          P1: BEGIN
  35.           DECLARE w_SPOOLED_FILE_NAME CHAR(10);
  36.           DECLARE w_JOB_NAME CHAR(28);
  37.           DECLARE w_FILE_NUMBER INT;
  38.           DECLARE COMMAND CHAR(200);  -- Ensure this is large enough.
  39.           DECLARE END_TABLE INT DEFAULT 0;
  40.           DECLARE C1 CURSOR FOR
  41.  
  42.            SELECT 
  43.             SPOOLED_FILE_NAME,
  44.             JOB_NAME,
  45.             FILE_NUMBER
  46.             FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC
  47.             WHERE USER_NAME='ORDINQ'
  48.             AND DAYS(CURRENT_TIMESTAMP) - DAYS(CREATE_TIMESTAMP) > 35;
  49.           DECLARE CONTINUE HANDLER FOR NOT FOUND
  50.             SET END_TABLE = 1;
  51.         --  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  52.         --    SET DEPT_SALARY = NULL;
  53.           OPEN C1;
  54.           FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;
  55.  
  56.           WHILE END_TABLE = 0 DO
  57.              SET COMMAND = 'DLTSPLF FILE(' CONCAT w_SPOOLED_FILE_NAME CONCAT ')  JOB(' CONCAT w_JOB_NAME CONCAT ') SPLNBR(' CONCAT TRIM(CHAR(w_FILE_NUMBER)) CONCAT ')';
  58.              -- set command = 'sndmsg msg(' concat x'7D' concat command concat x'7D' concat ') tousr(rob)';
  59.              CALL QSYS2.QCMDEXC(COMMAND);
  60.           FETCH C1 INTO w_SPOOLED_FILE_NAME, w_JOB_NAME, w_FILE_NUMBER;
  61.            END WHILE;
  62.            CLOSE C1;
  63.          END P1          
  64. ;
  65. CALL ROB.PURGE_ORDINQ_SPOOL_FILES;         
  66.  
  67.  
  68.  
© 2004-2019 by midrange.com generated in 0.01s valid xhtml & css