IBM Support

IC87836: QUERY REFERENCING AGGREGATION AND OLAP FUNCTION IN SAME EXPRESSION MIGHT GENERATE A -901

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, if OLAP and aggregation functions are part
    of the same expression in the select list,
    DB2 might generate a SQL0901N error message.
    
    eg. Select row_number() over () + min(c1) from t1;
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "Bad Opcode
    0, ref_arity
    2, sqlnq_pid ID 943 (SYSIBM.SCALAG)".)  SQLSTATE=58004
    
    Stack:
    0x0900000007FD6CC0 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28
    0x0900000007FD73A4 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
    0x140
    0x0900000007FD71B8 sqlnn_erds__FiN41e + 0x150
    0x09000000051E0494 sqlnn_erds__FiN41e@glue718 + 0xC0
    0x0900000006835834
    sqlng_build_S_X_op__FP9sqlng_blkPP12sqlri_opparmP9sqlnq_pid +
    0x1E20
    0x090000000835B7E0
    sqlng_process_parse_tree_node__FP9sqlng_blkP9sqlnq_pid + 0x30C
    0x090000000835BDAC
    sqlng_process_BF_node__FP9sqlng_blkP9sqlnq_pid + 0x114
    0x090000000835A698 sqlng_walk_HXP_chain__FP9sqlng_blk + 0x254
    0x090000000835A0BC
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x140
    0x090000000835D394
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x254
    0x090000000835C9AC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x204
    0x090000000835C678
    sqlng_process_return_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1E4
    0x090000000835A128
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1AC
    0x090000000835F200 sqlng_main__FP9sqlnq_qur + 0x6BC
    0x090000000837E280 sqlng_main__FP9sqlnq_qur@glue17D0 + 0x114
    0x090000000837DDEC
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_enviT7PP9sqlnq_qur + 0x320
    0x0900000008206B14
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_env + 0x40
    0x0900000007F74D00
    sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
    LOCK_INFOP16sqlra_cached_varPiPUl + 0x7C4
    0x0900000007F72948
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
    SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0x648
    0x0900000007F707FC sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x620
    0x0900000004DFC040
    sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x2B8
    0x090000000587F8C8
    sqljs_ddm_prpsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x858
    0x0900000003C1720C
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0x228
    0x0900000008120044
    .sqljsParse.fdpr.clone.223__FP13sqljsDrdaAsCbP14db2UCinterfaceP8
    sqeAgentb + 0x230
    0x090000000810660C @64@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
    + 0xAFC
    0x09000000082C79A8
    @64@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA0
    0x0900000003D1B1F0
    @64@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x354
    0x09000000082C7184 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
    0xE4
    0x0900000008281EC0 RunEDU__8sqeAgentFv + 0x7C
    0x090000000828451C EDUDriver__9sqzEDUObjFv + 0xDC
    0x090000000826C038 sqloEDUEntry + 0x270
    

Local fix

  • Workaround:
    Please rewrite the query to separate the OLAP function.
    eg. select row_number() over () + col1 from (select min(c1) as
    col1 from t1)
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to DB2 UDB Version 10.1 Fix Pack 1                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 10.1 Fix Pack 1
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC87836

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-04

  • Closed date

    2012-11-07

  • Last modified date

    2012-11-07

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

    IC81157

  • 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

  • RA10 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.1

Reference #: IC87836

Modified date: 07 November 2012