IBM Support

IT28784: RESTORING A LOADCOPY WHEN THERE IS A MODIFICATION STATE INDEX ONA COLUMN ORGANIZED TABLE MAY CORRUPT THE INDEX.

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error 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(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.
    

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='<TABNAME>' and
    tabschema='<TABSCHEMA>'"
    Use the output to do the following to check the table for
    errors:
      db2dart $dbName /t /tsi <tbspaceid> /oi <tableid>
    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 <tbspaceid> /oi <index_objectid>
    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 <MYDB> REBUILD WITH ALL TABLESPACES IN
    DATABASE EXCEPT TABLESPACE <tablespace(s) with problem>.
    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
    

Problem summary

  • ****************************************************************
    * 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.        *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Db2 Version 11.1 mod 4 fix pack 5
    

Temporary fix

  • 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 <MYDB> REBUILD WITH ALL TABLESPACES IN DATABASE
    EXCEPT TABLESPACE <problem 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.
    

Comments

APAR Information

  • APAR number

    IT28784

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-04-12

  • Closed date

    2019-10-31

  • Last modified date

    2019-11-29

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT30793

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 November 2019