IBM Support

IC90861: QUERY CONTAINING GENERATED COLUMN AND A DISTINCT MIGHT PRODUCE SQL0901N

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 might produce SQL0901N error message
    if the following conditions are true:
    1. The query contains one or more table with generated columns.
    2. The generated column is not referenced in the output column
    list.
    3. The source of the generated column is used in a predicate
    4. There is a DISTINCT or a aggregation (DISTINCT) function in
    the output
    5. The query also contains a not exists or a not in subquery.
    
    Example:
    In the table EVENT DDL,
    "CREATIONDATE" DATE NOT NULL GENERATED ALWAYS AS
    (DATE(CREATIONTIME))  <------ condition 1
    
    SELECT COUNT(DISTINCT event.PROZESSID) AS ANZAHL,
    LEFT(CREATIONTIME,10) AS TAG    <------------ condition 2, 4
    from PMONITOR.EVENT as event
    WHERE event.CREATIONTIME >= '20120101000000' and
    event.CREATIONTIME < '20130101000000' AND   <--------- condition
    3
    NOT EXISTS( SELECT * FROM PMONITOR.EVENT as subEv WHERE
    subEv.EVENTNAME='VMDBREPLERGEBNIS' and
    subEv.PROZESSID=event.PROZESSID)   <------ condition 5
    GROUP BY LEFT(CREATIONTIME,10);
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.
    (Reason "Order cols removed from sort output cols.".)
    SQLSTATE=58004
    
    The agent doing the query compilation will also produce a stack
    with a signature similar to:
    
     sqlnn_cmpl[390]
    sqlno_exe[2000]
    sqlno_final_phase[100]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[200]
    sqlno_post_pass[1150]
    sqlno_post_order_near_norm[301]
    
    
    Function at the top of the stack to look for is
    sqlno_post_order_near_normalize
    

Local fix

  • 1. db2set DB2_ANTIJOIN=NO (Note this is only valid for MPP)
    2. Create redundant predicates in the query  '01/01/2012' <=
    event.CREATIONDATE and event.CREATIONDATE <= '01/01/2013'
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description.                                       *
    *                                                              *
    * Note this issue does not exist in V10.1 version of DB2 as    *
    * there are different query rewrite rules that get applied in  *
    * this version.                                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 and Fix Pack 9                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 9.7 and Fix Pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC90861

  • 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

    2013-03-14

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

  • 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

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC90861

Modified date: 16 December 2013