Below is a simple REXX program and the corresponding JCL used to format the following DB2 messages:
DSNT376I - TIMEOUT
DSNT375I - DEADLOCK
DSNT501I - RESOURCE UNAVAILABLE
DSNI031I - LOCK ESCALATION
DSNJ031I - Unit of Recovery (UR) has written a high volume of log records without a commit or rollback
REXX code : DB2MSGFMT
/* REXX */
"EXECIO * DISKR IN (FINIS STEM IN_REC."
TMO_CNT = 0
DED_CNT = 0
RSC_CNT = 0
UCM_CNT = 0
ESC_CNT = 0
CALL WRITE_HEADERS
TRACE N
DO I = 1 TO IN_REC.0
IF SUBSTR(IN_REC.I,19,5) = '----' &,
SUBSTR(IN_REC.I,47,5) = '----' THEN
DO
DT = SUBSTR(IN_REC.I,35,11)
ITERATE
END
IF SUBSTR(IN_REC.I,20,8) = 'DSNT376I' THEN
DO
CALL EXTRACT_DATA
CALL WRITE_TIME_OUT_DATA
ITERATE
END
IF SUBSTR(IN_REC.I,20,8) = 'DSNT375I' THEN
DO
CALL EXTRACT_DATA
CALL WRITE_DEADLOCK_DATA
ITERATE
END
IF SUBSTR(IN_REC.I,20,8) = 'DSNT501I' THEN
DO
CALL CONCAT_LINES
PARSE VAR BG_LN . 'CORRELATION-ID=' CORR1 .,
'CONNECTION-ID=' CONN1 .,
'LUW-ID=' LUW1 .,
'REASON' RSN1 .,
'TYPE' TYP1 .,
'NAME' RSC1 .
CALL WRITE_RESOURCE_DATA
ITERATE
END
IF SUBSTR(IN_REC.I,20,8) = 'DSNJ031I' THEN
DO
CALL CONCAT_LINES
PARSE VAR BG_LN . 'HAS WRITTEN ' CNT1 .,
'CORRELATION NAME = ' CORR1 .,
'CONNECTION ID = ' CONN1 .,
'LUWID = ' LUW1 .,
'PLAN NAME = ' PLN1 .,
'AUTHID = ' AUTH1 .,
'END USER ID = ' USR1 .,
'TRANSACTION NAME = ' XTN1 .,
'WORKSTATION NAME = ' WST1 .
CALL WRITE_UNCOMMIT_LOG_DATA
ITERATE
END
IF SUBSTR(IN_REC.I,20,8) = 'DSNI031I' THEN
DO
CALL CONCAT_LINES
PARSE VAR BG_LN . 'RESOURCE NAME = ' RSC1 .,
'LOCK STATE = ' STAT1 .,
'PLAN NAME : PACKAGE NAME = ' PLN1 ':' PKG1 .,
'COLLECTION-ID = ' COLL1 .,
'CORRELATION-ID = ' CORR1 .,
'CONNECTION-ID = ' CONN1 .,
'LUW-ID = ' LUW1 .,
'THREAD-INFO = ' THD1 .,
'PARTITION-INFO = ' MSG1 ,
'ESCALATED' .
CALL WRITE_LOCK_ESC_DATA
ITERATE
END
END
IF TMO_CNT > 0 THEN
DO
TMO_REC.0 = TMO_CNT
"EXECIO * DISKW TMO (FINIS STEM TMO_REC."
END
IF DED_CNT > 0 THEN
DO
DED_REC.0 = DED_CNT
"EXECIO * DISKW DED (FINIS STEM DED_REC."
END
IF RSC_CNT > 0 THEN
DO
RSC_REC.0 = RSC_CNT
"EXECIO * DISKW RSC (FINIS STEM RSC_REC."
END
IF UCM_CNT > 0 THEN
DO
UCM_REC.0 = UCM_CNT
"EXECIO * DISKW UCM (FINIS STEM UCM_REC."
END
IF ESC_CNT > 0 THEN
DO
ESC_REC.0 = ESC_CNT
"EXECIO * DISKW ESC (FINIS STEM ESC_REC."
END
EXIT
WRITE_TIME_OUT_DATA:
TMO_CNT = TMO_CNT + 1
TMO_REC.TMO_CNT = DT TM || ','||,
STRIP(PLN1) || ','||,
STRIP(CORR1) || ','||,
STRIP(CONN1) || ','||,
STRIP(LUW1) || ','||,
STRIP(THRD1) || ','||,
STRIP(PLN2) || ','||,
STRIP(CORR2) || ','||,
STRIP(CONN2) || ','||,
STRIP(LUW2) || ','||,
STRIP(THRD2)
RETURN
WRITE_DEADLOCK_DATA:
DED_CNT = DED_CNT + 1
DED_REC.DED_CNT = DT TM || ','||,
STRIP(PLN1) || ','||,
STRIP(CORR1) || ','||,
STRIP(CONN1) || ','||,
STRIP(LUW1) || ','||,
STRIP(THRD1) || ','||,
STRIP(PLN2) || ','||,
STRIP(CORR2) || ','||,
STRIP(CONN2) || ','||,
STRIP(LUW2) || ','||,
STRIP(THRD2)
RETURN
WRITE_RESOURCE_DATA:
RSC_CNT = RSC_CNT + 1
RSN1 = ''''||STRIP(RSN1)
TYP1 = ''''||STRIP(TYP1)
RSC_REC.RSC_CNT = DT TM || ','||,
STRIP(CORR1) || ','||,
STRIP(CONN1) || ','||,
STRIP(LUW1) || ','||,
RSN1 || ','||,
TYP1 || ','||,
STRIP(RSC1)
RETURN
WRITE_UNCOMMIT_LOG_DATA:
UCM_CNT = UCM_CNT + 1
UCM_REC.UCM_CNT = DT TM || ','||,
STRIP(CORR1) || ','||,
STRIP(CONN1) || ','||,
STRIP(LUW1) || ','||,
STRIP(PLN1) || ','||,
STRIP(AUTH1) || ','||,
STRIP(USR1) || ','||,
STRIP(XTN1) || ','||,
STRIP(WST1) || ','||,
'HAS WRITTEN 'CNT1' LOG RECORDS'
RETURN
WRITE_LOCK_ESC_DATA:
ESC_CNT = ESC_CNT + 1
ESC_REC.ESC_CNT = DT TM || ','||,
STRIP(PLN1) || ','||,
STRIP(PKG1) || ','||,
STRIP(COLL1) || ','||,
STRIP(CORR1) || ','||,
STRIP(CONN1) || ','||,
STRIP(LUW1) || ','||,
STRIP(THD1) || ','||,
STRIP(RSC1) || ','||,
STRIP(STAT1) || ','||,
MSG1 'ESCALATED'
RETURN
EXTRACT_DATA:
CALL CONCAT_LINES
PARSE VAR BG_LN . 'PLAN=' PLN1 .,
'CORRELATION-ID=' CORR1 .,
'CONNECTION-ID=' CONN1 .,
'LUW-ID=' LUW1 .,
'THREAD-INFO=' THRD1 .,
'PLAN=' PLN2 .,
'CORRELATION-ID=' CORR2 .,
'CONNECTION-ID=' CONN2 .,
'LUW-ID=' LUW2 .,
'THREAD-INFO=' THRD2 .
RETURN
CONCAT_LINES:
TM = SUBSTR(IN_REC.I,1,8)
BG_LN = STRIP(IN_REC.I)
C_NUM = SUBSTR(BG_LN,LENGTH(BG_LN)-2,3)
DO J = I + 1 TO IN_REC.0
IF SUBSTR(IN_REC.J,3,1) = ' ' &,
SUBSTR(IN_REC.J,7,1) = ' ' THEN
NUM = SUBSTR(IN_REC.J,4,3)
ELSE
NUM = '###'
IF C_NUM = NUM THEN
DO
BG_LN = BG_LN || SUBSTR(IN_REC.J,20,69)
I = J
END
ELSE
LEAVE
END
RETURN
WRITE_HEADERS:
TMO_CNT = TMO_CNT + 1
TMO_REC.TMO_CNT = 'TIME,' ||,
'FAILING PLAN,' ||,
'FAILING CORRID,' ||,
'FAILING CONNID,' ||,
'FAILING LUW_ID,' ||,
'FAILING THREAD INFO,'||,
'HOLDING PLAN,' ||,
'HOLDING CORRID,' ||,
'HOLDING CONNID,' ||,
'HOLDING LUW_ID,' ||,
'HOLDING THREAD INFO'
DED_CNT = DED_CNT + 1
DED_REC.DED_CNT = TMO_REC.TMO_CNT
RSC_CNT = RSC_CNT + 1
RSC_REC.RSC_CNT = 'TIME,' ||,
'FAILING CORRID,' ||,
'FAILING CONNID,' ||,
'FAILING LUW_ID,' ||,
'REASON,' ||,
'TYPE,' ||,
'RESOURCE NAME'
UCM_CNT = UCM_CNT + 1
UCM_REC.UCM_CNT = 'TIME,' ||,
'CORRID,' ||,
'CONNID,' ||,
'LUW_ID,' ||,
'PLAN,' ||,
'AUTHID,' ||,
'END_USER_ID,' ||,
'TRANSACTION_NAME,' ||,
'WORKSTATION_NAME,' ||,
MESSAGE
ESC_CNT = ESC_CNT + 1
ESC_REC.ESC_CNT = 'TIME,' ||,
'PLAN,' ||,
'PACKAGE,' ||,
'COLL_ID,' ||,
'CORRID,' ||,
'CONNID,' ||,
'LUW_ID,' ||,
'THREAD INFO,' ||,
'RESOURCE NAME,' ||,
'LOCK_STATE,' ||,
MESSAGE
RETURN
JCL to run the above REXX code
//DELETES EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DEL 'USERID.DED.OUT'
DEL 'USERID.TMO.OUT'
DEL 'USERID.RSC.OUT'
DEL 'USERID.UCM.OUT'
DEL 'USERID.ESC.OUT'
SET MAXCC=0
//*
//STEP01 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//IN DD DISP=SHR,DSN=File that holds the DB2 master started task’s JESMSGLG output
//DED DD DSN=USERID.DED.OUT,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=400,RECFM=FB),
// SPACE=(CYL,(10,10),RLSE)
//TMO DD DSN=USERID.TMO.OUT,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=400,RECFM=FB),
// SPACE=(CYL,(10,10),RLSE)
//UCM DD DSN=USERID.UCM.OUT,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=400,RECFM=FB),
// SPACE=(CYL,(10,10),RLSE)
//ESC DD DSN=USERID.ESC.OUT,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=400,RECFM=FB),
// SPACE=(CYL,(10,10),RLSE)
//RSC DD DSN=USERID.RSC.OUT,
// DISP=(,CATLG,DELETE),
// DCB=(LRECL=150,RECFM=FB),
// SPACE=(CYL,(10,10),RLSE)
//SYSTSIN DD *
EX 'USERID.REXX.LIB(DB2MSGFMT)'
/*
//*
//XMITIP EXEC PGM=IKJEFT1B,DYNAMNBR=50
//SYSEXEC DD DISP=SHR,DSN=xmitip.library
//DED DD DISP=SHR,DSN=USERID.DED.OUT
//TMO DD DISP=SHR,DSN=USERID.TMO.OUT
//RSC DD DISP=SHR,DSN=USERID.RSC.OUT
//UCM DD DISP=SHR,DSN=USERID.UCM.OUT
//ESC DD DISP=SHR,DSN=USERID.ESC.OUT
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
%xmitip xxx.xxx@zzz.com +
MSGT 'CSV files for DB2 log msgs' +
From xxx.xxx@zzz.com +
Subject 'csv files' +
FILEDD (DED TMO RSC UCM ESC) +
FILENAME (DED.CSV TMO.CSV RSC.CSV UCM.CSV ESC.CSV)
/*
