msgCPF7024 RC2/msgCPF7018 RC2 - Determining the Open Commit Definition

Run the following SQL with replacing the Journal_Library / Journal_Name / Journal_Receiver1 / Journal_Receiver2 / Journal_Receiver_Library with the correct journal information.

WITH FirstRcvr (Journal_Receiver_Library, Journal_Receiver_Name, ATTACH_TIMESTAMP, DETACH_TIMESTAMP, Journal_Library, Journal_Name)
     AS (
         SELECT Journal_Receiver_Library,
                Journal_Receiver_Name,
                ATTACH_TIMESTAMP,
                IFNULL(DETACH_TIMESTAMP, TIMESTAMP_FORMAT('9999-12-31', 'YYYY-MM-DD')),
                Journal_Library,
                Journal_Name
             FROM QSYS2.JOURNAL_RECEIVER_INFO A
             WHERE Journal_Library =upper('Journal_Library')
                   AND Journal_Name = upper('Journal_Name')
                   AND (Previous_Journal_Receiver IS NULL
                       OR Previous_Journal_Receiver NOT IN (SELECT Journal_Receiver_Name
                               FROM QSYS2.JOURNAL_RECEIVER_INFO
                      WHERE Journal_Library = A.Journal_Library
                            AND Journal_Name = A.Journal_Name))
     ),
     Lock_Space (Lock_Space_Id, Job_Name, User_Name, Logical_Unit_Of_Work_Id, Logical_Unit_Of_Work_State, State_Timestamp,
             Local_Changes_Pending, Remote_Changes_Pending) AS (
         SELECT Lock_Space_Id,
                Job_Name,
                User_Name,
                Logical_Unit_Of_Work_Id,
                Logical_Unit_Of_Work_State,
                State_Timestamp,
                Local_Changes_Pending,
                Remote_Changes_Pending
             FROM Qsys2.Db_Transaction_Info
             WHERE State_Timestamp IS NOT NULL
     )
    SELECT DISTINCT 'WRKCMTDFN JOB(*ALL) LUWID(''' CONCAT REPLACE(A.Logical_Unit_Of_Work_Id, '''', '''''') CONCAT ''')' WRKCMTDFN,
                    A.Job_Name,
                    A.Lock_Space_Id,
                    A.User_Name,
                    A.Logical_Unit_Of_Work_State,
                    A.State_Timestamp,
                    A.Local_Changes_Pending,
                    A.Remote_Changes_Pending
        FROM FirstRcvr rcv,
             Lock_Space A
             INNER JOIN
        table
         (QSYS2.DISPLAY_JOURNAL(
            JOURNAL_LIBRARY => RCV.JOURNAL_LIBRARY,
            JOURNAL_NAME => RCV.JOURNAL_NAME,
            STARTING_RECEIVER_NAME => RCV.JOURNAL_RECEIVER_NAME,
            STARTING_RECEIVER_LIBRARY => RCV.JOURNAL_RECEIVER_LIBRARY,
            ENDING_RECEIVER_NAME => RCV.JOURNAL_RECEIVER_NAME,
            ENDING_RECEIVER_LIBRARY => RCV.JOURNAL_RECEIVER_LIBRARY,
            JOURNAL_CODES => 'C',
            JOURNAL_ENTRY_TYPES => 'SC,BC,SB')
         ) AS X
                 ON A.Job_Name = (X.Job_Number CONCAT '/' CONCAT TRIM(X.Job_User) CONCAT '/' CONCAT TRIM(X.Job_Name))
        WHERE a.state_timestamp < rcv.DETACH_TIMESTAMP
              OR A.Local_Changes_Pending = 'YES'
              OR A.Remote_Changes_Pending = 'YES';

This SQL is also provided by QMGTOOLS:

Either in QMGTOOLS/QMGDBSQL(OPENCMT) which you can open within ACS Run SQL Scripts as Source Physical File:

Alternatively a source for a program is provided in QMGTOOLS/QMGDBSQL(OPENCMTPGM)

This can be compiled with:

CRTBNDCL PGM(<YourLib>/OPENCMTPGM) SRCFILE(QMGTOOLS/QMGDBSQL) DBGVIEW(*ALL)

On the OS/400 command line, type the following:

DSPJRN JRN(JRNLIB/JRNNAME)
    RCVRNG(RCVLIB/RCVNAME RCVLIB/RCVNAME)
    JRNCDE((C *ALLSLT))
    ENTTYP(SC RB CM EC)
    OUTPUT(*OUTFILE)
    OUTFILFMT(*TYPE5)
    OUTFILE(QGPL/JRNDATA)

Press the Enter key.

This creates an output file of all the commit journal entries for a range of receivers.

The starting journal receiver should be the receiver you are trying to delete.

It is recommended that the ending journal receiver is the *CURRENT receiver although, depending on how many receivers there are total, this could create a very large output file.

If there are many receivers and you believe the output file will be too large, select an ending receiver that is far enough past the starting journal receiver in the receiver chain to ensure that the SC entry types will find their RB, EC, or CM matches.

Depending on the receiver sizes, this can be 5 or 500.

The outfile name used in this example and following steps is QGPL/JRNDATA.
- If a different name is used then you must change the name used below.

Note that this SQL statement has two common table expressions.   You need to copy/paste the entire SQL statement.   

with boundary as (
SELECT JOSEQN, JONBR, JOUSER, JOJOB, JOENTT, JOTSTP, JOCCID
FROM jrndata
WHERE JOCODE = 'C'
and JOENTT = 'SC' or JOENTT = 'EC' or
JOENTT = 'CM' or JOENTT ='RB'),

lastop as (
select max(joseqn) joseqn, jonbr, jouser, jojob, joccid
from boundary
group by jonbr, jouser, jojob, joccid)

select boundary.*
from boundary inner join lastop
on boundary.joseqn=lastop.joseqn and joentt='SC'

Screen print showing the entire SQL statement: