|
Problem IT28784
|
Status: Geschlossen |
RESTORING A LOADCOPY WHEN THERE IS A MODIFICATION STATE INDEX ONA COLUMN ORGANIZED TABLE MAY CORRUPT THE INDEX.
|
| Produkt: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problembeschreibung: |
The problem may occur if you have a
column organized table which has a modification state index and
done a LOAD with COPY YES and INDEXING MODE REBUILD and
done a rollforward that restored such a load copy.
NOTE: INDEXING MODE REBUILD may be chosen even if you did not
specify it. Check for the message SQL3213I The indexing mode is
"REBUILD".
A modification state index is created on a column organized
table when create index is done.
It is also created if the registry variable
DB2_EXTEND_COL_UNIQUE_INDEX_ACCESS set to ON (the default is
currently OFF) and a unique or primary key constraint is added.
To check which tables have a modification state index, do:
db2 "select tabname, tabschema, indname, index_objectid,
tbspaceid from syscat.indexes where indextype = 'MDST'"
If load in rebuild mode with the copy yes option is being used
for a table that has a modification state index, it is
recommended to take a new backup and apply the fix. Avoid
rolling forward thru such a load copy created without this fix.
There are a number of symptoms resulting from this problem after
or during rollforward through a loadcopy for a column organized
table that has a modification state index.
These include:
Errors reported by db2dart such as:
Error: Invalid page-index 0 in start-logical-row-number
extracted from the column-organized object page map.
Error: Invalid page-index 0 in end-logical-row-number extracted
from the column-organized object page map.
Error: Free Space Offset = 4099 Error: too high!
Record Length = 17 (x11) Error: INVALID LENGTH!
Ending Slot = 18446744073709551615 (xFFFFFFFF) Error:
invalid slot!
Error: page in tree but not marked in use: Object Page (34)
Error: bad pagenumber Error: bad ftoken Error: Not an index
page!
Errors during rollforward such as the following may be reported
in the db2diag.log:
FUNCTION: DB2 UDB, index manager, sqliakn, probe:902
FUNCTION: DB2 UDB, index manager, sqliakn, probe:901
FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page,
probe:2
MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad"
If the above errors are seen during rollforward but the
following message is also seen after the errors in the
db2diag.log, the problem may have been resolved already.
FUNCTION: DB2 UDB, index manager, sqliMarkInxBadDuringUndoRedo,
probe:2037
Unexpected error. Index will be marked bad to let recovery
continue.
A trap may occur during rollforward such as the following
edited/simplified stacks may occur:
0x00007F2170178C1B sqloEDUCodeTrapHandler
0x00007F21704AA88C _Z14sqlprFindQueue
0x00007F2170494918 _Z15sqlpPRecProcLog
0x00007F2170450E9E _Z20sqlpParallelRecovery
OR:
0x00007FD122442423 sqloEDUCodeTrapHandler
0x00007FD122E15340 sqlra_sqlWC_get_common
0x00007FD122AA930D _Z20sqlrr_signal_handler
0x00007FD122443F35 sqloExecuteEDUExitList
0x00007FD12244170A sqloDumpDiagInfoHandler
0x00007FD1222DA90F sqloWaitEDUWaitPost
0x00007FD1205410D5 _ZN11sqeScoordCB10ScoordWaitEP
0x00007FD12270DBE2 _Z8sqlprecmP8sqeAgent
0x00007FD12273A9F6 _Z19sqlprDoForwardPhaseP8sqeAgent
0x00007FD122737241 _Z19sqlpForwardRecoveryP8sqeAgent
Errors reported from rollforward may include:
SQL1271W Database "WSDB" is recovered but one or more table
spaces are
offline on members or nodes "0".
SQL1224N The database manager is not able to accept new
requests, has
terminated all requests in progress, or has terminated the
specified request
because of an error or a forced interrupt. SQLSTATE=55032
If the rollforward succeeded and did not automatically resolve
the issue, the following errors may be seen. It is possible
other errors may be reported.
Select, update and other commands may get:
SQL0901N The SQL statement or command failed because of a
database system
error. (Reason "unexpected error at
DatabasePageMapCursor.cpp:95".)
SQLSTATE=58004
AssertionFailedException: An assertion 'pPageMapIXCB != __null
&& pPageMapIXCB->itoken != 0 && (
(pPageMapIXCB->idxcb.pIndexDef->indexType) &
(0x0004000000000000UL) )' failed. Additional info:
DatabasePageMapCursor: Page map index must have been set by now.
IXCB = NULL itoken = -1 pagemap = Unknown
Thrown at DatabasePageMapCursor.cpp : 95
DATA #2 : Function name in Library, 105 bytes
ibm_cde::data::DatabasePageMapCursor::DatabasePageMapCursor(cons
t ibm_cde::data::DatabaseTableAccessor &)
DATA #3 : File name, 25 bytes
DatabasePageMapCursor.cpp
CALLSTCK:
[2] 0x00007FF36EC0C8AF
_ZN7ibm_cde8services24AssertionFailedException
[3] 0x00007FF36F9E8C4C _ZN7ibm_cde4data21DatabasePageMapCursor
[5] 0x00007FF36F3D26B1 _ZN7ibm_cde4data21DatabaseTableAccessor
[13] 0x00007FF370A9EE27
_ZN7ibm_cde5query9Scheduler19runSingleTableQueryEv
[16] 0x00007FF3716E6777 _ZN12cdeInterface10IUDExecute
How to know if the problem has happened if there were no
rollforward errors?
If you have done all the prerequisite actions listed above, then
check for errors by doing a db2dart inspection of the table. |
| Problem-Zusammenfassung: |
****************************************************************
* USERS AFFECTED: *
* Users of restore of LOAD COPY for column organized table *
* which has a modification state index *
****************************************************************
* PROBLEM DESCRIPTION: *
* The problem may occur if you have a *
* column organized table which has a modification state index *
* and *
* done a LOAD with COPY YES and INDEXING MODE REBUILD and *
* done a rollforward that restored such a load copy. *
* *
* NOTE: INDEXING MODE REBUILD may be chosen even if you did *
* not specify it. Check for the message SQL3213I The indexing *
* mode is "REBUILD". *
* *
* A modification state index is created on a column organized *
* table when create index is done. *
* It is also created if the registry variable *
* DB2_EXTEND_COL_UNIQUE_INDEX_ACCESS set to ON (the default is *
* currently OFF) and a unique or primary key constraint is *
* added. *
* To check which tables have a modification state index, do: *
* db2 "select tabname, tabschema, indname, index_objectid, *
* tbspaceid from syscat.indexes where indextype = 'MDST'" *
* *
* If load in rebuild mode with the copy yes option is being *
* used for a table that has a modification state index, it is *
* recommended to take a new backup and apply the fix. Avoid *
* rolling forward thru such a load copy created without this *
* fix. *
* *
* There are a number of symptoms resulting from this problem *
* after or during rollforward through a loadcopy for a column *
* organized table that has a modification state index. *
* These include: *
* *
* Errors reported by db2dart such as: *
* Error: Invalid page-index 0 in start-logical-row-number *
* extracted from the column-organized object page map. *
* Error: Invalid page-index 0 in end-logical-row-number *
* extracted from the column-organized object page map. *
* Error: Free Space Offset = 4099 Error: too high! *
* Record Length = 17 (x11) Error: INVALID LENGTH! *
* Ending Slot = 18446744073709551615 (xFFFFFFFF) Error: *
* invalid slot! *
* Error: page in tree but not marked in use: Object Page (34) *
* Error: bad pagenumber Error: bad ftoken Error: Not an *
* index page! *
* *
* Errors during rollforward such as the following may be *
* reported in the db2diag.log: *
* FUNCTION: DB2 UDB, index manager, sqliakn, probe:902 *
* FUNCTION: DB2 UDB, index manager, sqliakn, probe:901 *
* FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page, *
* probe:2 *
* MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is *
* bad" *
* *
* If the above errors are seen during rollforward but the *
* following message is also seen after the errors in the *
* db2diag.log, the problem may have been resolved already. *
* FUNCTION: DB2 UDB, index manager, *
* sqliMarkInxBadDuringUndoRedo, probe:2037 *
* Unexpected error. Index will be marked bad to let recovery *
* continue. *
* *
* A trap may occur during rollforward such as the following *
* edited/simplified stacks may occur: *
* 0x00007F2170178C1B sqloEDUCodeTrapHandler *
* 0x00007F21704AA88C _Z14sqlprFindQueue *
* 0x00007F2170494918 _Z15sqlpPRecProcLog *
* 0x00007F2170450E9E _Z20sqlpParallelRecovery *
* OR: *
* 0x00007FD122442423 sqloEDUCodeTrapHandler *
* 0x00007FD122E15340 sqlra_sqlWC_get_common *
* 0x00007FD122AA930D _Z20sqlrr_signal_handler *
* 0x00007FD122443F35 sqloExecuteEDUExitList *
* 0x00007FD12244170A sqloDumpDiagInfoHandler *
* 0x00007FD1222DA90F sqloWaitEDUWaitPost *
* 0x00007FD1205410D5 _ZN11sqeScoordCB10ScoordWaitEP *
* 0x00007FD12270DBE2 _Z8sqlprecmP8sqeAgent *
* 0x00007FD12273A9F6 _Z19sqlprDoForwardPhaseP8sqeAgent *
* 0x00007FD122737241 _Z19sqlpForwardRecoveryP8sqeAgent *
* *
* Errors reported from rollforward may include: *
* SQL1271W Database "WSDB" is recovered but one or more table *
* spaces are *
* offline on members or nodes "0". *
* SQL1224N The database manager is not able to accept new *
* requests, has *
* terminated all requests in progress, or has terminated the *
* specified request *
* because of an error or a forced interrupt. SQLSTATE=55032 *
* *
* If the rollforward succeeded and did not automatically *
* resolve the issue, the following errors may be seen. It is *
* possible other errors may be reported. *
* Select, update and other commands may get: *
* SQL0901N The SQL statement or command failed because of a *
* database system *
* error. (Reason "unexpected error at *
* DatabasePageMapCursor.cpp:95".) *
* SQLSTATE=58004 *
* AssertionFailedException: An assertion 'pPageMapIXCB != *
* __null && pPageMapIXCB->itoken != 0 && ( *
* (pPageMapIXCB->idxcb.pIndexDef->indexType) & *
* (0x0004000000000000UL) )' failed. Additional info: *
* DatabasePageMapCursor: Page map index must have been set by *
* now. IXCB = NULL itoken = -1 pagemap = Unknown *
* Thrown at DatabasePageMapCursor.cpp : 95 *
* DATA #2 : Function name in Library, 105 bytes *
* ibm_cde::data::DatabasePageMapCursor::DatabasePageMapCursor( *
* const ibm_cde::data::DatabaseTableAccessor &) *
* DATA #3 : File name, 25 bytes *
* DatabasePageMapCursor.cpp *
* CALLSTCK: *
* [2] 0x00007FF36EC0C8AF *
* _ZN7ibm_cde8services24AssertionFailedException *
* [3] 0x00007FF36F9E8C4C *
* _ZN7ibm_cde4data21DatabasePageMapCursor *
* [5] 0x00007FF36F3D26B1 *
* _ZN7ibm_cde4data21DatabaseTableAccessor *
* [13] 0x00007FF370A9EE27 *
* _ZN7ibm_cde5query9Scheduler19runSingleTableQueryEv *
* [16] 0x00007FF3716E6777 _ZN12cdeInterface10IUDExecute *
* *
* How to know if the problem has happened if there were no *
* rollforward errors? *
* If you have done all the prerequisite actions listed above, *
* then check for errors by doing a db2dart inspection of the *
* table. *
****************************************************************
* RECOMMENDATION: *
* Take a new backup and upgrade to the latest fix pack. *
**************************************************************** |
| Local-Fix: |
To prevent the problem from occurring before the fix is applied,
take a new backup and do not restore/rollforward using an older
backup that may result in ther restore of an old loadcopy.
If a "load .. copy yes" with indexing mode REBUILD needs to be
done before the fix is applied, the modification state index can
be dropped but this may result in some index plans not being
chosen.
Instructions for recreating the modification state index after
the fix is applied are included at the end of this section.
Example of a query to get the name of a modification state index
to drop.
db2 "select substr(indname,1,30) indname from syscat.indexes
where indextype = 'MDST' and tabname='T1'"
The following are actions that can be taken if a problem has
already occurred.
If rollforward worked but an error is encountered after
rollforward:
Query to see what tables have a modification state index and to
get the tbspaceid and index_objectid of the index object:
db2 "select tabname, tabschema, indname, index_objectid,
tbspaceid from syscat.indexes where indextype = 'MDST'"
Query to get the tbspaceid and tableid of the table:
db2 -v "select tbspaceid, tableid, substr(tabname, 1, 10) from
syscat.tables where tabname='' and
tabschema=''"
Use the output to do the following to check the table for
errors:
db2dart $dbName /t /tsi /oi
If errors are reported, use the output from the syscat.indexes
query above in this db2dart command to mark the index object
invalid.
db2dart $dbName /mi /tsi /oi
These indexes will be rebuilt according to the INDEXREC
configuration parameter.
If an error occurs during during rollforward and it does not
complete successfully some options are:
A) Get the fix for this defect which includes a support registry
variable setting to mark the index invalid after restoring the
load copy, allowing the rollforward to complete.
The index object will then be rebuilt according to the INDEXREC
config parameter setting.
B) db2 RESTORE DB REBUILD WITH ALL TABLESPACES IN
DATABASE EXCEPT TABLESPACE .
When the fix is applied, restore the tablespace(s) that had the
problem using the support registry variable setting.
C) Do filtered recovery to avoid rollforward on the table in
question. Unfortunately the table woud be left in drop pending
state.
The db could restored to a new db and rollforward to a point in
time before the load copies, to recover the data for the bad
table to be imported into a new table on the original db.
D) Rollforward to a point in time before the load copy(s), but
this would result in some updates being lost.
How to recreate the modification state if it was dropped, but a
regular index still exists:
In this example, the regular index is one associated a primary
key or unique constraint, but it could also be a user defined
index.
- Get the name of the index and verify that an index with type
MDST does not exist.
db2 "select substr(indname,1,25) indname, indextype from
syscat.indexes where tabschema='TEST1' and tabname = 'T1' and
indextype in ('REG','MDST')"
INDNAME INDEXTYPE
SQL171120105642950 REG
- Get the column names in the index.
db2 "select substr(indname,1,35) indname, substr(colname, 1,
10) colname, COLSEQ, COLORDER from syscat.indexcoluse where
indname = 'SQL171120105642950'"
INDNAME COLNAME COLSEQ
COLORDER
SQL171120105642950 COL2 1 A
SQL171120105642950 COL1 2 A
- Create an index that uses the same columns as the primary key
or unique constraint (or a user defined index).
db2 "create index I1 on TEST1.T1 (col2,col1)"
SQL0605W The index was not created because an index
"SYSIBM.SQL171120105642950" with a matching definition
already exists.
SQLSTATE=01550
- Verify that the modification state index was created, but no
regular index was created by running the initial query again.
INDNAME INDEXTYPE
SQL171120105642950 REG
SQL171120105649725388 MDST |
| Lösung |
|
| Workaround |
If no problem has been encountered, take a new backup and do not
use load copy for column organized tables that have a
modification state index until the the fix is applied.
If rollforward completes successfully, but there are errors in
the index, use db2dart to mark the index invalid so it will be
rebuilt according to the INDEXREC configuration parameter.
If rollforward does not complete, then it is best to get the fix
and use the service registry variable provided to allow
rollforward to complete. If that is not feasible, options
include
- db2 RESTORE DB REBUILD WITH ALL TABLESPACES IN DATABASE
EXCEPT TABLESPACE and later restore the
problem one once you have the fix with the service registry
variable set.
- filtered recovery to avoid rollforward for the table that has
a problem, and try to recover that table separately as much as
possible
- rollforward to a point in time before the load copy. |
| Bug-Verfolgung |
Vorgänger :
Nachfolger : IT30793 |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 12.04.2019
31.10.2019
29.11.2019 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
|
| Problem behoben lt. FixList in der Version |
