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

Installing DB2 failed on PowerHA

We’re installing DB2 9.7 on AIX 7.1 and run into the following error: installSAM: Cannot upgrade System Automation because this node is online Name OpState RSCTVersion spbcclr03 Online 3.1.4.4 installSAM: This node must be excluded from automation and then stopped before upgrade can proceed installSAM: Exclude this node from automation using command ‘samctrl -u a… Continue reading Installing DB2 failed on PowerHA