System Slowness and DEADLOCKS: DB/2

Document ID : KB000089145
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
System Slowness and DEADLOCKS: DB/2
Resolution:

Symptoms

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


Cause

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


Resolution

Confirm the following DB settings via get dbm 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 ON
    • 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.