IZ45757: EXP0021W INCORRECTLY RETUNED BY EXPLAIN FOR STATEMENTS INVOLVING MQTS

DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• When explaining a statement where the optimizer evaluates MQTs,
  an incorrect EXP0021W warning may be raised and stored into the
  explain diagnostic tables.
  
  For example, the explain output from db2exfmt might show the
  following message:
  
  Diagnostic Details: EXP0021W  Table column has no
  statistics.  The column "E6811_ORDENDES" of table
  "DBA_OWNER"."FK_TR_CLICONTR" has not had runstats
  run on it. This can lead to poor cardinality and
  predicate filtering estimates.
  
  even if runstats has been run on the table.
  
  The problem can be reproduced with the following:
  
  1. db2 "create table testmqt as (select * from employee E,
  sales S where S.sales_person=E.LASTNAME) data initially
  deferred refresh deferred enable query optimization"
  
  2. db2 refresh table testmqt
  
  db2 "set current refresh age =99999999999999"
  
  3. db2 "runstats on table db2inst1.employee with distribution
  on all columns and detailed indexes all"
  
  db2 "runstats on table db2inst1.sales with distribution on all
  columns and detailed indexes all"
  
  db2 "runstats on table db2inst1.testmqt with distribution on
  all columns and detailed indexes all"
  
  4. db2 "explain plan for select * from employee E, sales  S
  where S.sales_person=E.LASTNAME and
  sales_date='03/30/2006'"
  
  The formatted plan shows the following warning message even
  though runstats was executed in the previous step :
  
  Extended Diagnostic Information:
  --------------------------------
  Diagnostic Identifier: 1
  Diagnostic Details: EXP0021W  Table column has no
  statistics.  The column "SALES_DATE" of table "DB2INST1".
  "SALES" has not had runstats run on it.  This can
  lead to poor cardinality and predicate filtering estimates.
  

Local fix

• The incorrect warning can be ignored.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * EXP0021W INCORRECTLY RETUNED BY EXPLAIN FOR STATEMENTS       *
  * INVOLVING                                                    *
  * MQTS                                                         *
  *                                                              *
  * When explaining a statement where the optimizer evaluates    *
  * MQTs,                                                        *
  * an incorrect EXP0021W warning may be raised and stored into  *
  * the                                                          *
  * explain diagnostic tables.                                   *
  *                                                              *
  * For example, the explain output from db2exfmt might show the *
  *                                                              *
  * following message:                                           *
  *                                                              *
  * Diagnostic Details: EXP0021W  Table column has no            *
  * statistics.  The column "E6811_ORDENDES" of table            *
  * "DBA_OWNER"."FK_TR_CLICONTR" has not had runstats            *
  * run on it. This can lead to poor cardinality and             *
  * predicate filtering estimates.                               *
  *                                                              *
  * even if runstats has been run on the table.                  *
  *                                                              *
  * The problem can be reproduced with the following:            *
  *                                                              *
  * 1. db2 "create table testmqt as (select * from employee E,   *
  * sales S where S.sales_person=E.LASTNAME) data initially      *
  * deferred refresh deferred enable query optimization"         *
  * 2. db2 refresh table testmqt                                 *
  * db2 "set current refresh age =99999999999999"                *
  * 3. db2 "runstats on table db2inst1.employee with             *
  * distribution                                                 *
  * on all columns and detailed indexes all"                     *
  * db2 "runstats on table db2inst1.sales with distribution on   *
  * all                                                          *
  * columns and detailed indexes all"                            *
  * db2 "runstats on table db2inst1.testmqt with distribution on *
  * all columns and detailed indexes all"                        *
  * 4. db2 "explain plan for select * from employee E, sales  S  *
  * where                                                        *
  * S.sales_person=E.LASTNAME and sales_date='03/30/2006'"       *
  * The formatted plan shows the following warning message even  *
  *                                                              *
  * though runstats was executed in the previous step :          *
  *                                                              *
  * Extended Diagnostic Information:                             *
  * --------------------------------                             *
  * Diagnostic Identifier: 1                                     *
  * Diagnostic Details: EXP0021W  Table column has no            *
  * statistics.  The column "SALES_DATE" of table "DB2INST1      *
  * "."SALES" has not had runstats run on it.  This can          *
  * lead to poor cardinality and predicate filtering estimates.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Version 9.1 Fix Pack 8                            *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in Version 9.1 Fix Pack 8
  

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:

  IZ45758

Fix information

• Fixed component name

  DB2 UDB ESE HP-

• Fixed component ID

  5765F4103

Applicable component levels

• R910 PSY

     UP