Configuring the Database Cleanup utility to delete more tables and columns

The Database Cleanup utility is configurable. You can define tables and rows to clean in addition to the cleanup configurations that are defined by default. You can define tables and rows to delete by updating the utility configuration data in the CLEANCONF table. To extend the Database Cleanup utility, specify values for the jdbcDriver and jdbcUrlPrefix parameters when you are running the utility.

About this task

If you extend your database schema by creating tables, you can use the Database Cleanup utility to clean your new tables. If you change your database schema, you must review and possibly modify or add SQL statements in the CLEANCONF table so that the utility behaves correctly in the new schema. For example, if you add columns to one table, change a foreign key primary key relationship, or add a child table to the referential integrity path.

Note: Create cleanup configurations instead of altering existing configurations. By creating configurations, your changes are not lost when you apply maintenance fixes.

To add a cleanup configuration for the Database Cleanup utility, use the following syntax as a reference. The following statements configure the Database Cleanup utility to delete all objects with column_1 > 10, and where lastupdate is n days ago. In the following sample statements, object object_1 consists of table Table_1, which contains the following columns: column_1, column_2, lastupdate, and column_3.

Procedure

Run the following SQL statement:

insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) values 
('object_1', 'obsolete', 'delete from Table_1 where column_1 > 10 and (days(CURRENT TIMESTAMP) - days(lastupdate)) > ?', 'no', 1, 'yes')

db2 insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) 
values ('object_1', 'obsolete', 'delete from Table_1 where column_1 > 10 and (days(CURRENT TIMESTAMP)
- days(lastupdate)) > ?', 'no', 1, 'yes')

insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) values 
('object_1', 'obsolete', 'delete from Table_1 where column_1 > 10 and (sysdate - lastupdate) > ?', 'no', 1, 'yes')

where:

?: Is replaced by the value for the -days parameter that is included in the command-line when you run the utility.
no: Indicates that the name parameter is not used in the statement.
yes: Indicates that the -days parameter is used in the statement.
obsolete: Describes the cleanup type for object object_1.

You can use other values, but you must use the same value in the -type argument when you run the Database Cleanup utility.

Example: To run the Database Cleanup utility to clean the records that are in existence for two days from the new table, run the following script:

./dbclean.sh -object o1 -db dbname -dbuser user -type obsolete -days 2 -loglevel LOGLEVEL

Where LOGLEVEL is one of:

NONE
ERROR
WARNING
NORMAL
INFO
VERBOSE
DEBUG (The default value)

./dbclean.sh -object o1 -db dbname -type obsolete -days 2 -loglevel 1 -dbtype oracle -dbuser user -dbpasswd password

dbclean -object o1 -db dbname -type obsolete -days 2 -loglevel 1 -dbtype oracle -dbuser user -dbpasswd password

For the dbname parameter, use

host:port:sid

for example, myhost:1521:mydb.