IBM Support

IC76327: IN DPF ENVIRONMENT, CREATE INDEX ON CGTT COLLECT ... STATISTICS LEADS TO DB MARKED BAD

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In DPF environment, when CREATE INDEX is executed for a Created
    Global Temporary Tables (CGTT) and the COLLECT ... STATISTICS
    clause is specified, the database may encounter SQL1034C and is
    
    marked bad. This problem does not occur in serial mode.
    
    
    In the db2diag.log, there will be messages about "invalid pool
    ID"
    
    e.g.
    2011-05-05-12.43.29.962968-300 I1130222127E487     LEVEL: Error
    PID     : 28517                TID  : 47089295812928PROC :
    db2sysc 1
    INSTANCE: db2inst1             NODE : 001          DB   : SAMPLE
    APPHDL  : 0-58125              APPID:
    10.30.60.210.3518.110505171433
    AUTHID  : DB2USER
    EDUID   : 154073               EDUNAME: db2agntp (SAMPLE) 1
    FUNCTION: <0>, <0>, <0>, probe:10
    DATA #1 : String, 39 bytes
    sqlbGetPoolDefFromPtbl: Invalid poolID:
    DATA #2 : signed integer, 4 bytes
    65530
    
    ...
    
    2011-05-05-12.43.30.172512-300 I1130233877E4115    LEVEL: Severe
    PID     : 28517                TID  : 47089295812928PROC :
    db2sysc 1
    INSTANCE: db2inst1             NODE : 001          DB   : SAMPLE
    APPHDL  : 0-58125              APPID:
    10.30.60.210.3518.110505171433
    AUTHID  : DB2USER
    EDUID   : 154073               EDUNAME: db2agntp (SAMPLE) 1
    FUNCTION: DB2 UDB, data management, sqldGetTableData, probe:157
    MESSAGE : ZRC=0x87020076=-2029911946=SQLB_INV_POOLID "Invalid
    pool ID"
    DATA #1 : Pool ID, PD_TYPE_SQLB_POOL_ID, 2 bytes
    65530
    DATA #2 : objectID, PD_TYPE_SQLD_OBJECT_ID, 2 bytes
    0x802F
    DATA #3 : objectClass, PD_TYPE_SQLD_OBJECT_CLASS, 1 bytes
    SQLB_PERMANENT
    DATA #4 : Hexdump, 528 bytes
    0x00002AD3D4BFC350 : 0800 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC360 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC370 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC380 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC390 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3A0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3B0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3C0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3D0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3E0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC3F0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC400 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC410 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC420 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC430 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC440 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC450 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC460 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC470 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC480 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC490 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4A0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4B0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4C0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4D0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4E0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC4F0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC500 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC510 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC520 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC530 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC540 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AD3D4BFC550 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    CALLSTCK:
      [0] 0x00002AD231B03F98 pdLog + 0x398
      [1] 0x00002AD231BC2A87
    _Z16sqldGetTableDataP8sqeAgenttththjPciS1_iP15SQLD_TABLE_DATA +
    0x67B
      [2] 0x00002AD233B37A08
    _Z8sqlrkrtcP8sqlrr_cbP13SQLR_RPC_DRTDP17SQLR_RPCRPLY_DRTD +
    0x106
      [3] 0x00002AD233B4AEEB
    _Z20sqlrl_rpc_dms_routerP8sqlrr_cbP16sqlkdRqstRplyFmtiP15SQLR_RP
    CMESSAGE
    + 0x643
      [4] 0x00002AD233687CC3 _Z16sqlrr_rpc_routerP8sqlrr_cb + 0x4FD
      [5] 0x00002AD2336892CF
    _Z21sqlrr_subagent_routerP8sqeAgentP12SQLE_DB2RA_T + 0xD9B
      [6] 0x00002AD2328E2B98 _Z20sqleSubRequestRouterP8sqeAgentPjS1_
    + 0x672
      [7] 0x00002AD2328E3610 _Z21sqleProcessSubRequestP8sqeAgent +
    0xA6
      [8] 0x00002AD231C1DA84 _ZN8sqeAgent6RunEDUEv + 0x630
      [9] 0x00002AD23228BFC8 _ZN9sqzEDUObj9EDUDriverEv + 0xA6
    
    2011-05-05-12.43.30.194813-300 I1130237993E476     LEVEL: Severe
    PID     : 28517                TID  : 47089295812928PROC :
    db2sysc 1
    INSTANCE: db2inst1             NODE : 001          DB   : SAMPLE
    APPHDL  : 0-58125              APPID:
    10.30.60.210.3518.110505171433
    AUTHID  : DB2USER
    EDUID   : 154073               EDUNAME: db2agntp (SAMPLE) 1
    FUNCTION: DB2 UDB, global services, sqlzeMapZrc, probe:45
    MESSAGE : ZRC=0x87020076=-2029911946=SQLB_INV_POOLID "Invalid
    pool ID"
    

Local fix

  • Remove the "collect ... statistics" clause from the CREATE INDEX
    statement, as it is a no-op for CGTT
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * In DPF environment, when CREATE INDEX is executed for a      *
    * Created Global Temporary Tables (CGTT) and the COLLECT ...   *
    * STATISTICS clause is specified, the database may encounter   *
    * SQL1034C and is marked bad                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to Version 9.7 Fix Pack 5                             *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC76327

  • 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

    2011-05-11

  • Closed date

    2012-01-19

  • Last modified date

    2012-01-19

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

  • 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

[{"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":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
19 January 2012