Configure the Database Cleanup utility to delete more tables and columns

The Database Cleanup utility is configurable. We can define tables and rows to clean in addition to the cleanup configurations that are defined by default. We 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 we are running the utility.

Task info

If you extend the database schema by creating tables, we can use the Database Cleanup utility to clean your new tables. If you change the database schema, we 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, our 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: (IBM i)

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)

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')

(Oracle)

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 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.

We can use other values, but we 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: (DB2)

./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)

(AIX) (Oracle)

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

(Windows) (Oracle)

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.

Related concepts
Database Cleanup utility

Related reference
Database Cleanup utility command script