Database Cleanup utility

The Database Cleanup utility provides you with the ability to delete objects from the database. IBM recommends that you run the database cleanup periodically, especially if a lot of information changes in the database and the 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. Tip:

• If we are migrating from an existing version of WebSphere Commerce, we 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 we are cleaning the database, the process is time consuming since it can fill up the database transaction log files. The process can also potentially lock database tables while the store is running.

• If you run the Database Cleanup utility in a staging environment, 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 we want to keep). 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.

• (DB2) Depending on the amount of cleanup required for the 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. In contrast, when the utility runs in the offline mode, the utility does not use cascade-delete operations. Instead, the utility uses SQL delete statements for each table in the hierarchy to explicitly delete objects from each table. When the utility runs in the offline mode, the utility first checks the table hierarchy to identify the tables and hierarchy branches that contain records to delete. By default, the utility checks only the first two levels below the root table within a hierarchy. For example, in the following sample hierarchy, the utility checks the root table, T1, and any table within the first, 1 , and second hierarchy level, 2 .

After the utility completes checking the hierarchy levels, the utility reduces the hierarchy by excluding any tables that do not include records to delete. Any child table of an excluded table is also excluded unless the table is also the child of a table included in the reduced hierarchy. For example, the tables, T11 and T13 in this sample hierarchy remain in the reduced hierarchy, even though the tables might not include records to delete. The following image shows how the utility can reduce the sample hierarchy by excluding the tables that do not have records to delete and the child tables of these tables.

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. For example, the following image represents the pass groupings for the remaining tables in the preceding sample hierarchy. In the sample hierarchy, the table T6 includes foreign constraints to multiple parent tables. The utility groups table T6 into two passes for the same hierarchy level. One pass for the constraint with T2, and the other pass for the constraint with T3.

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, we must include the offlinemode parameter in the utility command set to yes. See Database Cleanup utility command script.

See

• Running the Database Cleanup utility in a staging environment
  

• Configure the Database Cleanup utility to delete more tables and columns
  

• Database Cleanup utility objects
  

• Cleaning the database
  

Related tasks
Configure databases for use with the staging utilities