PQ55393: SELECT FROM VIEW - TABLESPACE SCAN IS SELECTED INSTEAD INDEX SCAN.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SELECT from view - tablespace scan is selected instead
  index scan.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL DB2 USERS.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: Table scan is used instead of index     *
  *                      scan and result into performance        *
  *                      issue.                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Table scan is used instead of index scan for the case of
  union all in view with host variable predicate.
  i.e.
    CREATE INDEX T1IX ON T1(C1, C2);
    CREATE INDEX T2IX ON T2(C1, C2);
  
    CREATE V1(C1, C2) AS
      SELECT T1.C1, T1.C2
      FROM T1
      WHERE T1.C2 = '08/31/01'
     UNION ALL
      SELECT T2.C1, T2.C2
      FROM T2
      WHERE T2.C2 = '08/30/01');
  
    SELECT V1.C1
    FROM V1
    WHERE V1.C1 > :HV1 AND V1.C2 > :HV2;
  
  In V7, there are two problems in this case:
  1. Host variable predicate is not push down for union all
     in view which result into table scan instead of index scan.
     For the above example, if V1.C1 in not push down,
     Index T1IX and T2IX will not be used for index scan.
  2. Union all distribution is disabled with host variable.
     For the above example, an extra work file will be created.
  

Problem conclusion

• DB2 is modified to support preodicate push down for union
  in view and distrition for host variable.
  Additional KEYWORDS: SQLUNION, SQLHOSTVAR, SQLPRUNING,
  SQLUNIONALL, SQLACCESSPATH
  

Temporary fix

Comments

• ×**** PE02/03/07 FIX IN ERROR. SEE APAR PQ58628  FOR DESCRIPTION
  

APAR Information

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

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

  UQ62215

Modules/Macros

•    DSNXND   DSNXOB2  DSNXOCPT DSNXODSO DSNXOLPR
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R710 PSY UQ62215

     UP02/02/25 P F202

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.