Caused by: financing.tools.hub.shared.exception.R2FException: com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, 
DRIVER=4.15.146

# db2 get dbm cfg | grep -i lock

   Lock                                   (DFT_MON_LOCK) = OFF

 Max storage for lock list (4KB)              (LOCKLIST) = 12000

 Percent. of lock lists per application       (MAXLOCKS) = 80

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000

 Lock timeout (sec)                        (LOCKTIMEOUT) = 600

 Block log on disk full                (BLK_LOG_DSK_FUL) = NO

 Block non logged operations            (BLOCKNONLOGGED) = NO

 Lock timeout events                   (MON_LOCKTIMEOUT) = NONE

 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST

 Lock wait events                         (MON_LOCKWAIT) = NONE

 Lock wait event threshold               (MON_LW_THRESH) = 4294967295

 Lock event notification level         (MON_LCK_MSG_LVL) = 1

SET EVENT MONITOR DB2DETAILDEADLOCK state 0

DROP EVENT MONITOR DB2DETAILDEADLOCK

db2 "CREATE BUFFERPOOL BP32K_LOCK  IMMEDIATE SIZE 2500 AUTOMATIC PAGESIZE 32K"

db2 "CREATE LARGE TABLESPACE TSTLOCK PAGESIZE 32K MANAGED BY DATABASE

 USING (FILE '/db/a3inr2f/db2data/r2fapp/a3inr2f/NODE0000/tstlock' 100M)

 AUTORESIZE yes

 BUFFERPOOL BP32K_LOCK dropped table recovery on"

db2 "CREATE EVENT MONITOR locktimeoutevm  for locking  write to unformatted event table (table AUTOMON.TLOCKS in TSTLOCK) MANUALSTART "

db2 "set event monitor locktimeoutevm state=1"

db2 "CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS32K PAGESIZE 32 K BUFFERPOOL BP32K_LOCK"

db2 "select VARCHAR(evmonname,30) as evmonname, EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"

update db cfg for <dbname> using MON_LOCKTIMEOUT HIST_AND_VALUES immediate

db2 "call EVMON_FORMAT_UE_TO_TABLES ('LOCKING', NULL, NULL, NULL, 'AUTOMON ', NULL, NULL, -1, 'SELECT * FROM AUTOMON.TLOCKS ORDER BY event_timestamp')"

db2 "call EVMON_FORMAT_UE_TO_TABLES ('LOCKING', NULL, NULL, NULL, 'AUTOMON ', NULL, 'PRUNE_UE_TABLE', -1, 'SELECT * FROM AUTOMON.TLOCKS ORDER BY event_timestamp')"

db2 "call EVMON_FORMAT_UE_TO_TABLES ('LOCKING', NULL, NULL, NULL, 'AUTOMON ', NULL, 'recreate_force', -1, 'SELECT * FROM AUTOMON.TLOCKS ORDER BY event_timestamp')"

LOCK_ACTIVITY_VALUES

LOCK_EVENT

LOCK_PARTICIPANTS

LOCK_PARTICIPANT_ACTIVITIES

List all locking event:

-----------------------------------------------------------------------

db2 "select event_id

    , substr(event_type,1,18) as event_type

    , event_timestamp, dl_conns

    , rolled_back_participant_no

from db2inst1.LOCK_EVENT

order by event_id

    , event_timestamp

with ur"

To summarize counts according to event_type:

-----------------------------------------------------------------------

db2 "select substr(event_type,1,18) as event_type

    , count(*) as count

    , sum(dl_conns) sum_involved_connections

from db2inst1.LOCK_EVENT

group by event_type

with ur"

To summarize counts according to day:

-----------------------------------------------------------------------

db2 "select substr(event_type,1,18) as event_type , year(event_timestamp) as year , month(event_timestamp) as month , day(event_timestamp) as day , hour(event_timestamp) as hour ,

count(*) as count

from db2inst1.LOCK_EVENT

group by year(event_timestamp) , month(event_timestamp) , day(event_timestamp) , hour(event_timestamp) , event_type

order by year(event_timestamp) , month(event_timestamp) , day(event_timestamp) , hour(event_timestamp) , event_type with ur" 

To summarize counts according to table:

db2 "select substr(lp.table_schema,1,18) as table_schema , substr(lp.table_name,1,30) as table_name , substr(le.event_type,1,18) as lock_event , count(*)/2 as count

 from db2inst1.LOCK_PARTICIPANTS lp, db2inst1.LOCK_EVENT le where lp.xmlid=le.xmlid

group by lp.table_schema, lp.table_name, le.event_type order by lp.table_schema, lp.table_name, le.event_type with ur" 

To summarize counts according to statement:

db2 "with t1 as ( select STMT_PKGCACHE_ID as STMT_PKGCACHE_ID , count(*) as stmt_count

from db2inst1.lock_participant_activities where activity_type='current' group by STMT_PKGCACHE_ID)

select t1.stmt_count , (select substr(STMT_TEXT,1,100) as stmt_text from db2inst1.lock_participant_activities a1 where a1.STMT_PKGCACHE_ID=t1.STMT_PKGCACHE_ID fetch first 1 row only)

 from t1 order by t1.stmt_count desc with ur"