dbclean utility in IBM Websphere Commerce

Websphere commerce provides dbclean utility to clean up the database tables that have lots of obsolete data. For example, the STAGLOG table contains data of changes that needs to be propagated to the production. In a typical architecture, all changes are made to the staging environment and then they are propagated to the production using the stage prop utility. However, over a period of time, the STAGLOG table is filled up with lots of records and could become a performance issue.

The following is the syntax of the dbclean command.

dbclean.sh / dbclean.bat

-object obj –type s  -instancexml s  -db s  -dbuser s   -dbschema s  -dbpassword s  -dbtype s

-check_object_only s  -comming n –max s   -log s  -loglevel s  -script s  -days s –name s

-jdbcDriver s  -jdbcUrlPrefix s  -jdbcCustomizer s  -sqlmode n

Where

object is the name of the object to be deleted. Websphere commerce defines various objects in the following URL http://publib.boulder.ibm.com/infocenter/wchelp/v6r0m0/index.jsp?topic=/com.ibm.commerce.admin.doc/refs/rduobjects.htm

type of an object is also specified for each object in the above URL.

instancexml is the absolute path of the commerce instance xml file. Usually this can be found at WebSphere/CommerceServer60/instances/<<instance_name>>/xml/<<instance_name>>.xml 

db – Optional - is the name of the database. For oracle type in as host:port:sid

dbuser – Optional – is the owner of the schema or the administrator of the database. If not specified id of user invoking dbclean command is used

dbschema – Optional – is the database schema name

dbpassword – Optional – is the password for the login ID specified by dbuser parameter

dbtype – Optional – specifies the dbtype. Default is DB2.

check_object_only – Optional – checks dependencies between tables if specified as “yes”. For example, an OrderItem might have an owner (user).

Loglevel – Optional - NONE, ERROR, WARNING, NORMAL, INFO/VERBOSE, or DEBUG (Default)

As an example, to delete obsolete objects from STAGLOG table, the following is the command

dbclean.sh  -object staglog –type obsolete –instancexml <<Commerce_Server_path>>/CommerceServer60/instances/<<instance_name>>/xml/<<instance_name>>.xml  -db host:port:sid  -check_object_only yes   –dbtype oracle    -dbuser <<dbuser>>  -dbpassword  <<dbpwd>>

 This will delete all STAGLOG entries with type=obsolete, which means that all records that have STGPROCESSED=1 (already processed) in the STAGLOG table.

I just realized that the attribute "days" is a mandatory attribute for STAGLOG object. Specifying -days 1 means that remove all objects of STAGLOG which are atleast 1 day older.