IBM Support

IC91957: INSTANCE CRASH IF A QUERY CONTAINS A SUB-SELECT STATEMENT WHICH INCLUDES ONE OR MORE OLAP FUNCTIONS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Instance can crash if any DML statement (SELECT, INSERT, UPDATE,
    DELETE, MERGE, etc)  meets the following conditionss:
    
    A given sub-select within the query must contain:
    
    1) One or more sub-queries which generally is a predicate
    similar to EXISTS(SELECT ...), IN (SELECT ...), quantified
    predicates using the ALL or ANY qualifiers ie column >=
    ALL(SELECT ..)
    
    1a) The sub-query must use a column not produced by a table in
    the FROM clause of the sub-query.
    
    
    2) The sub-select must contain one or more OLAP functions,
    typically OLAP functions have an OVER(..) clause associated with
    the function and,
    
    2a) At least one of the OLAP functions is involved in a
    predicate or other complex expression.
    
    OR
    
    2b) ANY two of the OLAP functions have OVER clauses in which the
    ordered list of columns from the first are not a superset or
    subset of the ordered list of columns from the second, for
    example 'OVER(PARTITION BY C1, C2 ORDER BY C3)' is incompatible
    with 'OVER( PARTITION BY C4)' because they share no common
    columns.
    
    
    The stack of the trap file generated would be as follows:
    
    sqlnq_qunqncchild::pp
    sqlno_outer_reference
    sqlnx_exp_process_correlated_col_info
    sqlnx_exp_FILTER_analyze
    sqlnx_exp_analyze_plan
    sqlnx_exp_analyze_plan
    

Local fix

  • Change the SQL statement to separate the sub-queries and other
    predicates into a sub-select that provides input to the OLAP
    function.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v9.7 FP9                                      *
    ****************************************************************
    

Problem conclusion

  • Problem fixed in DB2 v9.7 FP9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC91957

  • 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-04-29

  • Closed date

    2013-12-23

  • Last modified date

    2013-12-23

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

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

    IC92118

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R980 PSN

       UP

  • RA10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 December 2013