Database Cleanup utility
The Database Cleanup utility provides you with the ability to delete objects from the database. It is recommended that you run the database cleanup periodically, especially if a lot of information changes in your database and your database includes unused tables or rows.
When the Database Cleanup utility deletes an object, the records
in the tables for object are deleted to preserve the referential integrity
of the database. The Database Cleanup utility deletes records in child
tables that are based on the delete rule of the referential integrity
definition in the database schema. Your database administrator 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.
- 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 large, resulting in large database tables. When you are cleaning the database, the process is time consuming since it can fill up your database transaction log files. The process can also potentially lock database tables while your store is running.
- If you run the Database Cleanup utility on a staging server, run the utility on only the STAGLOG object. The production-ready database is different from the production database. The production-ready database has only configuration data without the operation data. Deleting configuration data might cause a cascade delete on the operation data. When the stagingprop utility propagates the deletion to the production database, this deletion might cause a cascade delete to the operation data (which you want to keep). For more information, see Running the Database Cleanup utility in a staging environment. To clean only the configuration data, run the Database Cleanup utility on the production database.
- Depending on the amount of cleanup that is required for your database, consider running the DB2 REORGCHK utility before you run the utility to improve performance during the cleanup.
- For better performance, ensure that the Database Cleanup is done on the USRTRAFFIC table for MEMBER and ADDRESS tables on the WebSphere Commerce database.
Offline mode
The Database Cleanup utility offline mode reduces the performance impact of deleting many objects that are stored in a deeply or widely nested table hierarchy.
When the utility runs in the default online mode, the utility uses cascade-delete operations to remove records. The utility deletes objects in the root table and then uses the cascade-delete operations to delete objects in child tables in a table hierarchy. This process can be time-consuming and affect database performance when the utility is deleting many records for an object throughout a deeply or widely nested hierarchy. For example, when the utility is deleting user records, which can potentially exceed millions of records.
With the initial table hierarchy checked and reduced in scope, the utility groups the remaining tables into passes, which are based on the hierarchy level of the tables. Each pass can include multiple tables that belong within the same hierarchy level. There is no limit to the number of tables that can be included in a pass grouping. If a table includes foreign constraints to multiple tables, the table is included in multiple passes, one for each constraint.
With the tables grouped into passes, the utility generates the delete SQL statements to run against each table within each pass. Only the records that match the SQL statement are deleted. The utility uses a bottom-up approach to run the SQL statements against the tables in each pass for a hierarchy. The utility begins cleaning each pass by running the SQL statements concurrently against all of the tables in the passes for the lowest level of the hierarchy. The utility then runs the SQL statements against the passes in each higher level in the hierarchy before the utility runs the SQL statement to clean the root table last.
To run the Database Cleanup utility in the offline mode, you must include the offlinemode parameter in the utility command set to yes. For more information, see Database Cleanup utility command script.