Implementing Siebel Business Applications on DB2 UDB for z/OS > Maintenance Considerations for z/OS >

DB2 Statistics for Siebel Business Applications

It is recommended that you apply statistics to Siebel applications. Update statistics on table spaces that contain EIM interface tables and base tables (including table spaces that contain extension tables and repository tables) when there has been a change of 20 percent or more in the row distribution. It is usually not necessary to update statistics on all of the table spaces, only on those containing tables that have changed.

The RUNSTATS utility and the DSTATS utility are recommended for updating statistics.

About the RUNSTATS Utility

In DB2 for z/OS v8, you can use RUNSTATS to collect distribution and frequency statistics for all types of columns (indexed and nonindexed) and for user-defined groups of columns. You can use RUNSTATs to collect statistics on the most and least frequently occurring values, and to collect cardinal values for groups of columns.

In DB2 for z/OS v7, you can use RUNSTATS to collect distribution statistics, but only in certain cases: for example, only for columns which form the senior key of an index, and only for the most (rather than least) frequently occurring values.

Execution of the rstat390 utility from midtier executes RUNSTATS on the DB2 host. The rstat390 script is located within the SIEBSRVR_ROOT\bin directory (Windows) or the $SIEBSRVR_ROOT/bin directory (UNIX). For rstat390 help, use option /h.

Update statistics only when there is little activity on the system, such as after midnight. If you run this utility while users are accessing and updating the Siebel database, lock contention can occur. When this happens, an error message is generated, for example, the following error message is generated from rstat390.

ODBC error S1000 in SQLExecDirect: [IBM][CLI Driver][DB2/6000] SQL2310N The utility could not generate statistics. Error "-911" was returned.

This error does not indicate that your database has been harmed; however, you do have to rerun the RUNSTATS job for any table for which this type of error is generated, because statistics were not updated for that table.

You can execute RUNSTATS on an active system if you specify shrlevel change as an option. This option allows concurrent access while the RUNSTATS utility executes. You can also execute RUNSTATS directly from the DB2 host.

When to Use the DSTATS Utility

You can execute RUNSTATS following the reorganization of any fragmented table spaces and indexes. For more information on this topic, see About Reorganizing Table Spaces, Partitions, and Indexes.

If you are experiencing slow queries, however, run the DSTATS utility. Slow queries can result when the optimizer chooses an inefficient access path as a result of data skew on nonleading indexed columns or nonindexed columns. In such cases, IBM recommends running the DSTATS utility to collect column distribution statistics on these columns; this can lead to significant improvements in the query time. For example, on a query with search predicate PRIV_FLG = 'N', Siebel Systems has seen an improvement from 0.5 seconds to 0.04 seconds with column distribution statistics collected on S_CONTACT.PRIV_FLG.

For further information on DSTATS, see Siebel SupportWeb.