IBM Support

IT18745: DB2 MIGHT PRODUCE SQL0901N WHEN EXECUTING QUERY WITH GROUPBY ANDOUTER JOIN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 might produce SQL0901N error message when executing a query
    with the following pattern.
    
    1) The query has one or more outer join(s)
    2) The null producing subselect of the outer join has a group by
    clause
    3) The subselect below the group by has two or more identical
    expressions in the projected columns.
    
    eg.
     SELECT T2.*
     FROM T1 LEFT OUTER JOIN
     ( select T2.C1, T2.C2,
              sum(CASE WHEN (T2.c3='20610') THEN 1 ELSE 0 END) as
    col1,
              sum(CASE WHEN (T2.c3='20610') THEN 1 ELSE 0 END) as
    col2
       FROM T2
       GROUP BY T2.C1, T2.C2) T2
     ON
     (T1.C1 = T2.C1 and T2.C2 = T1.C2);
    
    Stack trace produced:
    
    <StackTrace>
    pthread_kill + 0xD4
    sqloDumpEDU + 0xC4
    sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0x110
    sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x72C
    sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28
    sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x234
    sqlnn_erds__FiN41e + 0x140
    sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x2F54
    sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x354
    sqlnr_gbpu_thru_OJ__FCP9sqlnq_qurPiCP3loc + 0x12C
    sqlnr_qrwprep_phase2__FP3locPi + 0xE04
    sqlnr_prep2_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 0x4C
    .sqlnr_comp.fdpr.clone.1544__FPiiP16sqlnr_rule_stateP10sqlnr_qrw
    aP14sqlnr_progress + 0x1E8
    sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass +
    0xC4
    sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress + 0xC4
    sqlnr_exe__FP9sqlnq_qur + 0x1174
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_enviT7PP9sqlnq_qur + 0x2CEC
    sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
    lrr_cmpl_env + 0x38
    sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
    LOCK_INFOP16sqlra_cached_varPiT11_Pb + 0x868
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
    T4PUcT4U
    sUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14SQLP_LOC
    K_INFOPP
    16sqlra_cached_varT12_bT19_Pb + 0x10C8
    sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x2194
    sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x190
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0xDE50
    @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1B84
    @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1B84
    @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0x1778
    @72@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA8
    @72@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x5F8
    RunEDU__8sqeAgentFv + 0x4BA88
    RunEDU__8sqeAgentFv + 0x120
    EDUDriver__9sqzEDUObjFv + 0x134
    sqloEDUEntry + 0x390
    </StackTrace>
    

Local fix

  • Workaround: None
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 11.1 Mod 2 Fix Pack 2
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT18745

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-01-10

  • Closed date

    2017-06-28

  • Last modified date

    2017-06-28

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020