Database tuning

Edit online

Use the following methods to tune the database.

Update database statistics

Make sure that the database statistics for the Library Server and Resource Manager databases are up to date. Often, this is enough to improve performance.

For more information about how to do this, see Optimizing server databases.

Create database indexes

For user-defined item attributes that are frequently used for searching, creating an index often improves response time and reduces resource usage. (Indexes on this type of attribute are not generated automatically.)

To create an index for an attribute

  1. Create an index for an attribute by using the IBM® Content Manager system administration client.

    The client creates an index that contains a column for the user attribute, and other columns that improve the of database joins.

  2. Run the reorg, runstats, and rebind commands to make sure that the Db2 UDB optimizer uses the new index effectively.

The usefulness of database indexes depends on the type of query. For example, wildcards at the beginning of a search term (such as @Title LIKE "%Java%") might prevent the database from effectively using the index (see also Avoid wildcards at the start of strings with the LIKE operator).

General indexes for the library server database and the resource manager database, and indexes for new item types are created automatically.

Optimize an Oracle database

A database table can become fragmented after many updates. Queries might take longer because the index entries in the library server and resource manager are no longer synchronized with the data that is in the database tables. Use the tools that are provided by Oracle to update the indexes and re-order them.

The following SQL commands might improve performance. To use the commands, log in as sysdba. Work with your database administrator to create a batch process that runs these SQL commands regularly.

Library server

Replace ICMADMIN with the library server database administrator ID.

execute dbms_stats.gather_schema_stats (ownname=>'ICMADMIN', method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL', options=>'GATHER', cascade=>TRUE, degree=>16);
execute dbms_stats.gather_schema_stats (ownname=>'SYS', method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL', options=>'GATHER', cascade=>TRUE, degree=>16);
execute dbms_stats.gather_schema_stats (ownname=>'SYSTEM', method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL', options=>'GATHER', cascade=>TRUE, degree=>16);

Resource manager

Replace RMADMIN with the resource manager database administrator ID.

execute dbms_stats.gather_schema_stats (ownname=>'RMADMIN', method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL', options=>'GATHER', cascade=>TRUE, degree=>16);