PH28050: R-SCAN IS USED WHEN MATCHING INDEX EXISTS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In Db2 12, table in subquery may use R-scan when a matching
  index exists.
  
  A problem query looks like:
  
  SELECT...
  FROM T1, T2, T3, T4
  WHERE T1.C1 = T2.C1
    AND T2.C2 = T3.C2
    AND T1.C4 = T4.C4
    AND T4.C4 IN
         (SELECT T11.C4
            FROM T1 T11
           WHERE T11.C1=?)
  ;
  
  Index I1(C1) provides matching index access to table T1 in the
  subquery. T11.C1=? has good filter factor, the ideal access path
  is the matching index access. But Db2 may not use this index, or
  even choose R-scan.
  ZSA45
  

Local fix

• BYPASS/CIRCUMVENTION:
  OPTHINT
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users who have a query                   *
  * including a IN or EXISTS subquery                            *
  * predicate.                                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Db2 may select an inefficient access                         *
  * path for a subquery which is under                           *
  * a IN or EXISTS predicate.                                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  If a query contains a IN or EXISTS subquery predicate, Db2 may
  do global optimization for the query, and select an inefficient
  access path for the subquery.
  Example:
  SELECT * FROM T1
  WHERE T1.C1 IN
         (SELECT T2.C1
            FROM T2
           WHERE T2.C2=?)
  ;
  There are two indexes on T2, IX1(C1,C2), IX2(C2). Db2 may do
  global optimization for this query, and add a predicate as
  T2.C1=T1.C1 in the subquery. Db2 may prune IX2 because it is a
  subset of IX1. It results in Db2 selecting RSCAN to access the
  non-correlated subquery, while IX2 can provide better
  performance.
  Additional keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLIN SQLINSUBQ SQLEXISTS
  SQLNONCORRSUBQ SQLCORRSUBQ SQLGLOBALOPT
  IDAAV7R1/K IDAAV7R5/K IDAAV5R1/K
  

Problem conclusion

• Code is updated to select an efficient access path for the
  subquery.
  Users of IBM Db2 Analytics Accelerator are strongly encouraged
  to apply this fix, in conjunction with PH21726, to reduce
  possible spikes in offload activity.
  

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:

  UI71492

Modules/Macros

• DSNXOPRP DSNXOPIX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI71492

     UP20/09/18 P F009

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.