PM80911: ENHANCE THE EXISTING OPTIMIZER FOR "CONSTANT <> CONSTANT" PRUNING (WHERE 1<>1)

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • queries that contain a boolean 1<>1 predicate will be pruned
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of       *
    *                 constant <> constant predicates              *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR fixes the problem             *
    *                      when a non correlated SELECT SQL        *
    *                      statement is executed, rows in a        *
    *                      table are accessed even though no       *
    *                      rows are to be returned because         *
    *                      there is a basic predicate that is      *
    *                      false as a not equal comparison         *
    *                      between two constants that have         *
    *                      the same values.                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR enhances the performance of a non correlated
    SQL statements with predicates involving constant
    comparisons.
    
    The performance of a non correlated  SELECT statement can be
    improved if it has a predicate that always evaluates to
    false because it is a not equal comparison between two
    constants that have the same values. The query can be executed
    without accessing the tables in the FROM clause because
    the predicate is always evaluated to false
    
    For example,
    
       CREATE TABLE T1 (C1 INT);
       INSERT INTO T1(c1) VALUES (123);
    
       SELECT * FROM T1 WHERE 1 <> 1;
    
    The above query will be flagged as returning no row because
    the predicate 1 <> 1 is always evaluated to false.Therefore the
    table T1 will not be accessed.
    

Problem conclusion

Temporary fix

Comments

  • DB2 is changed to flag a non correlated query with an always
    false predicate that involves a not equal comparison between
    two constants that have the same values and thereby avoid
    accessing rows in the table.
    
    Additional Keywords:  SQLNOROW  SQLPRUNING
    
    ž**** PE13/04/19 FIX IN ERROR. SEE APAR PM87166  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PM80911

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-01-18

  • Closed date

    2013-03-18

  • Last modified date

    2013-06-17

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

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

    UK92607 UK92608

Modules/Macros

  •    DSNXOPRU
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK92607

       UP13/04/03 P F304

  • R910 PSY UK92608

       UP13/04/03 P F304

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

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM80911

Modified date:

2013-06-17

Translate my page

Machine Translation

Content navigation