Database maintenance guidelines

an HCL 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 that is stored within the database using the RUNSTATS, REORGCHK, REORG, and REBIND commands.
DB2
DB2 database maintenance tasks
dbclean
Use a customized dbclean utility script to remove records marked for deletion, as well as any information defined as outdated in your database maintenance strategy policies.
RUNSTATS
Use the RUNSTATS command to update statistics on a daily or weekly basis. It is also recommended that you 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
Use the REORGCHK command to 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.
Note:
  • If you are running REORGCHK after RUNSTATS, you can use the CURRENT STATISTICS specifier to avoid updating the statics twice. You can also omit RUNSTATS and use REORGCHK to update the statics, however doing so provides less flexibility.
  • HCL Commerce DeveloperThe reorg.db2.bat and reorg.db2.sh can be run from within the WCDE_installdir/bin directory.
REORG
Use the REORG command to reorganize a database table by compacting information and reconstructing the rows to eliminate fragmented data. After you use the REORGCHK command to identify the database tables or indexes to be reorganized, you must explicitly invoke the REORG command for each object; tables or indexes identified by REORGCHK are not automatically reorganized.
Note: 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
Use the REBIND command to rebind individual static packages after executing the RUNSTATS command to use the updated statistics. To rebind multiple packages at once use the db2rbind command.
Note: 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 HCL Commerce site is minimal.

OracleFor more information about Oracle database maintenance, go to the documentation section of the Oracle Documentation website and search for documentation by the release and version of your Oracle database.