Siebel Installation Guide for UNIX: Servers, Mobile Web Clients, Tools > Guidelines for Configuring the RDBMS >

Managing DB2 UDB Table Fragmentation

No strict guidelines can be offered as to which tables and indexes may be fragmented due to the variety in application and customer operation variables at any given customer site. However, database administrators (DBAs) should pay attention to the status of large or heavily used tables, because fragmentation of these tables can affect performance significantly. For a list of these Siebel tables, see the Deployment Planning Guide.

You should not reorganize S_ESCL_LOG, S_DOCK_INIT_ITEM, S_ESCL_ACTN_REQ, S_APSRVR_REQ, and all S_DOCK_INITM_%% tables (where % is a digit), because these tables are defined to be in append mode.

Use the following strategy to manage table fragmentation:

• Run REORGCHK on heavily used tables, and then review the resulting reports and extract list of any fragmented objects.
• Based on the results of REORGCHK, reorganize any tables, as needed, by running REORG TABLE. For details on how to reorganize tables or indexes, see FAQ 2072 and FAQ 2073 on Siebel SupportWeb.
• After table reorganization, update statistics by using the runstats utility on any reorganized tables with the following minimum parameters:

  runstats on table tablename with distribution and detailed indexes all shrlevel change

  You may add other parameters as required, but use the shrlevel change parameter to allow concurrent access to your tables while runstats executes.

  CAUTION:  Because the runstats utility overwrites statistics loaded by Siebel applications, if you use runstats, you should always execute loadstats.sql afterwards, using either DB2 CLP or odbcsql. Otherwise, valuable statistics will be lost.

To run loadstats.sql using odbcsql, use the following command:

odbcsql /s DATASOURCE_NAME /u username /p password /v separator /siebsrvr_root/dbsrvr/db2udb/loadstats.sql TABLEOWNER_NAME