PM73214: TOTAL_COST 0 EVALUATED FOR QUERIES WITH SET OPERATIONS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This costing bug may be  causing access path degradation.
  May be most visible in monitored DB2, or with use of RLF
  or IDAA, where it gives unexpected access path (or query
  does not offload)
  QUERY is not being routed to IDAA when it appears that it should
  be. Instead there is misleading info in EXPLAIN TABLE:
  
  (QI_DATA from DSN_QUERYINFO_TABLE) is "301 The query is a DB2
  short
  running query or offloading is not advantageous".
  The query is a select statement off of a views that contains 25
  tables
  for a total of around 5.7 billion rows.  Of those 25 tables, 24
  of the
  have no indexes on them at all.
  
  so this definitely is not "short running query"
  
  Keywords:
  IDAAV2R1/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 9 and 10 for z/OS users of queries with  *
  *                 UNION ALL.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: The total cost of an access path may    *
  *                      not be accurately estimated for a query *
  *                      when the query contains a view or       *
  *                      table expression with UNION ALL.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The total cost of an access path may not be accurately estimated
  for a query when the query contains a view or table expression
  with UNION ALL and the view or table expression satisfies all
  the following conditions:
  
  1. The parent query block of the view or table expression
  requires a sort operation; for example, it contains an ORDER BY
  or GROUP BY clause.
  
  2. The parent query block of the view or table expression
  references this view or table expression only.
  
  3. There is no local predicate in the parent query block of the
  view or table expression or all the local predicates in the
  parent query block of the view or table expression can be pushed
  down into the view or table expression.
  
  For example,
  
  SELECT A.C1, A.C2, A.C3
  FROM (SELECT C1, C2, C3
        FROM T1
        UNION ALL
        SELECT C1, C2, C3
        FROM T2) A
  ORDER BY A.C1, A.C2, A.C3;
  
  The cost of the table expression A may not be rolled up into
  the total cost of the above query.
  
  The problem may prevent a query from being offloaded to
  IBM DB2 Analytics Accelerator.
  
  Additional keywords:
  SQLACCESSPATH SQLPERFORMANCE SQLEXPLAIN SQLUNIONALL
  IDAAV2R1/K IDAAV3R1/K
  

Problem conclusion

• Code has been modified to correct the cost estimation of the
  queries described as above.
  

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:

  UK90219 UK90220

Modules/Macros

• DSNXOTS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90219

     UP12/12/27 P F212

• R910 PSY UK90220

     UP12/12/27 P F212

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.