IBM Support

IT09727: DB2 MIGHT PRODUCE SQL0901N WHEN EXECUTING QUERY WITH SINGLE SUBTERM INLIST

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 might fail with SQL0901N error message
    when the following conditions are true:
    1) The query contains one or more IN predicate
    2) Atleast one of the IN predicate is of type Col IN (CONST)
    
    eg.
    SELECT 1
    FROM TEMP_TABLE D
    WHERE D.TYPE IN ( VALUES ('abc') );
    
    <StackTrace>
    -------Frame------ ------Function + Offset------
    0x090000000055BF94 pthread_kill + 0xD4
    0x0900000003067474 sqloDumpEDU + 0xA4
    0x0900000003339CB4 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
    0xCC
    0x09000000036AD294
    sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2@glue5BB + 0x98
    0x0900000003232AE0 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x320
    0x09000000058507D0 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x50
    0x0900000003533870 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
    0x240
    0x0900000004E8E84C sqlnn_erds__FiN41e + 0x33C
    0x0900000003E6D7F4
    sqlng_build_INDX_key__FP9sqlng_blkP14sqlng_scan_blkP13sqlno_keyc
    ondT3P10sqlz_valueT5PUlT7PP16sqlriIXKeyUpdateT9PUc + 0xB40
    0x0900000006DE8D64
    sqlng_build_KEY_obj__FP9sqlng_blkP14sqlng_scan_blkP16sqlno_ixkey
    condsT313SQLNN_BOOLEANP19sqlng_skeleton_nodeUcPPUcT8PUsT10_PP10S
    QLD_IXKEYT12_PUiT14_ + 0x958
    0x0900000006DF88DC
    sqlng_build_INDX_obj__FP9sqlng_blkP14sqlng_scan_blkPP11SQLD_IXIN
    FO + 0x39C
    0x0900000006DF1E90
    sqlng_build_TA_op__FP9sqlng_blkP14sqlng_scan_blkP9sqlno_qtb +
    0x2BC
    0x0900000006E17300
    sqlng_process_iscan_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x7F4
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E672BC
    sqlng_process_hsjn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0xB20
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E672BC
    sqlng_process_hsjn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0xB20
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E10F60
    sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x2E3C
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x0900000006E09418
    sqlng_process_sort_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x6A8
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E1A20C
    sqlng_scan_derived_table__FP9sqlng_blkP19sqlno_plan_operatorUcP9
    sqlnq_qun + 0x1718
    0x0900000004EF877C
    sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x54
    0x0900000006E056F0
    sqlng_process_groupby_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x484
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x0900000004F2C5E0
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0xA44
    0x0900000006E3A43C
    sqlng_process_smptq_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x4DC
    0x0900000006E395B8
    sqlng_process_tq_op__FP9sqlng_blkP19sqlno_plan_operator + 0x38C
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x0900000006E06810
    sqlng_process_temp_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x5CC
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E1A20C
    sqlng_scan_derived_table__FP9sqlng_blkP19sqlno_plan_operatorUcP9
    sqlnq_qun + 0x1718
    0x0900000004EF877C
    sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x54
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E67018
    sqlng_process_hsjn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x87C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E10F60
    sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x2E3C
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E10F60
    sqlng_process_nljn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x2E3C
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E67018
    sqlng_process_hsjn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x87C
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E672BC
    sqlng_process_hsjn_op__FP9sqlng_blkP19sqlno_plan_operator +
    0xB20
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x0900000006E09418
    sqlng_process_sort_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x6A8
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000006E1A20C
    sqlng_scan_derived_table__FP9sqlng_blkP19sqlno_plan_operatorUcP9
    sqlnq_qun + 0x1718
    0x0900000004EF877C
    sqlng_process_scan_op__FP9sqlng_blkP19sqlno_plan_operator + 0x54
    0x09000000054962AC
    sqlng_process_mate_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1BC
    0x09000000054959BC
    sqlng_process_pipe_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x25C
    0x090000000549562C
    sqlng_process_return_op__FP9sqlng_blkP19sqlno_plan_operator +
    0x1E4
    0x09000000054930B4
    sqlng_build_thread__FP9sqlng_blkP14sqlng_thd_ctrl + 0x1B4
    0x0900000005498378 sqlng_main__FP9sqlnq_qur + 0x6FC
    0x090000000591D004 sqlng_main__FP9sqlnq_qur@glue17F1 + 0x74
    0x090000000591CA30
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_enviT7PP9sqlnq_qur + 0x304
    0x090000000560F100
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_env + 0x40
    0x09000000058EA428
    sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
    LOCK_INFOP16sqlra_cached_varPiPUl + 0x810
    0x09000000058ED5BC
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
    SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0x5DC
    0x09000000058EFB14 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x6F4
    0x0900000003A163E8
    sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x2B0
    0x0900000005A00D4C
    sqljs_ddm_prpsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0xC6C
    0x09000000037BC3E4
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0x280
    0x090000000570A304
    .sqljsParse.fdpr.clone.274__FP13sqljsDrdaAsCbP14db2UCinterfaceP8
    sqeAgentb + 0x6AC
    0x0900000005708FA4 @63@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
    + 0x2B0
    0x09000000055064B0
    @63@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA4
    0x090000000550610C
    @63@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x278
    0x0900000005505BC0 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
    0x1EC
    0x09000000055D917C RunEDU__8sqeAgentFv + 0x2F0
    0x09000000055D7968 EDUDriver__9sqzEDUObjFv + 0xE8
    0x09000000055DDE18 sqloEDUEntry + 0x250
    </StackTrace>
    

Local fix

  • If the singleton predicate is a user defined predicate, then
    rewrite the query to convert it into an equality predicate.
    This might not be possible if the predicate was generated
    internally
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 970 fixpack 11                        *
    ****************************************************************
    

Problem conclusion

  • Problem was fixed in DB2 version 970 fixpack 11
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT09727

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-06-26

  • Closed date

    2015-10-22

  • Last modified date

    2015-10-22

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

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

    IT09766 IT13337

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT09727

Modified date: 22 October 2015