|
Problem IC84279
|
Status: Closed |
SQLDOBJTABINSERTRID, PROBE:1152 - DATABASE MARKED BAD OCCUR AFTER ATTACHING DATA PARTITIONS ON PARTITIONED TABLE. |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
As of v9.7, creating partitioned index on a target partitioned
table followed the ATTACHING DATA PARTITIONS/SET INTEGRITY tasks
may cause a logical corruption of object table (SQLD_BADPAGE)
which can incur the DB marked Bad.
The sample of reproducible steps would be like those:
db2 "create regular tablespace ts_data"
db2 "create regular tablespace ts_index"
db2 "create table t1 ( i1 int, i2 int ) partition by range
(i1)"\
"(part part0 starting(1) ending(1) in ts_data index in
ts_index)"
db2 "create index i1_t1 on t1 (i1) not partitioned"
db2 "create table t2 ( i1 int, i2 int ) in ts_data index in
ts_index"
db2 "create index i1_t2 on t2 (i1)"
db2 "alter table t1 attach partition part1 starting 2 ending 2
from t2"
db2 "set integrity for t1 immediate checked"
db2 "create index i2_t1 on t1 (i2) partitioned"
Below info will returned to the command line while creating the
partitioned index, which indicated the DB is marked Bad.
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1034C The database is damaged. All applications processing
the database
have been stopped. SQLSTATE=58031
A bunch of error messages like below will be found in the
db2diag.log:
2011-09-21-20.14.49.385868+480 I15804000A543 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, data management, sqldObjTabInsertRID,
probe:1152
RETCODE : ZRC=0x87040001=-2029780991=SQLD_BADPAGE "Bad Data
Page"
DIA8500C A data file error has occurred, record id is
"".
...skip...
2011-09-21-20.14.49.386238+480 I15805104A542 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging fu, probe:0
MESSAGE : PAGE OBJECT IDENTIFIERS:
DATA #1 : String, 54 bytes
Tablespace ID = 8, Object ID = 65535, Object Type = 0
...skip...
2011-09-21-20.14.49.387031+480 I15809183A497 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging fu, probe:0
MESSAGE : RID CONTENTS:
DATA #1 : String, 20 bytes
Page = 64, Slot = 7
An FODC_DBMarkedBad directory will be created in db2dump with
the following functions on the stack:
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000007A97D0 pthread_kill + 0xB0
0x0900000001F5EAD8 sqloDumpEDU + 0x54
0x0900000001DE0A34 MarkDBBad__16sqeLocalDatabaseFi + 0x340
0x0900000001DE0140 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
0x814
0x0900000001178AB8
sqldDumpBadPage__FP8sqeAgentP8SQLD_TCBP13SQLD_PAGESLOTP11SQLB_FI
X_CBUliT6
+ 0xAB4
0x09000000035CF4FC
@102@sqldObjTabInsertRID__FP13SQLD_DFM_WORKPUsP14SQLP_LOCK_INFO
+ 0x2B4
0x09000000035CEB90 sqldoisr__FP8sqeAgentUsPUsP15SQLD_OBJTAB_REC
+ 0x430
0x0900000000E037C4
@102@sqldCreateINX__FP8sqeAgentP8SQLD_TCBP17SQLD_CREATEINX_CB +
0x5BC
0x09000000020A28E4
sqldBeginIndexCreate__FP8sqeAgentP17SQLD_CREATEINX_CB + 0x1A1C
0x090000000240F96C
sqlrlCreateIndexPart__FP8sqlrr_cbP8sqlrg_idP17SQLD_CREATEINX_CBi
+ 0x78
0x09000000020AA194
sqlrlCreateLocalIndex__FP8sqlrr_cbP8sqlrg_pdP14sqlrg_partkeydP8s
qlrg_idP18sqlrg_datapartinfoP17SQLD_CREATEINX_CBibT8
+ 0x92C
0x0900000003936684
sqlrl_create_index__FP8sqlrr_cbPUcsT2T3T2T3T2T3P11sqlrg_xinfoT3U
lT2P16sqlrl_index_parmT3P22sqlrl_extendedIdx_infoT12_iT18_P18sql
rg_xmlIndexInfoP16SQLD_SCANINXINFO
+ 0x80
0x09000000017E29C0 sqlnq_create_index_end__FP9sqlnq_qtbiN22 +
0xC28
0x090000000186201C
sqlnq_create_index_stmt__FPP8stknode_i10actiontypePUcP3loc +
0x77C
0x0900000004D2421C sqlnp_smactn__FP8sqlnp_cbi + 0x34
0x0900000004D249E8 sqlnp_parser__FP8sqlnp_cb + 0x278
0x0900000004D761AC
sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0xBC
0x0900000004CF36EC
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_enviT7PP9sqlnq_qur
+ 0x9D8
0x0900000004CEF534
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_env
+ 0x40
0x0900000004BE8974
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
LOCK_INFOP16sqlra_cached_varPiPUl
+ 0x7C4
0x0900000004BE7824
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb
+ 0x648
0x0900000004EDF704 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x608
0x0900000004FC4DAC sqlri_ddl_get_section__FP8sqlrr_cb + 0xAC
0x0900000004FC4B08 sqlri_ddl_common__FP8sqlrr_cb + 0x4CC
0x0900000004FC45E4 sqlriddl__FP8sqlrr_cb + 0x3C
0x0900000004E21A84 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x24
0x0900000005012674 sqlrr_execute_immediate__FP8sqlrr_cbi + 0x53C
0x0900000005011FF4
sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0x25C
0x0900000005011CA0
sqljs_ddm_excsqlimm__FP14db2UCinterfaceP13sqljDDMObject + 0x88C
0x09000000050113A4
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface
+ 0x24
0x0900000004E5EF04
.sqljsParse.fdpr.clone.212__FP13sqljsDrdaAsCbP14db2UCinterfaceP8
sqeAgentb
+ 0x2BC
0x0900000004DB02C0 @64@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0xAE0
0x0900000004C52B54
@64@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA0
0x0900000000F293E8
@64@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x310
0x0900000004C5232C sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0xEC
0x0900000004CD6CD4 RunEDU__8sqeAgentFv + 0xB4
0x0900000004CD4320 EDUDriver__9sqzEDUObjFv + 0xDC
0x0900000004CD4204 sqlzRunEDU__FPcUi + 0x24
0x0900000004CDD4E4 sqloEDUEntry + 0x264
</StackTrace>
The problem happens because creating partitioned index is trying
to use an object ID that is already marked as used on that
related tablespace after attaching a source data partition to it
with set integrity.
Because when attaching a partition to a partitioned table, DB2
will drop any indexes on the source table that are not required
once the table is attached. Under such condition, if DB2 drop
all indexes on the source table, DB2 need to reset the index
object ID in the partition's packed descriptor to zero, however
DB2 were not doing this (So it come to light this new APAR). A
non-zero index object ID in the packed descriptor means that an
index object exists and one of DB2 internal function will use it
later when creating the table's first non-partitioned index. The
code in DB2 internal compontent sees that the index object does
not exist and goes to create the index object with this object
ID and when inserting into the tablespace's object table, this
causes an error and the database is brought down. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL USERS *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Try the localfix or upgrade to v101 FP1 *
**************************************************************** |
| Local Fix: |
1. If recreating target partitioned table is acceptable:
Export all the data from the target partitioned table, drop the
table and recreate the table, then import the data that was
previously exported.
Then use one of these two workaround to keep from continually
hitting the problem once the table is recreated.
a. Drop all indexes on source table that correspond to "NOT
PARTITIONED" indexes on target table prior to the ATTACH
operation. These indexes will be drop anyway by the ATTACH
operation.
b. Create at least one partitioned index on target table before
the attach.
2. If recreating target partitioned table is not acceptable:
It's required to detach all of the problematic partitions into
standalone tables.
To determine which partitions have the problem below diagnsotic
data is needed as following:
a. db2cat output from the table, which can be gather using the
following command:
db2cat -d BCSSDB -s 'SCHEMA_NAME' -n 'TABLE_NAME' -o
db2cat.out
b. output from the following query:
db2 "select DATAPARTITIONID, DATAPARTITIONNAME from
SYSCAT.DATAPARTITIONS where TABSCHEMA='SCHEMA_NAME' and
TABNAME='TABLE_NAME'"
Then contact the IBM Support Service. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
As of v9.7, creating partitioned index on a target partitioned
table followed the ATTACHING DATA PARTITIONS/SET INTEGRITY tasks
may cause a logical corruption of object table (SQLD_BADPAGE)
which can incur the DB marked Bad.
The sample of reproducible steps would be like those:
db2 "create regular tablespace ts_data"
db2 "create regular tablespace ts_index"
db2 "create table t1 ( i1 int, i2 int ) partition by range
(i1)"\
"(part part0 starting(1) ending(1) in ts_data index in
ts_index)"
db2 "create index i1_t1 on t1 (i1) not partitioned"
db2 "create table t2 ( i1 int, i2 int ) in ts_data index in
ts_index"
db2 "create index i1_t2 on t2 (i1)"
db2 "alter table t1 attach partition part1 starting 2 ending 2
from t2"
db2 "set integrity for t1 immediate checked"
db2 "create index i2_t1 on t1 (i2) partitioned"
Below info will returned to the command line while creating the
partitioned index, which indicated the DB is marked Bad.
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command. During SQL
processing it
returned: SQL1034C The database is damaged. All applications
processing
the database have been stopped. SQLSTATE=58031
A bunch of error messages like below will be found in the
db2diag.log:
2011-09-21-20.14.49.385868+480 I15804000A543 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, data management, sqldObjTabInsertRID,
probe:1152
RETCODE : ZRC=0x87040001=-2029780991=SQLD_BADPAGE "Bad Data
Page"
DIA8500C A data file error has occurred, record id is
"".
...skip...
2011-09-21-20.14.49.386238+480 I15805104A542 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging fu, probe:0
MESSAGE : PAGE OBJECT IDENTIFIERS:
DATA #1 : String, 54 bytes
Tablespace ID = 8, Object ID = 65535, Object Type = 0
...skip...
2011-09-21-20.14.49.387031+480 I15809183A497 LEVEL: Severe
PID : 1246572 TID : 98974 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-30640 APPID:
*LOCAL.db2inst1.110921121149
AUTHID : DB2INST1
EDUID : 98974 EDUNAME: db2agent (BCSSDB) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging fu, probe:0
MESSAGE : RID CONTENTS:
DATA #1 : String, 20 bytes
Page = 64, Slot = 7
An FODC_DBMarkedBad directory will be created in db2dump with
the following functions on the stack:
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000007A97D0 pthread_kill + 0xB0
0x0900000001F5EAD8 sqloDumpEDU + 0x54
0x0900000001DE0A34 MarkDBBad__16sqeLocalDatabaseFi + 0x340
0x0900000001DE0140 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
0x814
0x0900000001178AB8
sqldDumpBadPage__FP8sqeAgentP8SQLD_TCBP13SQLD_PAGESLOTP11SQLB_FI
X_CBUliT6
+ 0xAB4
0x09000000035CF4FC
@102@sqldObjTabInsertRID__FP13SQLD_DFM_WORKPUsP14SQLP_LOCK_INFO
+ 0x2B4
0x09000000035CEB90 sqldoisr__FP8sqeAgentUsPUsP15SQLD_OBJTAB_REC
+ 0x430
0x0900000000E037C4
@102@sqldCreateINX__FP8sqeAgentP8SQLD_TCBP17SQLD_CREATEINX_CB +
0x5BC
0x09000000020A28E4
sqldBeginIndexCreate__FP8sqeAgentP17SQLD_CREATEINX_CB + 0x1A1C
0x090000000240F96C
sqlrlCreateIndexPart__FP8sqlrr_cbP8sqlrg_idP17SQLD_CREATEINX_CBi
+ 0x78
0x09000000020AA194
sqlrlCreateLocalIndex__FP8sqlrr_cbP8sqlrg_pdP14sqlrg_partkeydP8s
qlrg_idP18sqlrg_datapartinfoP17SQLD_CREATEINX_CBibT8
+ 0x92C
0x0900000003936684
sqlrl_create_index__FP8sqlrr_cbPUcsT2T3T2T3T2T3P11sqlrg_xinfoT3U
lT2P16sqlrl_index_parmT3P22sqlrl_extendedIdx_infoT12_iT18_P18sql
rg_xmlIndexInfoP16SQLD_SCANINXINFO
+ 0x80
0x09000000017E29C0 sqlnq_create_index_end__FP9sqlnq_qtbiN22 +
0xC28
0x090000000186201C
sqlnq_create_index_stmt__FPP8stknode_i10actiontypePUcP3loc +
0x77C
0x0900000004D2421C sqlnp_smactn__FP8sqlnp_cbi + 0x34
0x0900000004D249E8 sqlnp_parser__FP8sqlnp_cb + 0x278
0x0900000004D761AC
sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0xBC
0x0900000004CF36EC
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_enviT7PP9sqlnq_qur
+ 0x9D8
0x0900000004CEF534
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_env
+ 0x40
0x0900000004BE8974
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
LOCK_INFOP16sqlra_cached_varPiPUl
+ 0x7C4
0x0900000004BE7824
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb
+ 0x648
0x0900000004EDF704 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x608
0x0900000004FC4DAC sqlri_ddl_get_section__FP8sqlrr_cb + 0xAC
0x0900000004FC4B08 sqlri_ddl_common__FP8sqlrr_cb + 0x4CC
0x0900000004FC45E4 sqlriddl__FP8sqlrr_cb + 0x3C
0x0900000004E21A84 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x24
0x0900000005012674 sqlrr_execute_immediate__FP8sqlrr_cbi + 0x53C
0x0900000005011FF4
sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0x25C
0x0900000005011CA0
sqljs_ddm_excsqlimm__FP14db2UCinterfaceP13sqljDDMObject + 0x88C
0x09000000050113A4
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface
+ 0x24
0x0900000004E5EF04
.sqljsParse.fdpr.clone.212__FP13sqljsDrdaAsCbP14db2UCinterfaceP8
sqeAgentb
+ 0x2BC
0x0900000004DB02C0 @64@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0xAE0
0x0900000004C52B54
@64@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA0
0x0900000000F293E8
@64@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x310
0x0900000004C5232C sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0xEC
0x0900000004CD6CD4 RunEDU__8sqeAgentFv + 0xB4
0x0900000004CD4320 EDUDriver__9sqzEDUObjFv + 0xDC
0x0900000004CD4204 sqlzRunEDU__FPcUi + 0x24
0x0900000004CDD4E4 sqloEDUEntry + 0x264
</StackTrace>
The problem happens because creating partitioned index is trying
to use an object ID that is already marked as used on that
related tablespace after attaching a source data partition to it
with set integrity.
Because when attaching a partition to a partitioned table, DB2
will drop any indexes on the source table that are not required
once the table is attached. Under such condition, if DB2 drop
all indexes on the source table, DB2 need to reset the index
object ID in the partition's packed descriptor to zero, however
DB2 were not doing this (So it come to light this new APAR). A
non-zero index object ID in the packed descriptor means that an
index object exists and one of DB2 internal function will use it
later when creating the table's first non-partitioned index. The
code in DB2 internal compontent sees that the index object does
not exist and goes to create the index object with this object
ID and when inserting into the tablespace's object table, this
causes an error and the database is brought down. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 13.06.2012
01.11.2012
01.11.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.8.974
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.1
|
 |
| 10.5.0.1
|
|
