Change BRMS media used in the current job

ChangeTapeJob.sql

-- Purpose: List tapes used during this job 
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.5.0?topic=services-qusrbrmmedia-info-view-sql-service
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-job-info-table-function

SELECT VOLUME_SERIAL
            FROM QUSRBRM.MEDIA_INFO
                 INNER JOIN QUSRBRM.QA1AMM
                     ON VOLUME_SERIAL = TMCVSR
                 INNER JOIN TABLE (
                         QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE', JOB_NAME_FILTER => '*')
                     )
                     ON TRIM(JOB_NAME) = TRIM(LPAD(TMJNBR, 6, '0') CONCAT '/' CONCAT TRIM(TMUSER) CONCAT '/' CONCAT TRIM(TMCJOB));

    stop;

-- Purpose: Change BRMS media used in the current job passing parameters to be changed
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.5.0?topic=services-qusrbrmmedia-info-view-sql-service
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-job-info-table-function

CREATE OR REPLACE PROCEDURE SQLTOOLS.CHGJOBMED (
                IN MOVPCY VARCHAR(10) DEFAULT '*SAME',  --New Move Policy
                IN MEDCLS VARCHAR(10) DEFAULT '*SAME',  --New Media Class
                IN RETENTION INTEGER DEFAULT 0          --Retention days, 9999 for *PERM
            )
        LANGUAGE SQL
        SPECIFIC SQLTOOLS.CHGJOBMED
        NOT DETERMINISTIC
        MODIFIES SQL DATA
        CALLED ON NULL INPUT
        SET OPTION ALWBLK = *ALLREAD,
                   ALWCPYDTA = *OPTIMIZE,
                   COMMIT = *NONE,
                   DECRESULT = (31,
                   31,
                   00),
                   DYNDFTCOL = *NO,
                   DYNUSRPRF = *USER,
                   SRTSEQ = *HEX
BEGIN
    DECLARE TAPE CHAR(6);
    DECLARE NEWEXP VARCHAR(6);
    DECLARE DATFMT VARCHAR(10);
    DECLARE CMD VARCHAR(1000);
    DECLARE DAYOFMON INT;
    DECLARE FINE INT DEFAULT 0;

    -- I'm calculating the new retention for the tape
    DECLARE CURCHGJOBMED CURSOR FOR
        SELECT VOLUME_SERIAL,
               CASE
                   WHEN RETENTION <> 9999 THEN TO_CHAR(CREATED + RETENTION DAYS, 'ddMMyy')
                   ELSE '*PERM'
               END
            FROM QUSRBRM.MEDIA_INFO
                 INNER JOIN QUSRBRM.QA1AMM
                     ON VOLUME_SERIAL = TMCVSR
                 INNER JOIN TABLE (
                         QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE', JOB_NAME_FILTER => '*')
                     )
                     ON TRIM(JOB_NAME) = TRIM(LPAD(TMJNBR, 6, '0') CONCAT '/' CONCAT TRIM(TMUSER) CONCAT '/' CONCAT TRIM(TMCJOB));
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINE = 1;
    VALUES
        DAY(CURRENT DATE) INTO DAYOFMON;
    IF DAYOFMON > 7 THEN
        RETURN;
    END IF;

    SELECT DATE_FORMAT
        INTO DATFMT
        FROM TABLE (
                QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL', JOB_NAME_FILTER => '*')
            );
    CALL QSYS2.QCMDEXC('CHGJOB DATFMT(*DMY)');
    OPEN CURCHGJOBMED;
    FETCH CURCHGJOBMED
        INTO TAPE,
             NEWEXP;
    WHILE FINE = 0 DO
        -- I'm preparing command to run in order to change BRMS tape
        VALUES
            'CHGMEDBRM VOL(' CONCAT TAPE CONCAT ') MEDCLS(' CONCAT MEDCLS CONCAT ') EXPDATE(' CONCAT NEWEXP CONCAT ') MOVPCY('
                CONCAT MOVPCY CONCAT ')' INTO CMD;
        CALL SYSTOOLS.LPRINTF(
            TRIM(CMD)
        );
        CALL QSYS2.QCMDEXC(
            TRIM(CMD)
        );
        FETCH CURCHGJOBMED
            INTO TAPE,
                 NEWEXP;
    END WHILE;

    CALL QSYS2.QCMDEXC(
        'CHGJOB DATFMT(' CONCAT TRIM(DATFMT) CONCAT ')'
    );
END;

stop;