IBM Support

IC81157: 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                                        *
    * 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_compileModeT3P *
    * 14sqlrr_cmpl_enviT7PP9sqlnq_qur + 0x320                      *
    * 0x0900000008206B14                                           *
    * sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P *
    * 14sqlrr_cmpl_env + 0x40                                      *
    * 0x0900000007F74D00                                           *
    * sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14S *
    * QLP_LOCK_INFOP16sqlra_cached_varPiPUl + 0x7C4                *
    * 0x0900000007F72948                                           *
    * sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_ *
    * idUiT4PUcT4UsUcP14sqlra_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__FP13sqljsDrdaAsCbP13sqljDDMObjectP14d *
    * b2UC                                                         *
    * interface + 0x228                                            *
    * 0x0900000008120044.sqljsParse.fdpr.clone.223__FP13sqljsDrdaA *
    * sCbP14db2UCinterfaceP8                                       *
    * 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                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 UDB Version 9.7, FixPak 6                     *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.7, FixPak 6
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    IC81157

  • 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-02-01

  • Closed date

    2012-06-08

  • Last modified date

    2012-06-08

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

Modified date: 08 June 2012