PM46986: INCORRECT OUTPUT FROM SELECT WITH IN LIST AFTER UPGRADING TO DB2 9

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • User is upgrading to DB2 9 from V8 and performs a query with
    an IN-LIST and fewer records than are expected are returned.
    DB2 is dropping the condition of the last value in the IN-LIST.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: Users of DB2 for z/OS executing queries      *
    *                 that have multiple IN-list predicates        *
    *                 on the same table.                           *
    ****************************************************************
    * PROBLEM DESCRIPTION: THE PROBLEM THIS FIX ADDRESSES HAS THE  *
    *                      FOLLOWING REPORTED SYMPTOMS:            *
    *                                                              *
    *                       INCORRECT OUTPUT                       *
    *                                                              *
    *                      PROBLEM SCENARIO:                       *
    *                                                              *
    *                      Too few rows are returned when          *
    *                      executing a query that has multiple     *
    *                      IN-list predicates on the same table.   *
    *                      Here is an example:                     *
    *                                                              *
    *                      SELECT *                                *
    *                       FROM T1 TAB1,                          *
    *                            T2 TAB2                           *
    *                       WHERE                                  *
    *                            TAB1.COL1 = TAB2.COL1             *
    *                        AND TAB1.COL2 = TAB2.COL2             *
    *                        AND                                   *
    *                        (                                     *
    *                         (TAB1.COL3 IN ('A','C','S','P')      *
    *                          AND                                 *
    *                          TAB1.COL4 IN (' #','PO','TR','CS')  *
    *                          AND                                 *
    *                          TAB2.COL3 = 'Z'                     *
    *                         )                                    *
    *                         OR                                   *
    *                         (TAB1.COL3 IN ('A','C','S','P')      *
    *                         AND                                  *
    *                         TAB1.COL4 IN (' #','LA','SR')        *
    *                         AND                                  *
    *                         TAB2.COL3 = 'X'                      *
    *                         )                                    *
    *                        )                                     *
    *                      ;                                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    SUMMARY OF DB2 EVENTS:
    DB2 was attempting to remove redundant predicates and
    removed an IN-list predicate that was not supposed to be
    removed.
    

Problem conclusion

  • PROBLEM CONCLUSION:
    DB2 now correctly removes redundant predicates that are not
    IN-list.
    
    ADDITIONAL KEYWORDS:
    SQLIN INCORROUT SQLINCORR DB2INCORR/K
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM46986

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-09-01

  • Closed date

    2011-10-11

  • Last modified date

    2011-11-02

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

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

    UK72798

Modules/Macros

  •    DSNXOPPD
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK72798

       UP11/10/26 P F110

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM46986

Modified date:

2011-11-02

Translate my page

Machine Translation

Content navigation