DB2 - Problembeschreibung
Problem IC84279 | Status: Geschlossen |
SQLDOBJTABINSERTRID, PROBE:1152 - DATABASE MARKED BAD OCCUR AFTER ATTACHING DATA PARTITIONS ON PARTITIONED TABLE. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * 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. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows | |
Lösung | |
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 | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 13.06.2012 01.11.2012 01.11.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7., 9.8.974 | |
Problem behoben lt. FixList in der Version | |
10.1.0.1 | |
10.5.0.1 |