Creating Proper Statistics on Siebel EIM Tables

This topic is part of Troubleshooting Siebel EIM Performance.

On IBM DB2, you can use the IFB file parameter UPDATE STATISTICS to control whether Siebel EIM dynamically updates the statistics of EIM tables. The default setting is TRUE. This parameter can be used to create a set of statistics on the EIM tables that you can save and then reapply to subsequent runs. After you have determined this optimal set of statistics, you can turn off the UPDATE STATISTICS parameter in the IFB file (UPDATE STATISTICS = FALSE), thereby saving time during the Siebel EIM runs.

Note: It is recommended to manage statistics manually. Also note that using UPDATE STATISTICS in IFB executes RUNSTATS in SHRLEVEL REFERENCE mode, which causes exclusive locks and can prevent parallel EIM execution.

To determine the optimal set of statistics, you need to run several test batches and RUNSTATS commands with different options to see what produces the best results.

Before and after each test, execute the db2look utility in mimic mode to save the statistics from the database system catalogs. For example, if you are testing Siebel EIM runs using EIM_CONTACT1 in database SIEBELDB, then the following command generates UPDATE STATISTICS commands in the file EIM_CONTACT1_mim.sql:

db2look -m -a -d SIEBELDB -t EIM_CONTACT1 -o EIM_CONTACT1_mim.sql

The file EIM_CONTACT1_mim.sql contains SQL UPDATE statements to update database system catalog tables with the saved statistics. You can experiment with running test Siebel EIM batches after inserting the RUNSTATS commands provided in IBM DB2 Options. After you find the set of statistics that works best, you can apply that particular mim.sql file to the database. Between runs, save statistics with db2look.

Note: The db2look utility runs on IBM DB2 for Linux, Unix, and Windows (most of the IBM DB2 references in this guide are to this product). On IBM DB2 for z/OS, you can use the Optimization Service Center (OSC) utility instead. For more information about using Siebel EIM with IBM DB2 for z/OS, see IBM DB2 for z/OS and Siebel EIM.