Performance consideration for DBClean

DBClean can be tuned to gain performance improvement. The CLEANCONF table holds the actual SQL statement that the script references. Sample SQL stored in the CLEANCONF table to clean up the objects can be found at this link|:

http://publib.boulder.ibm.com/infocenter/wchelp/v5r6m1/index.jsp?topic=/com.ibm.commerce.admin.doc/refs/rduobjects.htm

For other objects that can be cleaned up using DBClean, review the list at above link. A rule of thumb is to clean up, at the minimum, obsolete tables and objects. Some of the cleanup utility objects are:

For more information, see this link about Database cleanup utility objects supported by Commerce:

http://publib.boulder.ibm.com/infocenter/wchelp/v5r6m1/index.jsp?topic=/com.ibm.commerce.admin.doc/refs/rduobjects.htm

For example, if a user wants to delete guest shoppers who have no orders associated and who has been in the system for more than 10 days, a new object called guest_shopper can be created by inserting the following SQL into the CLEANCONF table and calling DBCLEAN on the object (Example 25-3).

Example 25-3 Sample SQL to delete guest shoppers with DBClean

Deletes guest_shopper ids older than 10days **
delete from member
where member_id in (select users_id from users where registertype='G'
and (days(CURRENT TIMESTAMP) - days(lastsession)) >= 10
and (users_id not in (select member_id from orders)) and (users_id > 0)) 

xxxx