PH53600: BIND/REBIND WITH APREUSE MIGHT FAIL IF AP CONTAINS RANGE LIST INDEX SCAN WHICH CAN AVOID SORT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• For BIND/REBIND with APREUSE or OPTHINT, there might be issue
  for comparing/picking up the specific access path when below
  conditions are satisfied:
  1. Query contains DISTINCT
  2. The specified access path contains range list index scan
  (accesstype='NR') which can avoid sort
  
  BIND/REBIND APREUSE(WARN) will report the above stmt as
  STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL OR PARTIALLY
  SUCCESSFUL in message DSNT286I and generate a new access path
  which might be identical to the original specified access path.
  REBIND APREUSE(ERROR) will fail with the above stmt identified
  as STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL OR
  PARTIALLY SUCCESSFUL.
  
  BIND/REBIND OPTHINT will report the above stmt as STATEMENTS
  WHERE OPTHINT FULLY APPLIED in message DSNT222I and generate a
  new access path which contains extra sort mini plan comparing
  the original specified access path.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 and Db2 13 for z/OS who                  *
  * BIND/REBIND package with APREUSE or                          *
  * OPTHINT to reuse an access path containing                   *
  * range list access type.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * For BIND/REBIND with APREUSE or                              *
  * OPTHINT, there might be an issue for                         *
  * comparing/picking up the specific                            *
  * access path when the below conditions                        *
  * are satisfied:                                               *
  * 1. Query contains DISTINCT.                                  *
  * 2. The specified access path contains                        *
  * range list index access(accesstype =                         *
  * 'NR') which can avoid sort for                               *
  * distinct.                                                    *
  * BIND/REBIND APREUSE(WARN) will report                        *
  * the above statement as STATEMENTS                            *
  * WHERE APREUSE IS EITHER NOT SUCCESSFUL                       *
  * OR PARTIALLY SUCCESSFUL in message                           *
  * DSNT286I and generate a new access                           *
  * path which might be identical to the                         *
  * original specified access path.                              *
  * REBIND APREUSE(ERROR) will fail with                         *
  * the above statement identified as                            *
  * STATEMENTS WHERE APREUSE IS EITHER NOT                       *
  * SUCCESSFUL OR PARTIALLY SUCCESSFUL.                          *
  * BIND/REBIND OPTHINT will report the                          *
  * above statement as STATEMENTS WHERE                          *
  * OPTHINT FULLY APPLIED in message                             *
  * DSNT222I and generate a new access                           *
  * path which contains extra sort mini                          *
  * plan when compared to the original                           *
  * specified access path.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  SELECT DISTINCT C2
  FROM T1
  WHERE T1.C1 = 1 AND
        (T1.C2 LIKE '19%' OR T1.C2 LIKE '29%') ;
  Index IX1 on (C1, C2) is chosen with range list access type.
  Sort for distinct can be avoided.
  When running BIND/REBIND with APREUSE or OPTHINT to reuse the
  old access path, Db2 can't recognize that index IX1 can avoid
  sort for distinct and build a sort miniplan that can result in
  the below error:
  BIND/REBIND APREUSE(WARN) will report the above statement as
  STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL OR PARTIALLY
  SUCCESSFUL in message DSNT286I and generate a new access
  path which might be identical to the original specified
  access path.
  REBIND APREUSE(ERROR) will fail with the above statement
  identified as STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL
  OR PARTIALLY SUCCESSFUL.
  BIND/REBIND OPTHINT will report the above statement as
  STATEMENTS WHERE OPTHINT FULLY APPLIED in message DSNT222I and
  generate a new access path which contains an extra sort mini
  plan when compared the original specified access path.
  

Problem conclusion

• Db2 has been modified to correctly process the aforementioned
  SQL statement.
  Additional Keywords SQLDISTINCT OPTHINT OPTHINTS APREUSE
  MSGDSNT286I MSGDSNT222I SQLACCESSPATH
  

Temporary fix

Comments

APAR Information

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

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

  UI91612 UI91613

Modules/Macros

• DSNXOPTH
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI91612

     UP23/05/09 P F305

• RD10 PSY UI91613

     UP23/05/09 P F305

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.