Care and Feeding of Statistical Views

Posted by

The whole point of statistical views is to improve the performance of SQL that joins tables. Or to help you collect statistics on expressions – though if you’ve got 10.5, consider expression-based indexes for that instead. Giving the DB2 Optimizer more data is generally a good thing for query performance.

If you don’t keep runstats current on your statistical views, they won’t be much help. In DB2 9.7, automated statistics cannot be collected on statistical views. I’m not a fan of automated runstats anyway, though it may be time for me to give automated runstats another chance. It appears that automatic statistics includes statistical views in DB2 10.1.

In any case, it is important to know how to identify statistical views. You can come up with a list of statistical views using a query like this one:

db2 "select tabschema, tabname from syscat.tables where type='V' and substr(property,13,1) = 'Y' with ur"
TABSCHEMA   TABNAME                                                                                                                         
---------   ---------------------
WSCOMUSR    STATS_SEARCH_VIEW_01                                                                                                            

  1 record(s) selected.

Using syntax like that, you can generate a list of statistical views to do runstats on. I was hoping I could just pop them into my normal list of tables in a script, but the problem that I run into there is that my script uses “and detailed indexes all”. When you try to use that on a statisical view you get:

$ db2 "runstats on table wscomusr.STATS_SEARCH_VIEW_01 with distribution and detailed indexes all"
SQL20288N  Statistics could not be updated for object "STATS_SEARCH_VIEW_01" 
of type "Statistical View". Reason code = "2".  SQLSTATE=428DY

Looking up that error:

$ db2 ? SQL20288N

SQL20288N  Statistics could not be updated for object "" of
      type "". Reason code = "".


Statistics could not be set for the target object of RUNSTATS or UPDATE
for the reason given by the reason code. Possible reason codes are as

1. Statistics are not supported for the target object type.
2. The RUNSTATS options specified are prohibited on a view.
3. Update on certain statistics of the target object type is not
   supported. Data page statistics is not applicable for XML paths
   indexes. For more information on certain statistics that cannot be
   updated please see SYSSTAT.INDEXES catalog view description in the
   SQL Reference.

The statement cannot be processed.

User response: 

1. Verify that the object name specified as the target of the RUNSTATS
   or UPDATE can have statistics.
2. Remove RUNSTATS options that are prohibited on a view enabled for
3. Verify that the statistics of the target object type can be updated.

sqlcode: -20288

 sqlstate: 428DY

Dang, guess my script will require some more significant re-writing. I wish IBM had made that a warning instead of an error.

In 9.7, I can use this syntax on my statiscical view:

$ db2 "runstats on table wscomusr.STATS_SEARCH_VIEW_01 with distribution"
DB20000I  The RUNSTATS command completed successfully.

Reading, I see that DB2 supports the “VIEW” keyword in place of “table” on 10.1 and higher, so I presume this would work on 10.1:

$ db2 "runstats on view wscomusr.STATS_SEARCH_VIEW_01 with distribution"

I also find it slightly annoying that determining if a view is a statistical view requires looking at the thirteenth position of the PROPERTY column – seems like a pretty basic property that deserves its own column.

Statistical views were fully implemented in either 9.5 or 9.7, but there was some functionality that you could very painfully move back as far as 8.2 if you’re stuck on an old version that is still supported, IBM should be able to give you the details on how to do that. I was never desperate enough to do it.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.