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

Obtain the fix for this APAR.

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