DB2 FOR LUW / DB2FORLUW / A10 - DB2

Queries using a range partitioned table defined with a NULLS 
FIRST partitioning column, a partitioned index, and NULL values 
in the data might return unordered data or might be missing data 
from the result set. 
 
Unordered data: 
 
When the query contains no predicates on the column defined as 
NULLS FIRST and specifies an ORDER BY clause containing the 
columns of the partitioned index, then the NULL values are 
interspersed with other data from the table instead of being 
output at the end of the result set. 
 
The partitioned index must be used in the access plan and the 
access plan must not contain a SORT. 
 
Missing Data: 
 
When the partitioned index is used and the NULLS FIRST column is 
used as a GAP column, some of the query results might be omitted 
from the result set. A GAP column is typically present due to a 
missing predicate on that column of the index. 
 
Looking at the access plan produced by db2exfmt, the operator 
details for the IXSCAN will show 
 
  JUMPSCAN: (Jump Scan Plan) 
   TRUE 
 
and 
 
  Gap Info:            Status 
  ---------            ------ 
  Index Column 1:      Gap 
  Index Column 2:      No Gap 
 
In the example, when the NULLS FIRST column is Index Column 1, 
the result set might be incorrect.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 LUW version 10 Fix Pack 3.                     * 
****************************************************************

Drop the relevant index or convert it a non-partitioned index. 
 
For unordered data, change the sequence or direction of columns 
used in the ORDER BY clause so that they do not exactly match to 
the index. 
 
For the missing data, disable JUMPSCAN by using 
DB2_REDUCED_OPTIMIZATION='JUMPSCAN OFF'

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
 

Problem was first fixed in DB2 LUW version 10 Fix Pack 3

not known / see Local fix

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

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

13.06.2013
27.09.2013
27.09.2013