Database maintenance guidelines

A WebSphere Commerce site can experience significant performance degradation if its database is not properly maintained. To prevent performance issues, implement a data maintenance strategy to remove obsolete data, periodically reorganize database tables, and maintain current database table statistics. Maintenance activities include:

• Maintenance of the database itself using the dbclean utility.

• Maintenance of the data stored within the database using the RUNSTATS, REORGCHK, REORG, and REBIND commands.

(DB2)

dbclean

Use a customized dbclean utility script to remove records marked for deletion, as well as any information defined as outdated in the database maintenance strategy policies.

RUNSTATS

Update statistics on a daily or weekly basis. Update the statistics after schema changes, a massive update, or increased load (such as refreshing the catalog). Since the statistics updated by the RUNSTATS command are used by the optimizer when determining access paths to the data, inaccurate statistics can result in decreased database efficiency.

REORGCHK

Update the statistics for all objects in the database and determine which tables and indexes must be reorganized. The REORGCHK command outputs a table that lists all of the table and index objects in the database. Objects with an asterisk (*) in the REORG column might need to be reorganized.

• If we are running REORGCHK after RUNSTATS, we can use the CURRENT STATISTICS specifier to avoid updating the statics twice. We can also omit RUNSTATS and use REORGCHK to update the statics, however doing so provides less flexibility.

• (Developer) The reorg.db2.bat and reorg.db2.sh can be run from within the WCDE_installdir/bin directory.

REORG

Reorganize a database table by compacting information and reconstructing the rows to eliminate fragmented data. After using the REORGCHK command to identify the database tables or indexes to be reorganized, we must explicitly invoke the REORG command for each object; tables or indexes identified by REORGCHK are not automatically reorganized.

A classic (offline) reorganization locks the database tables being reorganized for writing. To allow updates to these tables, use inplace (online) table reorganization instead.

REBIND

Rebind individual static packages after executing the RUNSTATS command to use the updated statistics. To rebind multiple packages at once use the db2rbind command.

If a package is already in use, the db2rbind command cannot rebind a package.

Since these tasks might cause some performance degradation and locking while they are being executed, perform database maintenance when the user traffic to the WebSphere Commerce site is minimal. (Oracle) For more information about Oracle database maintenance, go to the documentation section of the Oracle Technology Network Web site and search for documentation by the release and version of your Oracle database.

See

• Implement a data maintenance strategy
  
• Manage the KEYS table for non-sequential key enabled tables
  
• Database Cleanup utility