AIOps

by Debra Shepard

Is your DB2 Agent crashing when accessing the  Archive Log History in the workspace?

The data request in the workspace for the Archive Log History needs to run a sorted sql query on the DB2 archive database. If there is no temporary tablespace with a suitable page size, then the DB2 agent fails to get data and crashes.

You may see the following symptoms:

1. The end of DB2 agent logs looks like below:

(50D16252.5C6F-6:kudlragt.cpp,572,"GetLogRecordInfo") About the execute query:'SELECT EID,SEQNUM,START_TIME,END_TIME,FIRSTLOG,LASTLOG,BACKUP_ID,OPERATION,OPERATIONTYPE,OBJECTTYPE,LOCATION,DEVICETYPE,ENTRY_STATUS FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='X' ORDER BY EID DESC FETCH FIRST 15 ROW ONLY'
(50D16252.5C70-6:globalsql.cpp,380,"ExecuteStatementMultiRow") Entry
(50D16252.5C71-6:globalsql.cpp,399,"ExecuteStatementMultiRow") sqlhandle: Allocated hStmt 10001 for _hDbc 1(50D16252.5C72-6:globalsql.cpp,433,"ExecuteStatementMultiRow") ERROR returned from kud00_DB2LIB_Lib::pMyDB2LIB_SQLExecDirect(): rc = -1; _hDbc 1; SQL Stmt 'SELECT EID,SEQNUM,START_TIME,END_TIME,FIRSTLOG,LASTLOG,BACKUP_ID,OPERATION,OPERATIONTYPE,OBJECTTYPE,LOCATION,DEVICETYPE,ENTRY_STATUS FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='X' ORDER BY EID DESC FETCH FIRST 15 ROW ONLY'
(50D16252.5C73-6:globalsql.cpp,444,"ExecuteStatementMultiRow") Exit
(50D16252.5C74-6:kudlragt.cpp,692,"GetLogRecordInfo") rc from executing statement:[-1]
(50D16252.5C75-6:globalsql.cpp,235,"cleanupConnection") Entry
(50D16252.5C76-6:globalsql.cpp,241,"cleanupConnection") sqlconnect: disconnecting _hDbc 1
(50D16252.5C77-6:globalsql.cpp,249,"cleanupConnection") sqlhandle: Freeing db handle: _hDbc 1
(50D16252.5C78-6:globalsql.cpp,257,"cleanupConnection") sqlhandle: Freeing env handle: _hEnv 1
(50D16252.5C79-6:globalsql.cpp,262,"cleanupConnection") Exit(50D16252.5C7A-6:globalsql.cpp,235,"cleanupConnection") Entry
(50D16252.5C7B-6:globalsql.cpp,262,"cleanupConnection") Exit
(50D16252.5C7C-6:kudlragt.cpp,720,"GetLogRecordInfo") Exit
(50D16252.5C7D-6:kudlragt.cpp,249,"TakeSample") count=[15]
(50D16252.5C7E-6:kudlragt.cpp,253,"TakeSample") Allocating pKralogrec - before ptr 0
 

2. The core stack information looks like below:

Program terminated with signal 11, Segmentation fault.
#0  0x0000020000fd14ba in memcpy () from /lib64/libc.so.6
(gdb) where
#0  0x0000020000fd14ba in memcpy () from /lib64/libc.so.6
#1  0x0000000080223130 in kud00_kudlogrec_agent::TakeSample() ()
#2  0x00000200002c8fc0 in ctira::DriveDataCollection() () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#3  0x00000200002d4af4 in TableManager::checkForExpiredRequests(long) () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#4  0x00000200002d52c8 in TableManager::timeout(CTRA_Timerspec_*) () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#5  0x000002000027264a in CTRA_timer_base::TimerCallbackHandler() () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#6  0x0000020000270984 in Handler_base(void*) () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#7  0x0000020000274378 in CTRA_timer_task(void*) () from /products/IBM/ITM/tmaitm6/ls3266/lib/libkra.so
#8  0x00000200000640e6 in start_thread () from /lib64/libpthread.so.0
#9  0x00000200010283ea in thread_start () from /lib64/libc.so.6

You can diagnose the problem by doing the following.

Execute the corresponding query on the archive db by using this command:

[db2inst1@v525400b1c26a logs]$ db2 connect to sample
 
   Database Connection Information
 
 Database server        = DB2/LINUX 10.1.1
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
 
[db2inst1@v525400b1c26a logs]$ db2 "SELECT EID,SEQNUM,START_TIME,END_TIME,FIRSTLOG,LASTLOG,BACKUP_ID,OPERATION,OPERATIONTYPE,OBJECTTYPE,LOCATION,DEVICETYPE,ENTRY_STATUS FROM SYSIBMADM.DB_HISTORY WHERE OPERATION='X' ORDER BY EID DESC FETCH FIRST 12 ROW ONLY"
SQL1585N  A temporary table could not be created because there is no available
system temporary table space that has a compatible page size.  SQLSTATE=54048
[db2inst1@v525400b1c26a logs]$ db2 list tablespaces show detail
...
 Tablespace ID                        = 1
 Name                                     = TEMPSPACE1
 Type                                       = System managed space
 Contents                                = System Temporary data
 State                                      = 0x0000
   Detailed explanation:
     Normal
 Total pages                            = 1
 Useable pages                      = 1
 Used pages                           = 1
 Free pages                            = Not applicable
 High water mark (pages)       = Not applicable
 Page size (bytes)                   = 8192
 Extent size (pages)                = 32
 Prefetch size (pages)             = 32
 Number of containers             = 1

The page size in DB2 could be 4KB, 8KB, 16KB, 32KB and 64KB.  You can resolve the issue by first  choosing a page size (i.e, 16 KB) and then creating a temporary tablespace (and a corresponding buffer pool) with that page size. If you are unsure what size you will need, just create one of each page size (4/8/16/32/64 KB) that you do not currently have. Then try to run the above select query again to check. If it passes, then The DB2 agent will work fine in the workspace Archive Log History as well.

Additional ITM Agent Insights series of IBM Tivoli Monitoring Agent blogs are indexed under ITM Agent Insights: Introduction