IBM Support

IC72117: TRUNCATED SORT OF STAR JOIN DIMENSION TABLES CAUSES INCORRECT RESULTS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When a query uses Fetch First N Rows Only, and has a series of
    lossless Referential Integrety (RI) Joins, the optimizer may
    choose to use a truncated operator, such as a Truncated SORT or
    a Truncated Table Queue. The optimizer may then incorrectly
    choose to truncate the join of a series of dimension tables,
    leading to incorrect results.
    
    This can most commonly be identified by looking for the
    following argument on either a SORT:
      TRUNCSRT: (Truncated sort (for Fetch First n Rows Only))
       TRUE
    
    or a TABLE QUEUE (TQ) operator:
      TRUNCTQ : (Truncated Table Queue (for Fetch First n Rows
    Only))
       <value>
    where <value> could be any one of "INPUT", "OUTPUT" or "INPUT
    AND OUTPUT"
    
    If either of these operators occur in a plan that contains only
    the dimension tables, then the plan may return incorrect
    results, if the join between the dimension tables produces more
    tuples then the truncated operator will allow to be produced.
    

Local fix

  • Remove Fetch First N Rows from the query
    

Problem summary

  • APAR fix was provided in Fixpack 4.
    

Problem conclusion

  • The fix ensures correct result set is returned for the case in
    question.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72117

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-10-22

  • Closed date

    2011-05-09

  • Last modified date

    2011-05-09

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

    LI75724

  • 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

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC72117

Modified date: 09 May 2011