IBM Support

IC69433: THERE COULD BE -901 WHEN COMPILING A QUERY CONTAINING SCALAR AGGREGATE FUNCTIONS WITH PARTITION BY CLAUSE IN SMP

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • In SMP environment(ie. intra-partition parallelism is enabled),
    if a query containing scalar aggregate functions with PARTITION
    BY clause, eg. ROWNUMBER() OVER (PARTITION by
    A.APLY_NO,A.POLICY_NO), then there could be -901 error
    happening.
    An error message may be left in db2diag.log looked like this:
    =============================================================
    2010-06-02-09.27.05.754479+480 I5761138A882       LEVEL: Severe
    PID     : 242136               TID  : 136603      PROC : db2sysc
    0
    INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
    APPHDL  : 0-19720              APPID:
    10.1.1.1.45113.100930183636
    AUTHID  : DB2INST1
    EDUID   : 136603               EDUNAME: db2agent (SAMPLE) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
    probe:300
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 58
     sqlerrmc: sqlno_smp_create_part_from[200]:rc(     0)
     sqlerrp : SQLNO33E
     sqlerrd : (1) 0x00000000      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0xFFFFFF38      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
    
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    <...skipped...>
    
    2010-06-02-09.27.14.272301+480 E5826893A719       LEVEL: Info
    (Origin)
    PID     : 242136               TID  : 136603      PROC : db2sysc
    0
    INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
    APPHDL  : 0-19720              APPID:
    10.1.1.1.45113.100930183636
    AUTHID  : DB2INST1
    EDUID   : 136603               EDUNAME: db2agent (SAMPLE) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND
              "Could not find an internal object required to process
    the command"
    DATA #1 : String, 153 bytes
    Compiler error stack for rc = -2144272380:
    sqlnn_cmpl[390]
    sqlno_exe[2000]
    sqlno_final_phase[50]
    sqlnoSmpPlanMateOp[500]
    sqlno_smp_create_part_from[200]
    =============================================================
    
    Corresponding call stack may look like this:
    <StackTrace>
    sqlno_smp_create_part_from_key_order
    sqlnoSmpPlanMateOp
    sqlnoSmpPlanPipeOp
    sqlnoSmpPlanDummy1Op
    sqlnoSmpPlanSortOp
    sqlnoSmpPlanScanOp
    sqlnoSmpPlanMateOp
    sqlnoSmpPlanPipeOp
    sqlno_smp_process_subsection
    sqlnoSmpPlanPass
    sqlnoSmpPostPassMain
    sqlno_final_phase
    sqlno_exe
    sqlno_exe
    sqlnn_cmpl
    ......
    </StackTrace>
    

Local fix

  • Disable intra-partition parallelism.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All.                                                         *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * In SMP environment(ie. intra-partition parallelism is        *
    * enabled), if a query containing scalar aggregate functions   *
    * with PARTITION                                               *
    * BY clause, eg. ROWNUMBER() OVER (PARTITION by                *
    * A.APLY_NO,A.POLICY_NO), then there could be -901 error       *
    * happening.                                                   *
    * An error message may be left in db2diag.log looked like      *
    * this:                                                        *
    * =============================================================
    * 2010-06-02-09.27.05.754479+480 I5761138A882      LEVEL:      *
    * Severe                                                       *
    * PID    : 242136              TID  : 136603      PROC :       *
    * db2sysc 0                                                    *
    * INSTANCE: db2inst1            NODE : 000        DB  : SAMPLE *
    * APPHDL  : 0-19720              APPID:                        *
    * 10.1.1.1.45113.100930183636                                  *
    * AUTHID  : DB2INST1                                           *
    * EDUID  : 136603              EDUNAME: db2agent (SAMPLE) 0    *
    * FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,      *
    * probe:300                                                    *
    * DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes                *
    * sqlcaid : SQLCA    sqlcabc: 136  sqlcode: -901  sqlerrml: 58 *
    * sqlerrmc: sqlno_smp_create_part_from[200]:rc(    0)          *
    * sqlerrp : SQLNO33E                                           *
    * sqlerrd : (1) 0x00000000      (2) 0x00000000      (3)        *
    * 0x00000000                                                   *
    * (4) 0x00000000      (5) 0xFFFFFF38      (6)                  *
    * 0x00000000                                                   *
    * sqlwarn : (1)      (2)      (3)      (4)        (5)      (6) *
    * (7)      (8)      (9)      (10)        (11)                  *
    * sqlstate:                                                    *
    * <...skipped...>                                              *
    *                                                              *
    * 2010-06-02-09.27.14.272301+480 E5826893A719      LEVEL: Info *
    * (Origin)                                                     *
    * PID    : 242136              TID  : 136603      PROC :       *
    * db2sysc 0                                                    *
    * INSTANCE: db2inst1            NODE : 000        DB  : SAMPLE *
    * APPHDL  : 0-19720              APPID:                        *
    * 10.1.1.1.45113.100930183636                                  *
    * AUTHID  : DB2INST1                                           *
    * EDUID  : 136603              EDUNAME: db2agent (SAMPLE) 0    *
    * FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl,          *
    * probe:650                                                    *
    * MESSAGE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND          *
    * "Could not find an internal object required to               *
    * process the command"                                         *
    * DATA #1 : String, 153 bytes                                  *
    * Compiler error stack for rc = -2144272380:                   *
    * sqlnn_cmpl[390]                                              *
    * sqlno_exe[2000]                                              *
    * sqlno_final_phase[50]                                        *
    * sqlnoSmpPlanMateOp[500]                                      *
    * sqlno_smp_create_part_from[200]                              *
    * =============================================================
    *                                                              *
    * Corresponding call stack may look like this:                 *
    * <StackTrace>                                                 *
    * sqlno_smp_create_part_from_key_order                         *
    * sqlnoSmpPlanMateOp                                           *
    * sqlnoSmpPlanPipeOp                                           *
    * sqlnoSmpPlanDummy1Op                                         *
    * sqlnoSmpPlanSortOp                                           *
    * sqlnoSmpPlanScanOp                                           *
    * sqlnoSmpPlanMateOp                                           *
    * sqlnoSmpPlanPipeOp                                           *
    * sqlno_smp_process_subsection                                 *
    * sqlnoSmpPlanPass                                             *
    * sqlnoSmpPostPassMain                                         *
    * sqlno_final_phase                                            *
    * sqlno_exe                                                    *
    * sqlno_exe                                                    *
    * sqlnn_cmpl                                                   *
    * ......                                                       *
    * </StackTrace>                                                *
    *                                                              *
    *                                                              *
    *                                                              *
    * Local fix:                                                   *
    * Disable intra-partition parallelism.                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to db2 version 9.7 fixpack 4 or later fixpack.        *
    ****************************************************************
    

Problem conclusion

  • This problem is first fixed in db2 version 9.7 fixpack 4.
    

Temporary fix

  • Disable intra-partition parallelism.
    

Comments

APAR Information

  • APAR number

    IC69433

  • 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-06-23

  • Closed date

    2011-04-28

  • Last modified date

    2011-04-28

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

    IC69406

  • 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 #: IC69433

Modified date: 28 April 2011