Find Open Commit Cycles

findOpenCommitCycles.sql

CREATE OR REPLACE FUNCTION SYSTOOLS.FIND_OPEN_COMMIT_CYCLES(
            JOURNAL_NAME VARCHAR(10) DEFAULT '*ALL',
            JOURNAL_LIBRARY VARCHAR(10) DEFAULT '*ALL',
            IASP_NUMBER INT DEFAULT 0
        ) 
    RETURNS TABLE (
        JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
        JOURNAL_RECEIVER_NAME VARCHAR(10),
        JOURNAL_LIBRARY VARCHAR(10),
        JOURNAL_NAME VARCHAR(10),
        COMMITMENT_DEFINITION VARCHAR(10),
        JOB_NAME VARCHAR(28),
        LOGICAL_UNIT_OF_WORK_STATE VARCHAR(20),
        PENDING_CHANGES BIGINT,
        STATE_TIMESTAMP TIMESTAMP,
        DEFAULT_LOCK_LEVEL VARCHAR(4),
        JOURNAL_RECEIVER_ASP_NUMBER INT,
        COMMIT_CYCLE DECIMAL(21, 0)
    )
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN
        DECLARE STMT VARCHAR(2000);
        DECLARE JRN_CURSOR CURSOR FOR JRN_SETUP_STMT;
        DECLARE GLOBAL TEMPORARY TABLE SESSION.RESULTS (
                    JOURNAL_RECEIVER_LIBRARY VARCHAR(10),
                    JOURNAL_RECEIVER_NAME VARCHAR(10),
                    JOURNAL_LIBRARY VARCHAR(10),
                    JOURNAL_NAME VARCHAR(10),
                    COMMITMENT_DEFINITION VARCHAR(10),
                    JOB_NAME VARCHAR(28),
                    LOGICAL_UNIT_OF_WORK_STATE VARCHAR(20),
                    PENDING_CHANGES BIGINT,
                    STATE_TIMESTAMP TIMESTAMP,
                    DEFAULT_LOCK_LEVEL VARCHAR(4),
                    JOURNAL_RECEIVER_ASP_NUMBER INT,
                    COMMIT_CYCLE DECIMAL(21, 0)
                ) WITH REPLACE;
        
        SET STMT = 'INSERT INTO SESSION.RESULTS (
                    SELECT 
                    JOURNAL_RECEIVER_LIBRARY,
                    JOURNAL_RECEIVER_NAME,
                    JRNRCVINF.JOURNAL_LIBRARY,
                    JRNRCVINF.JOURNAL_NAME,
                    COMMITMENT_DEFINITION,
                    JOB_NAME,
                    LOGICAL_UNIT_OF_WORK_STATE,
                    PENDING_CHANGES,
                    STATE_TIMESTAMP,
                    DEFAULT_LOCK_LEVEL,
                    JOURNAL_RECEIVER_ASP_NUMBER,
                    COMMIT_CYCLE
                    FROM QSYS2.DB_TRANSACTION_INFO DTI,
                      TABLE (
                          QSYS2.DB_TRANSACTION_JOURNAL_INFO(DTI.LOCK_SPACE_ID)
                      ) TXJRNINF,
                      QSYS2.JOURNAL_RECEIVER_INFO JRNRCVINF
                 WHERE JRNRCVINF.JOURNAL_NAME = TXJRNINF.JOURNAL_NAME
                       AND JRNRCVINF.JOURNAL_LIBRARY = TXJRNINF.JOURNAL_LIBRARY
                       AND COMMIT_CYCLE IS NOT NULL ';
          
          IF JOURNAL_NAME != '*ALL' THEN
            SET STMT = STMT CONCAT 'AND JRNRCVINF.JOURNAL_NAME = ''' CONCAT JOURNAL_NAME CONCAT '''';
          END IF;
          
          IF JOURNAL_LIBRARY != '*ALL' THEN
            SET STMT = STMT CONCAT 'AND JRNRCVINF.JOURNAL_LIBRARY = ''' CONCAT JOURNAL_LIBRARY CONCAT '''';
          END IF;
          
          IF IASP_NUMBER != 0 THEN
            SET STMT = STMT CONCAT 'AND JOURNAL_RECEIVER_ASP_NUMBER = ''' CONCAT IASP_NUMBER CONCAT '''';
          END IF;  
          
          SET STMT = STMT CONCAT ')';
          
          PREPARE S1 FROM STMT;
          EXECUTE S1;  
          
          RETURN SELECT * FROM SESSION.RESULTS;
    END;


SELECT * FROM TABLE(SYSTOOLS.FIND_OPEN_COMMIT_CYCLES(JOURNAL_LIBRARY => 'GTEST'));