PL19577: OPTIMIZER DOES NOT USE AVAILABLE INDEX

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• RUNTIME PERFORMANCE DEGRADED FOR AN APPLICATION UNDER DB2
  RELEASE 3 BECAUSE OPTIMIZER DOES NOT CHOOSE AN AVAILABLE INDEX
  WHICH WAS USED WHEN THE PLAN WAS BOUND UNDER RELEASE 2. THE
  APPLICATION AND USER TABLE ARE UNCHANGED BETWEEN RELEASES. THE
  DIFFERENCE IN PERFORMANCE IS LARGE. UNDER RELEASE 2 THE QUERY
  USED 1.5 CPU SECONDS AND TOOK 10 SECONDS ELAPSED TIME AGAINST A
  26K ROW TABLE. UNDER RELEASE 3, THE QUERY USES 58 CPU MINUTES
  AND TAKES 5.5 HOURS ELAPSED TIME. EXPLAIN ALSO INDICATES THAT
  THE INDEX IS NOT USED UNDER RELEASE 3.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:    NON-CLUSTERED INDEX IS NOT           *
  *                      SELECTED FOR ACCESS PATH.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
       A NON-CLUSTERED INDEX IS NOT SELECTED FOR ACCESS PATH
  IN DB2 REL. 3 THAT WAS SELECTED IN DB2 REL. 2.
  ...
      SQL STATEMENT WAS SIMILAR TO THE FOLLOWING:
     SELECT COL1, COL2, COL3, .....
           FROM TABLE
             WHERE COL2 >= :HOSTVAR
                ORDER BY COL2 :
  ...
     INDEX DEFINED ON (COL2)
  ...
      IN DB2 REL. 3 SORT COST ESTIMATES WERE LOWERED TO MORE
  ACCURATELY REFLECT IMPROVED SORT PERFORMANCE.  THE PROBLEM
  IS THAT IN THE CASE OF NON-CLUSTERED INDEXES (CLUSTERED='N'
  IN SYSIBM.SYSINDEXES) THE 'ORDER BY' WAS AN EFFECTIVE
  CIRCUMVENTION TO FORCE THE USE OF THE INDEX BECAUSE OF THE
  HIGH SORT COST ESTIMATE (PL16683 AND PL10106 DESCRIBE SIMILAR
  PROBLEMS WITH NONCLUSTERED INDEXES).
  

Problem conclusion

• THE SORT COST ESTIMATE IN THE REL. 3 VERSION OF DSNXOOS IS
  CHANGED TO GIVE SAME RESULT AS THE REL. 2 VERSION OF DSNXOSS.
  

Temporary fix

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

Comments

APAR Information

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

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

  UL24240 UL27031 UL28454 UL29446 UL32542

Modules/Macros

•    DSNXOSS
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R302 PSY UL24240

     UP88/01/18 P DLL5 F802

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.