Troubleshooting
Problem
After a table is converted to ITC you may sometimes start to receive SQL0901N and many FODC dump files for an index reorg on that table.
Symptom
SQL0901N alongwith FODC files in db2dump directory
Cause
A corrupted block in the index may cause this problem. Other causes are unknown.
Diagnosing The Problem
When an auto reorg runs on an index of ITC table you may see the
following messages in db2diag.log :
========================================================================
=
2016-10-29-14.04.40.766887+480 I18211938A470 LEVEL: Severe
PID : 28196 TID : 5512 PROC : db2sysc 0
INSTANCE: db2x NODE : 000 DB : x
APPHDL : 0-60357 APPID: *LOCAL.db2x.xxxxxxx
AUTHID : DB2x HOSTNAME: db01
EDUID : 5512 EDUNAME: db2agent (x) 0
FUNCTION: DB2 UDB, data management, sqldOnlineTableReorg, probe:95
MESSAGE : Table: SCHEMA.TABLE
...
2016-11-01-16.20.39.266496+480 I19525590A549 LEVEL: Severe
PID : 28196 TID : 1416 PROC : db2sysc 0
INSTANCE: db2x NODE : 000 DB : x
APPHDL : 0-32837 APPID: *LOCAL.db2x.xxxxxxx
AUTHID : DB2x HOSTNAME: db01
EDUID : 1416 EDUNAME: db2agent (x) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging function), probe:30
MESSAGE : SQLD_TCB:
DATA #1 : String, 34 bytes
Perm Table(12:2562)=SCHEMA.TABLE
...
2016-11-01-16.54.30.278254+480 I19622578A470 LEVEL: Severe
PID : 28196 TID : 1416 PROC : db2sysc 0
INSTANCE: db2x NODE : 000 DB : x
APPHDL : 0-32837 APPID: *LOCAL.db2x.161101055917
AUTHID : DB2x HOSTNAME: db01
EDUID : 1416 EDUNAME: db2agent (x) 0
FUNCTION: DB2 UDB, data management, sqldOnlineTableReorg, probe:95
MESSAGE : Table: SAPx.TST03
2016-11-01-16.54.30.278423+480 I19623049A882 LEVEL: Severe
PID : 28196 TID : 1416 PROC : db2sysc 0
INSTANCE: db2x NODE : 000 DB : x
APPHDL : 0-32837 APPID: *LOCAL.db2x.xxxxxx
AUTHID : DB2x HOSTNAME: db01
EDUID : 1416 EDUNAME: db2agent (x) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging
function), probe:30
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLDOLRT
sqlerrd : (1) 0x82090001 (2) 0x00000001 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
...
2016-11-01-16.54.30.707489+480 I19628194A397 LEVEL: Event
PID : 28272 TID : 69784 PROC : db2acd 0
INSTANCE: db2x NODE : 000
HOSTNAME: db01
EDUID : 69784 EDUNAME: db2acd 0
FUNCTION: DB2 UDB, Health Monitor, db2AutoReorgExec, probe:10
STOP : Automatic reorg has completed with errors on table SCHEMA.TABLE
========================================================================
Along with above messages, we also get FODC dumps.
Checking the stack trace we see :
<StackTrace>
DumpStackTraceInternal
DumpStackTraceV98
OSSTrapFile4dump
sqlo_trce
sqloDumpDiagInfoHandler
---- Signal 17 (SIGUSR2) delivered ----
_lwp_kill
pthread_kill
sqloDumpEDU
sqlischa
sqliaddk
sqldbCheckBlockForSparseOLR
sqldbBuildSparseBlockList
sqldOLRInitialize
sqldOnlineTableReorg
sqlrreorg_table
sqlrreorg_sendinfo
sqlrr_reorg
sqlerKnownProcedure
sqlerCall
sqljs_ddm_excsqlstt
Resolving The Problem
Turn off Automatic Reorg and run the following reorg statements manually:
db2 "reorg indexes all for table SCHEMA.TABLE cleanup all"
db2 "reorg indexes all for table SCHEMA.TABLE rebuild"
db2 "reorg indexes all for table SCHEMA.TABLE reclaim extents"
Then, turn on Automatic reorg again. The problem should not repeat.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21995445