Dbclean utility script

Administer > Database Cleanup utility

Dbclean utility script

Overview

The Database Cleanup utility (dbclean) removes objects from the database.
Run the Database Cleanup utility on a production system with an associated staging system.
You must include the optional parameters, logon user ID, and password even if you are currently running this utility with the same user ID.

Run the script from a DB2 command line.
Run the script from a Windows command prompt.

Log onto the system as a non root user for WebSphere Commerce.
From a command line, run the Database Cleanup utility script (dbclean.sh)

Parameter values

object The name of the object to be deleted.
type The type of object to delete.
instancexml The absolute path to the WebSphere Commerce configuration file.
db Optional: The name of the database.
The name of the database as found in the relational database directory.
For DB2 UDB databases, the Type 2 database name is deprecated, where the database names do not contain a prefix.
That is, the DB2 Type 4 JDBC driver is used instead, where the Type 4 database name is prefixed with the database server and port. For example, db_server:db_port/db_name.
Use host:port:sid. For example, myhost:1521:mydb.
dbuser Optional: The logon ID of the administrator who has created the schema or Site Administrator of the database.
If this parameter is not specified, the ID of the user invoking the utility is used.
On IBM i this is required and is the user profile associated with the WCS instance. This is also the schema name.
dbschema Optional: The database schema name.
This parameter allows a user to run the Database Cleanup utility with a user ID other than that specified for the dbuser parameter.
dbpasswd Optional: The password of the logon ID that is specified by the dbuser or dbschema parameter.
If not specified, the system prompts you to enter the password.
dbtype Optional: The database type.

This can be be either DB2 or Oracle. The default is DB2.
For IBM i, the default is DB2/iSeries.
The default is DB2.

check_object_only Optional: When the check_object_only option is set to yes, it looks at each one of the child tables and sees if they will be affected if their parent table is deleted. If they will be affected, the Database Cleanup utility delete restricts, meaning that the parent cannot be deleted. If they will not be affected, the parent table can be deleted. For example, if an OrderItem is to be deleted, it may affect the owner of the OrderItem, which can be a guest user with nothing else but that OrderItem. In that case, the OrderItem can be dbCleaned. The utility does not perform a check if you leave the parameter to no (the default). This information can be found in the log file.
commit Optional: The number of rows to be deleted for each commit. The default is 1000.
max Optional: The maximum number of rows to be deleted for the entire run. The default is 100000.
log Optional: The path and name of the log file in which the utility records its activities. The issuer of this utility must have write authority to the specified path and the path must already exist. If this parameter is not specified, a log file called DBClean.timestamp.log is created...

WC_INSTALL/instances/instance/logs/DBClean
WC_USER/instances directory.

loglevel Optional: The level of logging to be performed during the database cleanup. Available log level are NONE, ERROR, WARNING, NORMAL, INFO/VERBOSE, or DEBUG. The default is DEBUG.
script Optional: Path and name of the output script to be saved. By default, it is saved as:

WC_INSTALL/logs/assetclean_userName_timestamp_.sh

days Optional: The minimum days in existence for a record to be deleted.
name Optional: The ID of the object to be deleted.
This parameter is required if member was indicated as the value for the organization parameter and organization was indicated as the type value.
jdbcDriver Optional: The JDBC driver to be used.
The default value depends on the database type. For example, if you are using DB2, the JDBC driver may be com.ibm.db2.jcc.DB2Driver. If you are using Oracle, the JDBC driver may be oracle.jdbc.OracleDriver.
jdbcUrlPrefix Optional: The URL prefix for the JDBC driver specified.
The default value depends on the database type. For example, if you are using DB2, the JDBC URL prefix may be jdbc:db2://.
jdbcCustomizer Optional: Specifies the location of the customizer parameter for use with the Database Cleanup utility.
The default values are:

jdbcDriver=com.ibm.db2.jcc.DB2Driver jdbcUrlPrefix=jdbc:db2://

Note that other combinations may work but have not been tested.
sqlmode Optional: Options are direct (1) and default (0).
If -sqlmode 1, the DELETE statement retrieved from the CLEANCONF table is run directly without any dbClean modification. However the commit counter is ignored and the result set from the SQL is committed to the database until the max counter parameter is reached. sqlmode 0 is the default dbClean behavior with all parameters respected including the commit counter. Note that the default dbClean behavior can enhance SQLs retrieved from the CLEANCONF table.

Related
Database Cleanup utility
Configure databases for use with the staging utilities
Set the PATH environment variables for WebSphere Commerce utilities
Clean the database
Add a configuration to the Database Cleanup utility
Delete Sales Assistant statistic objects
Delete staged objects
Delete store objects
Delete user objects
Delete user traffic log objects
Delete vendor objects
Delete address objects
Delete available to promise inventory objects
Delete attachment objects
Delete auction objects
Delete auction log objects
Delete autobid log objects
Delete base item objects
Delete bid log objects
Delete cacheivl objects
Delete calculation code objects
Delete campaign objects
Delete campaign statistic objects
Delete catalog entry objects
Delete catalog group objects
Delete coupon objects
Delete expected inventory record objects
Delete details about expected inventory record objects
Delete forum message objects
Delete fulfillment center objects
Delete inventory code adjustment objects
Delete inventory adjustment objects
Delete specified item information objects
Delete member message relationship objects
Delete message objects
Delete order objects
Delete organization objects
Delete Product Advisor statistic objects
Delete Product Comparison statistic objects
Delete Product Explorer statistic objects
Delete policy objects
Delete product set objects
Delete returned item objects
Delete return reason objects
Delete account objects
Delete calculation rule objects
Delete distributor objects
Delete file upload objects
Database Cleanup utility objects
Examples: Deleting objects

+
Search Tips | Advanced Search

object	The name of the object to be deleted.
type	The type of object to delete.
instancexml	The absolute path to the WebSphere Commerce configuration file.
db	Optional: The name of the database. The name of the database as found in the relational database directory. For DB2 UDB databases, the Type 2 database name is deprecated, where the database names do not contain a prefix. That is, the DB2 Type 4 JDBC driver is used instead, where the Type 4 database name is prefixed with the database server and port. For example, db_server:db_port/db_name. Use host:port:sid. For example, myhost:1521:mydb.
dbuser	Optional: The logon ID of the administrator who has created the schema or Site Administrator of the database. If this parameter is not specified, the ID of the user invoking the utility is used. On IBM i this is required and is the user profile associated with the WCS instance. This is also the schema name.
dbschema	Optional: The database schema name. This parameter allows a user to run the Database Cleanup utility with a user ID other than that specified for the dbuser parameter.
dbpasswd	Optional: The password of the logon ID that is specified by the dbuser or dbschema parameter. If not specified, the system prompts you to enter the password.
dbtype	Optional: The database type. This can be be either DB2 or Oracle. The default is DB2. For IBM i, the default is DB2/iSeries. The default is DB2.
check_object_only	Optional: When the check_object_only option is set to yes, it looks at each one of the child tables and sees if they will be affected if their parent table is deleted. If they will be affected, the Database Cleanup utility delete restricts, meaning that the parent cannot be deleted. If they will not be affected, the parent table can be deleted. For example, if an OrderItem is to be deleted, it may affect the owner of the OrderItem, which can be a guest user with nothing else but that OrderItem. In that case, the OrderItem can be dbCleaned. The utility does not perform a check if you leave the parameter to no (the default). This information can be found in the log file.
commit	Optional: The number of rows to be deleted for each commit. The default is 1000.
max	Optional: The maximum number of rows to be deleted for the entire run. The default is 100000.
log	Optional: The path and name of the log file in which the utility records its activities. The issuer of this utility must have write authority to the specified path and the path must already exist. If this parameter is not specified, a log file called DBClean.timestamp.log is created... WC_INSTALL/instances/instance/logs/DBClean WC_USER/instances directory.
loglevel	Optional: The level of logging to be performed during the database cleanup. Available log level are NONE, ERROR, WARNING, NORMAL, INFO/VERBOSE, or DEBUG. The default is DEBUG.
script	Optional: Path and name of the output script to be saved. By default, it is saved as: WC_INSTALL/logs/assetclean_userName_timestamp_.sh
days	Optional: The minimum days in existence for a record to be deleted.
name	Optional: The ID of the object to be deleted. This parameter is required if member was indicated as the value for the organization parameter and organization was indicated as the type value.
jdbcDriver	Optional: The JDBC driver to be used. The default value depends on the database type. For example, if you are using DB2, the JDBC driver may be com.ibm.db2.jcc.DB2Driver. If you are using Oracle, the JDBC driver may be oracle.jdbc.OracleDriver.
jdbcUrlPrefix	Optional: The URL prefix for the JDBC driver specified. The default value depends on the database type. For example, if you are using DB2, the JDBC URL prefix may be jdbc:db2://.
jdbcCustomizer	Optional: Specifies the location of the customizer parameter for use with the Database Cleanup utility. The default values are: jdbcDriver=com.ibm.db2.jcc.DB2Driver jdbcUrlPrefix=jdbc:db2:// Note that other combinations may work but have not been tested.
sqlmode	Optional: Options are direct (1) and default (0). If -sqlmode 1, the DELETE statement retrieved from the CLEANCONF table is run directly without any dbClean modification. However the commit counter is ignored and the result set from the SQL is committed to the database until the max counter parameter is reached. sqlmode 0 is the default dbClean behavior with all parameters respected including the commit counter. Note that the default dbClean behavior can enhance SQLs retrieved from the CLEANCONF table.