‎

1. Reset the journal receiver sequence:

CHGJRN JRN(Library/Journal) JRNRCV(*GEN) SEQOPT(*RESET)

e.g: CHGJRN JRN(AMFLIBx/MPXJRN) JRNRCV(*GEN) SEQOPT(*RESET)

*Where x is the second character of the XA environment

2. Find all the offending timestamp tables (tables that have a record with a journal sequence of more than 2,000,000,000):

SELECT * FROM MESxy.SLDATATS

ORDER BY STAMP DESC

Note: Anything that is returned in this query that has a journal sequence (JRNSEQ) higher than 2,000,000,000 needs to be reset

XA files to SQL table names:

1. EMPMAS   = employee

2. EMPMSX   = employee

3. FACMST = work center for EPDM

4. ITEMASA = item site

5. ITEMBL = items warehouse

6. ITMENT = item site

7. ITMRVA = item revision

8. ITMSIT = item site

9. MODATA = manufacturing order component item

10. MODESC = manufacturing operation notes

11. MOMAST = manufacturing order

12. MOROUT = manufacturing order operations

13. POBLKT = purchase order line schedule

14. POITEM = purchase order item

15. POMAST = purchase order

16. REASON = reason codes

17. SITMST = sites for EPDM

18. SLDATA = inventory locations

19. SLQNTY = inventory balance

20. VENNAM = customer

21. WHSMST = warehouse

22. WRKCTR = work center for PDM

*Where xy is the name of the sync library of the XA environment

*Add 'TS' to the end of any XA table name to see the time stamp table for the given file

3. Find the relative record number in the table:

SELECT RRN(SLDATATS) FROM MESxy.SLDATATS

ORDER BY STAMP DESC

4. Set the journal sequence to the relative record number

UPDATE MESxy.SLDATATS T

SET JRNSEQ =

(

    SELECT RN

    FROM

    (

        SELECT

            RRN(T2) AS RRN,

            ROW_NUMBER() OVER (ORDER BY RRN(T2)) AS RN

        FROM MES11.SLDATATS T2

    ) X

    WHERE X.RRN = RRN(T)

)

5. Repeat steps 3 and 4 for each offending table