IBM Support

IC79752: -901 ERROR WHILE COMPILING QUERY HAVING MULTIPLE AGGREGATE DISTINCT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Compiling a query may throw out -901 error "qnc was not found
    when expected" with the following call stack
    
    Compiler error stack for rc = -2144272209:
    sqlnn_cmpl[370]
    sqlnr_exe[1200]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_optprep_action[100]
    sqlnr_optprep[2240]
    sqlnr_compute_matchinfo[600]
    sqlnq_som::compute_matchin[230]
    sqlnq_mch::computeQclqncPP[900]
    
    when query contains multiple aggregate distinct on different
    columns, e.g. COUNT(DISTINCT COLX), COUNT(DISTINCT COLY) and the
    query referenced base table has dependent optimizer MQT. An
    optimizer MQT is such Materialized Query Table whose definition
    is a SELECT from one or multiple base table with only join or
    filtering predicate.
    

Local fix

  • Add all aggregate columns, i.e. COLX and COLY, to MQT select
    output list.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All DB2 Users                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * -901 ERROR WHILE COMPILING QUERY HAVING MULTIPLE AGGREGATE   *
    * DISTINCT                                                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * The customer should upgrade to DB2 V97 FP6  or higher        *
    * versions to fix this issue.                                  *
    ****************************************************************
    

Problem conclusion

  • The customer should upgrade to DB2 V97 FP6 or higher versions
    to fix this issue.
    

Temporary fix

  • Add all aggregate columns, i.e. COLX and COLY, to MQT select
    output list.
    

Comments

APAR Information

  • APAR number

    IC79752

  • 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

    2011-11-11

  • Closed date

    2012-06-08

  • Last modified date

    2012-06-08

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

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

    IC84292 IC91742

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

Modified date: 08 June 2012