Customer said, they got the following Errors in applications logs
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
I checked the db2diag.log, there is no any logs related with the error. then I check the dbm cfg
Diagnostic error capture level (DIAGLEVEL) = 3
DBM configure Parameter
# db2 get dbm cfg | grep -i lock Lock (DFT_MON_LOCK) = OFF
DB configure Parameter
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
I advice customer to use event monitor to monitor the database.
Step 1: check event and drop the not useful event
SET EVENT MONITOR DB2DETAILDEADLOCK state 0 DROP EVENT MONITOR DB2DETAILDEADLOCK
Keep in mind that monitor events are a heap_size consuming memory. So verify that mon_heap_sz is set correctly (e.g. automatic )
Step 2: create an tablespace used by event monitor
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"
Step 3: verify the creation of event monitor
db2 "select VARCHAR(evmonname,30) as evmonname, EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors"
Step 4: Now we have the monitor for locking, but we need to setup some parameters:
— Set up the MON_LOCKTIMEOUT
— By default, this parameter is set to NONE. Possible values include:
—NONE no data is collected on lock timeouts (DEFAULT)
—WITHOUT_HIST data about lock timeout events is sent to any active event monitor tracking locking events
—HISTORY the last 250 activities performed in the same
UOW are tracked by event monitors tracking locking events, in addition
to the data about lock timeout events.
—HIST_AND_VALUES In addition to the the last 250
activities perfromed in the same UOW and the data about lock timeout
events, values that are not long or xml data are also sent to any active
event monitor tracking locking events
step 5: Open the monitor
update db cfg for <dbname> using MON_LOCKTIMEOUT HIST_AND_VALUES immediate
step 6: 解析日志
方法一：
format the unformatted event to the table we have create in precedence
db2 "call EVMON_FORMAT_UE_TO_TABLES ('LOCKING', NULL, NULL, NULL, 'AUTOMON ', NULL, NULL, -1, 'SELECT * FROM AUTOMON.TLOCKS ORDER BY event_timestamp')"
extract and delete the data from the UE table
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')"
if you run the procedure "EVMON_FORMAT_UE_TO_TABLES", DB will create the following tables
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"
方法二：
a. locate to $HOME/sqllib/samples/java/jdbc
b. compile the source code : $HOME/sqllib/java/jdk64/bin/javac db2evmonfmt.java
c. run the tool : $HOME/sqllib/java/jdk64/bin/java db2evmonfmt -d <dbname>
-ue <unformatted evmon table name> -ftext -hours 1 -type LOCKTIMEOUT
(For db2jcct error, export LIBPATH=$HOME/sqllib/lib64:$LIBPATH)
Collecting data: DB2 lock timeouts