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 = " ". Explanation: 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 follows: 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 optimization. 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.
