Diagnosing DB2 hot page issues

Abstract ________________________________________ Certain types of queries perform worse under increasing user load, due to hash bucket group latch contention caused by hot pages. Symptom ________________________________________ db2pd -latches shows excessive waiters on SQLB_HASH_BUCKET_GROUP_HEADER_groupLatch (“grouplatch”). db2pd -stacks shows excessive EDU waits in latch conflict routines (getConflictComplex) arising from bufferpool routines (sqlbfix, sqlbufix). Cause ________________________________________ There are numerous… Continue reading Diagnosing DB2 hot page issues

Why doesn’t SELECT release IS lock in DB2

I came across this question when analyzing the following application snapshot: Application Snapshot Application handle = 56710 Application status = UOW Waiting <<<<<<<<<<< UOW Waiting means DB2 server is waiting for application submit the next statement, does NOT indicate the current transaction has committed. Status change time = 02/26/2017 12:30:01.381390 Application code page = 1208… Continue reading Why doesn’t SELECT release IS lock in DB2

Slow query in DB2 due to Cartesian

Recently one developer came to me complaining poor sql performance and here is how I investigated the issue. Our environment: AIX 7.1 DB2 9.7.0.7 db2top -d dbname ConnTime..: 15:10:01.316 UOW Start.: 15:10:01.322 Appl name.: db2bp DB2 user..: CAIJI OS user…: caiji Agent id..: 62181 Coord DBP.: 0 Coord id..: 46105 Client pid: 26149284 Hash joins:… Continue reading Slow query in DB2 due to Cartesian

step-by-step guide on configuring db2 hadr replication

Source: 192.168.91.59 Destination: 192.168.91.11 DB name: CMSDB DB2 version: 9.7.0.7 # as db2inst1 on primary, make sure LOGARCHMETH1 is set db2 get db cfg for cmsdb | grep LOGARCHMETH1 # Setting up HADR cfg parameters on Primary database. db2 update db cfg for CMSDB using HADR_LOCAL_HOST 192.168.91.59 db2 update db cfg for CMSDB using HADR_LOCAL_SVC… Continue reading step-by-step guide on configuring db2 hadr replication

Resolving Apply ASN1001E in DB2 SQL Replication

Error Message in Apply: 2016-09-08-10.51.37.843021 ASN1001E APPLY “YHDBSUB0” : “WorkerThread”. The Apply program encountered an SQL error. The ERRCODE is “B20101”. The SQLSTATE is “42601”. The SQLCODE is “-104”. The SQLERRM is “WHERE?BEGIN-OF-STATEMENT?<variable_set>”. The SQLERRP is “SQLNP01F”. The server name is “”. The table name is “CAS.FX_ACCNT_SERIAL_JPY”. Usually this error is seen if you have… Continue reading Resolving Apply ASN1001E in DB2 SQL Replication

Identifying multiple unused indexes in DB2 automatically

db2pd utility could be used to identify unused indexes in db2. Nevertheless, when the amount of unused index is significant, manually querying index information could be tedious. Here is how to retrieve unused index automatically. # retrieve db2pd tcbstats information db2pd -d cmsdb -full -tcbstats all file=/tmp/db2pd_tcb.txt # generate tables with index and not used… Continue reading Identifying multiple unused indexes in DB2 automatically

Upgrade DB2 ESE from 9.7 to 10.5

# v10.5fp7_aix64_server_t.tar 10.5.0.7 media # as user db2inst1, make sure no invalid objects exist db2 connect to shinpbdb db2 connect to shibordb db2 connect to shinetdb db2 “SELECT SUBSTR(OBJECTSCHEMA,1,10) OBJECTSCHEMA, SUBSTR(OBJECTNAME,1,30) OBJECTNAME, SUBSTR(ROUTINENAME, 1,30) ROUTINENAME, OBJECTTYPE, INVALIDATE_TIME, LAST_REGEN_TIME FROM SYSCAT.INVALIDOBJECTS” db2 “CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)” # as user root cd /install tar -xvf v10.5fp7_aix64_server_t.tar cd… Continue reading Upgrade DB2 ESE from 9.7 to 10.5