DB2 FOR LUW / DB2FORLUW / 970 - DB2

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

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

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

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem fixed in DB2 v9.7 FP9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC92118 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

29.04.2013
23.12.2013
23.12.2013

9.7.FP9