---

9.4.5 Database cleanup The Database Cleanup utility enables you to delete many objects from the database at the same time while maintaining referential integrity. You may want to do this if you have changed a great deal of information in the database and have unused tables or rows. There are several objects that you can delete from the database. 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 command cleans the database in one of two ways: top-down or bottom-up. Top-down deletes all rows from the child tables with a delete cascade. If a delete restrict is specified in the referential integrity, the delete cascade will fail and we will have to use the bottom-up method. To use the bottom-up method, specify yes for the (Windows, AIX®, Solaris™, or 400) FORCE parameter in the command syntax, which first deletes the child tables, followed by the parent table. Another way to trigger the bottom-up method is to specify the (Windows, AIX, Solaris, or 400) LOGLEVEL parameter as 2 in the command syntax. Specifying 0 logs nothing, and 1 logs only the delete statements from the top table. The (Windows, AIX, Solaris, or 400) LOGLEVEL 2 logs the delete statements from each deleted child table until the top table. Although selecting 2 triggers the bottom-up method, it cannot guarantee a successful deletion if there is a delete restrict in the referential integrity. To delete records with a delete restrict, specify the (Windows, AIX, Solaris, or 400) FORCE parameter as yes. You can expect longer response time with the bottom-up method if the table has many child tables. For example, the MEMBER table contains more than 500 child tables. For performance reasons, we recommend using the top-down method. Note: Only use Loglevel 2 or the FORCE parameter as yes if specifying the FORCE parameter as no and Loglevel 0 or 1 fails. The Database Cleanup utility is configurable, extensible, and adaptable. Aside from the preset cleanup configurations, you can add new objects to the database table to define which tables and rows to clean. If you have extended the database schema by creating new tables, you can use the Database Cleanup utility to clean the new tables. If you have changed the database schema (such as adding new columns to one table, changing the foreign key primary key relationship, or adding a new child table to the referential integrity path), the Database Cleanup utility will automatically adapt to the changes. If you change the column names, update the configuration data in the CLEANCONF table. 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 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 the new tables. Note: 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 to keep). To clean configuration data, run the Database Cleanup utility on the production database.

---

ibm.com/redbooks

+

Search Tips   |   Advanced Search