IBM Support

IC84118: QUERYING A DETACHED PARTITION OF A RANGE-PARTITIONED/MDC TABLE RETURNS SQL0901N ERROR ON HADR STANDBY AFTER TAKEOVER

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When using a data-partitioned MDC table, detaching a partition
    results in the following with regards to the resulting target
    table (i.e. the detached partition):
    - An empty index object is created
    - The indexes are marked in error and as requiring rebuild
    - The next access to the detached partition will drive index
    rebuild and all of the block indexes will be recreated.
    
    In an HADR system, if a takeover occurs before the next access
    to the detached partition, then the index will no longer be
    marked in error and as requiring rebuild.  As a result, the next
    query that is run on the new primary that requires the block
    index will fail with an SQL0901N with the reason text "Unable to
    find index on table!".
    
    An example of error messages in db2diag.log  is shown below:
    
    2011-10-10-14.35.10.144742+660 I8706135A2169      LEVEL: Severe
    PID     : 843992               TID  : 70421       PROC : db2sysc
    0
    INSTANCE: INSTNAME1              NODE : 000         DB   :
    DBNAME1
    APPHDL  : 0-25092              APPID:
    *LOCAL.INSTNAME1.111010033438
    AUTHID  : INSTNAME1
    EDUID   : 70421                EDUNAME: db2agntp (DBNAME1) 0
    FUNCTION: DB2 UDB, data management, sqldSetupIndexScan, probe:1
    MESSAGE : Unable to find index on table!
    

Local fix

  • Take database offline and mark the index bad using db2dart's /mi
    option.  Then run restart the instance and run RESTART DATABASE
    and run a SELECT against the problem table to rebuild the index.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 9.7.0.1.                                      *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 9.7.0.1.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC84118

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-06-12

  • Closed date

    2012-11-14

  • Last modified date

    2012-11-14

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

    IV09728

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC84118

Modified date: 14 November 2012