IC93059: RANGE PARTITIONED TABLES DEFINED WITH A NULLS FIRST PARTITIONING COLUMN MIGHT RETURN INCORRECT RESULTS

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

APAR status

• Closed as program error.

Error description

• 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.
  

Local fix

• 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'
  

Problem summary

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

Problem conclusion

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

Temporary fix

Comments

APAR Information

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

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

  IC94297 IC94298

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP