IBM Support

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

Subscribe

You can track all active APARs for this component.

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 users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.5.0.1.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.5.0.1.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC94298

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-22

  • Closed date

    2013-08-23

  • Last modified date

    2013-08-23

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

    IC93059

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA50 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.5

Reference #: IC94298

Modified date: 23 August 2013