|
Problem IC92118
|
Status: Closed |
INSTANCE CRASH IF A QUERY CONTAINS A SUB-SELECT STATEMENT WHICH INCLUDES ONE OR MORE OLAP FUNCTIONS |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem 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 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v10.1 FP3. *
**************************************************************** |
| 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. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Fixed in DB2 v10.1 FP3. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC96560
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.05.2013
02.10.2013
02.10.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
