DB2 database maintenance utilities | Commerce DB2 database maintenance solution


25.1.2 WebSphere Commerce Database Cleanup utility


+

Search Tips   |   Advanced Search

Keeping obsolete data affects runtime performance and makes the database difficult to manage. In order to keep the database consistent, policies should be defined and enforced to remove outdated information from the database.

WebSphere Commerce includes a Database Cleanup utility that allows you to delete objects from the database. You may want to do this if you have changed a lot of information in your database and have unused tables or rows.

When the Database Cleanup utility deletes an object, the records in the object's tables are deleted to preserve the referential integrity of the database. The Database Cleanup utility deletes records in child tables based on the delete rule of the referential integrity definition in the database schema. You can set the delete rule to on delete cascade, on delete set null, or on delete restrict. If you add new tables, ensure that the referential integrity and delete rule is properly defined. Otherwise, the Database Cleanup utility cannot work with your new tables.

The general approach to implement WebSphere Commerce Database Cleanup utility is as shown in Figure 25-2.

Figure 25-2 WC database cleanup utility

The following command can be used to record the cardinality (number of rows) of each table. Run this command on a monthly basis and save the output. Comparing the current table cardinality with that of previous months will allow you to identify those tables that require the most attention and maintenance.

db2 "EXPORT TO tablecard.csv OF DEL SELECT tabschema, tabname, definer, card, fpages FROM syscat.tables WHERE type = 'T' AND (days (current date) - days (stats_time)) < 7"

Tips for implementing DBClean:

1. If you are migrating from an existing version of WebSphere Commerce, you can run the Database Cleanup utility after your migration. Remember to evaluate the types of data on your system and how they affect database maintenance. Typically, user and order data can be quite large, resulting in large database tables. When you clean the database, this will be time consuming since it can fill up your database transaction log files or potentially lock database tables when your store is running.

2. You should only run the Database Cleanup utility on a staging server to clean the staglog object. The staging database is different from the production database. The staging database only has configuration data without the operation data. Deleting configuration data might cause a delete cascade on the operation data. When the Stage Propagate utility propagates the deletion to the production database, this might cause a cascade delete to the operation data (which you want to keep). To clean configuration data, run the Database Cleanup utility on the production database.

3. Depending on the amount of cleanup required for your database, you should consider running the DB2 REORGCHK utility prior to running the DBClean to improve performance during the cleanup.
xxxx