Cleaning Up Old, Unsaved Journal Receivers

JournalReceiverCleanup.SQL

/*************************************************************************************************/
/* Manage consumption of storage by journal receivers, only keep last N days of journals         */
/*************************************************************************************************/
SELECT *
    FROM QSYS2.JOURNAL_RECEIVER_INFO
    WHERE DETACH_TIMESTAMP < CURRENT DATE - 3 DAYS;


-- NOTE: *IGNINQMSG is not specified for DLTJRNRCV. Any unsaved journals receivers will not be deleted.
CREATE OR REPLACE FUNCTION SYSTOOLS.DELETE_OLD_RECEIVERS (
            DAYS_OLD INT,
            RECEIVER_LIBRARY VARCHAR(1000) DEFAULT '*ALL',
            RECEIVER_NAME VARCHAR(10) DEFAULT '*ALL',
            DELETE_UNSAVED VARCHAR(3) DEFAULT 'NO',
            PREVIEW VARCHAR(3) DEFAULT 'YES'
        ) 
    RETURNS TABLE (
        ACTION_TAKEN VARCHAR(3),
        JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
        JOURNAL_RECEIVER_NAME VARCHAR(10),
        DESCRIPTIVE_TEXT VARCHAR(50),
        JOURNAL_LIBRARY VARCHAR(10),
        JOURNAL_NAME VARCHAR(10),
        SIZE INTEGER,
        ATTACH_TIMESTAMP TIMESTAMP,
        DETACH_TIMESTAMP TIMESTAMP,
        SAVE_TIMESTAMP TIMESTAMP
    ) SPECIFIC SYSTOOLS.DLTOLDJRCV
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        FOR JRN_CURSOR CURSOR FOR SELECT X.*
                                FROM QSYS2.JOURNAL_RECEIVER_INFO X, TABLE(SYSTOOLS.SPLIT(RECEIVER_LIBRARY, ',')) Y
                                WHERE DETACH_TIMESTAMP < CURRENT TIMESTAMP - DAYS_OLD DAYS 
                                    AND (JOURNAL_RECEIVER_LIBRARY LIKE REPLACE(RECEIVER_LIBRARY, '*ALL', '%') OR X.JOURNAL_RECEIVER_LIBRARY = TRIM(Y.ELEMENT)) -- Handle both *ALL and comma-delimited formats
                                    AND (JOURNAL_RECEIVER_NAME LIKE REPLACE(RECEIVER_NAME, '*ALL', '%'))
                                    AND (UPPER(DELETE_UNSAVED) = 'YES' OR (SAVE_TIMESTAMP IS NOT NULL AND UPPER(DELETE_UNSAVED) = 'NO'))
                                ORDER BY DETACH_TIMESTAMP ASC, JOURNAL_RECEIVER_NAME ASC  
        DO
         IF PREVIEW = 'NO' THEN
            CALL QSYS2.QCMDEXC('DLTJRNRCV JRNRCV(' CONCAT JOURNAL_RECEIVER_LIBRARY CONCAT '/' CONCAT JOURNAL_RECEIVER_NAME CONCAT ')');
            PIPE ('YES', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
         ELSE
            PIPE ('NO', JOURNAL_RECEIVER_LIBRARY, JOURNAL_RECEIVER_NAME, DESCRIPTIVE_TEXT, JOURNAL_LIBRARY, JOURNAL_NAME, SIZE, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, SAVE_TIMESTAMP);
         END IF;
         END FOR;
        RETURN;
    END;

-- PREVIEW            => 'YES' will NOT delete receivers (default)
--                        'NO' will delete receivers
-- RECEIVER_LIBRARY   => '*ALL' will search all libraries on system (default)
--                       Otherwise, a comma-delimited list of library names can be provided
-- RECEIVER_NAME      => '*ALL' will search for all receiver names
--                       Otherwise, this is handled as a LIKE predicate, meaning wildcarding can be used. See example below.
-- DELETE_UNSAVED     => 'NO' will prevent unsaved receivers from being deleted (default)
--                       'YES' will delete all receivers, including unsaved ones (typically not recommended)
-- DAYS_OLD           => Journals with a detach timestamp older than DAYS_OLD days ago will be deleted
SELECT * FROM TABLE (SYSTOOLS.DELETE_OLD_RECEIVERS(DAYS_OLD => 3,
                                                   RECEIVER_LIBRARY => 'QSYS',
                                                   RECEIVER_NAME => 'QSQ%', -- Find all journal names starting with QSQ
                                                   DELETE_UNSAVED => 'NO',
                                                   PREVIEW => 'YES'));