System Slowness and DEADLOCKS: DB/2

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The Automation Engine shows performance degradation and review of the logs shows DEADLOCK entries similar to:

20140821/062732.543 - U0003592 UCUDB - Status: '40001' Native error: '-911' Msg: ' SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001'
20140821/062733.543 - U0000006 DEADLOCK

Environment

Release: ANY
Component: Automic Workload Automation

Database: DB2

Cause

This is often caused by misconfiguration of the DB/2 Database for the Automation Engine.

Resolution

Confirm the following DB settings via get db cfg:

• DLCHKTIME to 1000
  This parameter controls the database's deadlock time. The predefined default value is 10 seconds, which negatively impacts the system's performance (1000 corresponds to 1 second).
• LOCKLIST to 10240
• LOGBUFSZ to 2048
• APPLHEAPSZ at least to 2048
• APP_CTL_HEAP_SZ to 1024
• PCKCACHESZ to 256
• CATALOGCACHE_SZ to 128
• AUTO_RUNSTATS to OFF. Manually execute the RUNSTATS when the database has regularly been in use for some time and all tables contain data records. DB2 selects incorrect access channels if RUNSTATS are processed on empty tables. Deadlock situations can occur which could eventually result in a complete system standstill. The file upd_stat.sql. is provided in the folder IMAGE:\db\db2\UC4 version. It contains the relevant statements for the manual RUNSTAT execution. Replace UC4DB with the appropriate user name before executing the statements.
• DB2_EVALUNCOMMITED to YES
• DB2_SKIPINSERTED to ON
• DB2_SKIPDELETED to ON

If all of these settings are correct, please set TCP/IP=2 and DB=3 trace on the WP's during a period of slowness and send the logs into support with confirmation of the above settings.