IBM Support

IC72946: REDISTRIBUTION FAILS WITH SQL0984 "-902" WHEN PARTITIONED INDEXES LOCATE IN DIFFERENCE TABLESPACE THAN DATA PARTITION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • This problem is encountered only when growing the database
    partition group and reditributing when there is a range
    partitioned table with partitioned indexes stored in a different
    tablespace than the data partitions. Redistribute was trying to
    insert the table record in SYSIBM.INTERNAL in the wrong location
    in the new partitions.  In the specific case,  the slot was
    already taken by an index record belonging to a different table.
    
    For example,
    2010-11-07-02.14.10.900806+540 E4377098A711       LEVEL: Info
    PID     : 1913044              TID  : 14871       PROC : db2sysc
    0
    INSTANCE: db2inst1             NODE : 000         DB   : db2db
    APPHDL  : 0-146                APPID: *N0.db2inst1.101106132259
    AUTHID  : DB2INST1
    EDUID   : 14871                EDUNAME: db2agent (db2db) 0
    FUNCTION: DB2 UDB, database utilities - Redistribute,
    sqlurRedistributeTableByID, probe:8236
    MESSAGE : REDISTRIBUTE TABLE
    DATA #1 : String, 17 bytes
    <schema> <tbname>
    DATA #2 : String, 8 bytes
    db2inst1
    DATA #3 : String, 20 bytes
    table1
    DATA #4 : String, 18 bytes
    <pool ID> <obj ID>
    DATA #5 : unsigned integer, 2 bytes
    14
    DATA #6 : unsigned integer, 2 bytes
    4
    
    2010-11-07-02.14.11.010710+540 I4377810A542       LEVEL: Severe
    PID     : 2252934              TID  : 23961       PROC : db2sysc
    5
    INSTANCE: db2inst1             NODE : 005         DB   : db2db
    APPHDL  : 0-146                APPID: *N0.db2inst1.101106132259
    AUTHID  : DB2INST1
    EDUID   : 23961                EDUNAME: db2agntp (db2db) 5
    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
    "".
    
    ..
    
    2010-11-07-02.14.11.510083+540 I4451543A510       LEVEL: Severe
    PID     : 2252934              TID  : 23961       PROC : db2sysc
    5
    INSTANCE: db2inst1             NODE : 005         DB   : db2db
    APPHDL  : 0-146                APPID: *N0.db2inst1.101106132259
    AUTHID  : DB2INST1
    EDUID   : 23961                EDUNAME: db2agntp (db2db) 5
    FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
    logging fu, probe:0
    MESSAGE : SQLD_TCB:
    DATA #1 : String, 36 bytes
    Perm Table(15:65535)=SYSIBM.INTERNAL
    

Local fix

  • 1) dropping all of the partitioned indexes before redistribute.
    Then recreate indexes after the redistribute
    2) skipping the Range partitioned tables and using alternative
    data movement
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * AIX/SunOS/Linux                                              *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * This problem is encountered only when growing the database   *
    *                                                              *
    * partition group and reditributing when there is a range      *
    *                                                              *
    * partitioned table with partitioned indexes stored in a       *
    * different                                                    *
    * tablespace than the data partitions. Redistribute was trying *
    * to                                                           *
    * insert the table record in SYSIBM.INTERNAL in the wrong      *
    * location                                                     *
    * in the new partitions.  In the specific case,  the slot was  *
    *                                                              *
    * already taken by an index record belonging to a different    *
    * table.                                                       *
    *                                                              *
    *                                                              *
    * For example,                                                 *
    *                                                              *
    * 2010-11-07-02.14.10.900806+540 E4377098A711      LEVEL: Info *
    *                                                              *
    * PID    : 1913044              TID  : 14871      PROC :       *
    * db2sysc                                                      *
    * 0                                                            *
    *                                                              *
    * INSTANCE: db2inst1            NODE : 000        DB  : db2db  *
    * APPHDL  : 0-146                APPID:                        *
    * *N0.db2inst1.101106132259                                    *
    * AUTHID  : DB2INST1                                           *
    *                                                              *
    * EDUID  : 14871                EDUNAME: db2agent (db2db) 0    *
    *                                                              *
    * FUNCTION: DB2 UDB, database utilities - Redistribute,        *
    *                                                              *
    * sqlurRedistributeTableByID, probe:8236                       *
    *                                                              *
    * MESSAGE : REDISTRIBUTE TABLE                                 *
    *                                                              *
    * DATA #1 : String, 17 bytes                                   *
    *                                                              *
    * <schema> <tbname>                                            *
    *                                                              *
    * DATA #2 : String, 8 bytes                                    *
    *                                                              *
    * db2inst1                                                     *
    *                                                              *
    * DATA #3 : String, 20 bytes                                   *
    *                                                              *
    * table1                                                       *
    *                                                              *
    * DATA #4 : String, 18 bytes                                   *
    *                                                              *
    * <pool ID> <obj ID>                                           *
    *                                                              *
    * DATA #5 : unsigned integer, 2 bytes                          *
    *                                                              *
    * 14                                                           *
    *                                                              *
    * DATA #6 : unsigned integer, 2 bytes                          *
    *                                                              *
    * 4                                                            *
    *                                                              *
    * ..                                                           *
    *                                                              *
    *                                                              *
    *                                                              *
    * 2010-11-07-02.14.11.510083+540 I4451543A510      LEVEL:      *
    * Severe                                                       *
    * PID    : 2252934              TID  : 23961      PROC :       *
    * db2sysc                                                      *
    * 5                                                            *
    *                                                              *
    * INSTANCE: db2inst1            NODE : 005        DB  : db2db  *
    * APPHDL  : 0-146                APPID:                        *
    * *N0.db2inst1.101106132259                                    *
    * AUTHID  : DB2INST1                                           *
    *                                                              *
    * EDUID  : 23961                EDUNAME: db2agntp (db2db) 5    *
    *                                                              *
    * FUNCTION: DB2 UDB, trace services, sqlt_logerr_string        *
    * (secondary                                                   *
    * logging fu, probe:0                                          *
    *                                                              *
    * MESSAGE : SQLD_TCB:                                          *
    *                                                              *
    * DATA #1 : String, 36 bytes                                   *
    *                                                              *
    * Perm Table(15:65535)=SYSIBM.INTERNAL                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 FP4.                                     *
    ****************************************************************
    

Problem conclusion

  • This problem fixed in DB2 V9.7 FP4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72946

  • 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

    2010-12-01

  • Closed date

    2011-06-23

  • Last modified date

    2011-06-23

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

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

    IC72967

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 #: IC72946

Modified date: 23 June 2011