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 preset clean configurations. 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 (such as adding new columns to one table, changing the foreign key primary key relationship, or adding a child table to the referential integrity path), you must review and possibly modify or add SQL statements in the CLEANCONF table so that the utility behaves correctly in the new schema.
Note: You can create configurations instead of altering the existing ones. That way, changes are not lost on a fix pack installation.

To add a configuration to the Database Cleanup utility, use the following syntax as a reference. For example, object o1 consists of table R1, which contains the following columns: col1, col2, lastupdate, and col3. To configure the Database Cleanup utility to delete all objects with col1 > 10, and where lastupdate is n days ago:

Procedure

Run the following SQL statement:
For IBM i OS operating system
insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) values 
('o1', 'obsolete', 'delete from r1 where col1 > 10 and (days(CURRENT TIMESTAMP) - days(lastupdate)) > ?', 'no', 1, 'yes')
SolarisDB2LinuxAIXWindows
db2 insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) 
values ('o1', 'obsolete', 'delete from r1 where col1 > 10 and (days(CURRENT TIMESTAMP)
- days(lastupdate)) > ?', 'no', 1, 'yes')
Oracle
insert into cleanconf (objectname, type, statement, namearg, sequence, daysarg) values 
('o1', 'obsolete', 'delete from r1 where col1 > 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. The 'no' indicates that the name parameter is not used in the statement. The 'yes' indicates that the -days parameter is used in the statement. The 'obsolete' value describes the cleanup type for object 'o1'. 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:
  • For IBM i OS operating systemSolarisDB2LinuxAIX./dbclean.sh -object o1 -db dbname -dbuser user -type obsolete -days 2 -loglevel LOGLEVEL
  • DB2Windowsdbclean -object o1 -db dbname -type obsolete -days 2 -loglevel LOGLEVEL
Where LOGLEVEL  is either NONE, ERROR, WARNING, NORMAL, INFO(or VERBOSE), or DEBUG.  The default is DEBUG.
  • SolarisAIXOracle./dbclean.sh -object o1 -db dbname -type obsolete -days 2 -loglevel 1 -dbtype oracle -dbuser user -dbpasswd password
  • WindowsOracledbclean -object o1 -db dbname -type obsolete -days 2 -loglevel 1 -dbtype oracle -dbuser user -dbpasswd password
Note: For the dbname parameter, use host:port:sid for example, myhost:1521:mydb.