GSE IMSDB2

1
GSE IMS/DB2

• Detection of long LUW

March 13th 2003 Author Philippe De Backer
Toyota Motor
Marketing Europe
2
Operational Environment overview

• System configuration
• OS/390 V2.10 Parallel Sysplex 2 Production LPAR
• DB2 Version 6 - Data sharing
• Cics TS 1.3
• MQ/Series 2.1
• TSO Batch
• Dynamic SQL via reporting tools / MS Tools
• CAF connections

• Application configuration
• User defined XCOMMIT/XRESTART subroutines
• Commit frequency controlled via XCMTINTV DB2
  Table
• Restart information/user data stored in XRESTART
  DB2 Table

3
COMA.XCMTINTV_TBL (1/32) PROGRAM_ID
COMMIT_INTERVAL TIME_FROM
TIME_TO DATE_CREATED 000001 NPMIP011
10. 01.01.0001 000013
....INTV 50.
01.01.0001 000014 ....INTV 15.
0800 2200 01.01.0001
XRESTART INITIAL/ RESTART/COMMIT PARM ?
RESTART ? GOBACK WITH USER DATA FROM XRESTART
TBL INITIAL ? INIT ROW IN XRESTART TBL AND TAKE
COMMIT COMMIT ? PUT DATA IN XRESTART TBL AND
TAKE COMMIT GOBACK
Application PGM - - call XRESTART - - - - - call
XCOMMIT - -
XCOMMIT GET ROW FOR PGM-NAME IN XCMTINTV
KEEP CURRENT TIMESTAMP IN VARIABLE IF ROW
DOESNT EXISTS TAKE DEFAULT VALUE TIME
INTERVAL VALUE REACHED SINCE LAST COMMIT ? YES
GOBACK commitY NO GOBACK commitN
4
Scope

• What is the purpose
• Detect Long UOW
• Detect programs not doing commit
• Detect logical loop in program
• Avoid never ending rollback
• Avoid the potential need of an archive tape
  during rollback
• Adapt commit frequency based upon the behaviour
  of a program

• Available tools and eye-catcher
• DSNR035I DB2 message
• LOGLOAD value in ZPARM
• CA/Ops to trap messages and take actions based
  upon thresholds
• SMTP to send warning mail and ALARM Manager

5
DSNR035I _DB1P DSNRPBCW WARNING - UNCOMMITTED UR
493 AFTER 8 CHECKPOINTS - CORRELATION
NAME APDD50 CONNECTION ID DB2CALL
LUWID TMMENET.A51DB1P.B901593F38D0
103162 PLAN NAME DPPP
AUTHID OPCC END USER ID
TRANSACTION NAME WORKSTATION NAME

• DB2 CHECKPOINT frequency depending on
• LOGLOAD value in ZPARM
• Global activity

• CA/OPS
• Traps the DSNR035I message
• Activation of the associated RULE

• DSNR035I RULE Will
• Check the message text
• Pick-up of checkpoint, correlation id,
  connection ID, DB2 SSID
• Read CA/OPS variable containing thresholds
• Take ACTION(S)

6
Possible Immediate Actions

• Use TSO SEND command to warn DBA if problem
  occurs in DEV DB2
• Send MAILS to operation team at warning level
• Raise an ALARM at cancel level for PROD thread
• Cancel the thread at cancel level (NB cancel
  command depends on the connection type Batch /
  TSO User / DDF / Cics transaction)
• Create a LOG record describing the event in user
  LOG FILE

Post actions

• Increase LOGLOAD value
• Decrease COMMIT interval for a particular program
• Check LOG FILE
• Check the general DB2 PROBLEMS file
  (timeout,deadlock,lock escl)
• Check application logic

7

• LOG FILE
• Updated on line at event time
• Logging of warning event
• Action log

8

• GLOBAL DB2 PROBLEM LOG FILE
• Updated by reading the MVS SYSLOG FILE of
  YESTERDAY
• Concurrent thread are identified
• Visible by application team (no excuse guys .)

9

• What's NEXT
• THRESHOLD must be linked to LOGCOPY activity
  (avoid LOG SWITCH during UOW)
• Wait DB2 V7 and new DSNR035I message based on
  of LOG records written by in flight UR (not
  dependent of global activity)
• SET LOG CHKTIME iso LOGLOAD (V7)
• How to link a Cics corrid to the TASK ID to be
  able to automate PURGE command
• CANCEL THREAD NOBACKOUT ! (V7)