Dbclean utility script

 

Overview

The Database Cleanup utility (dbclean) removes objects from your database.

For example... 

./dbclean.sh -object objname 
             -type all 
             -instancexml WC_installdir/instances/instance/xml/instance.xml 
             -db dbname 
             -dbuser user 
             -loglevel loglevel

For Oracle include the optional parameters, logon user ID, and password even if you are currently running this utility with the same user ID.

For Windows:

For *nix, before running the Database Cleanup utility, log onto your system as a non root user for WebSphere Commerce. Run the Database Cleanup utility script from a command line.

For I5/OS use dbclean.sh. To run shell scripts:

  1. Log on with a user profile that has a CCSID other than 65535.

  2. Open a QSHELL command window by typing the following command on the command line: STRQSH.

  3. Run the utility as follows:

    WC_installdir/bin/dbclean.sh parameters

 

Parameter values

object
The name of the object to be deleted. Type one of the following object names:

account
Delete account objects.

address
Delete address objects.

atp_inventory
Delete receipt information objects.

attachment
Delete attachment objects.

auction
Delete auction objects.

auctionlog
Delete auction log objects.

autobidlog
Delete autobids objects for auctions.

baseitem
Delete product information objects.

bidlog
Delete bid log objects.

cacheivl
Delete cacheivl objects.

calculation_code
Delete calculation code objects.

catentry
Delete catalog entry objects.

catalog_group
Delete catalog group objects.

contract
Delete contract objects.

coupon_promotion
Delete coupon objects.

cpgnlog
Delete campaign objects.

cpgnstats
Delete campaign statistic objects.

expected_inventory_records
Delete inventory objects.

expected_inventory_records_details
Delete inventory detail objects.

fileupload
Delete file upload objects.

forummsg
Delete message objects between a Site Administrator and customers.

fulfillment_center
Delete fulfillment center objects.

inventory_adjustments
Delete inventory objects.

inventory_adjustment_codes
Delete inventory code objects.

itemspecification
Delete specified item objects.

message
Delete auction-related message objects.

msgmemrel
Delete message member relationship objects.

order
Delete order objects.

organization
Delete organization objects.

pastats
Delete Product Advisor statistic objects.

pcstats
Delete Product Comparison statistic objects.

pestats
Delete Product Explorer statistic objects.

policy
Delete policy objects.

product_sets
Delete product set objects.

rfq
Delete Request For Quote objects.

rma
Delete returned item objects.

rtnreasons
Delete return reason objects.

sastats
Delete Sales Assistant statistic objects.

staglog
Delete staged objects.

store
Delete store objects.

user
Delete user objects.

usrtraffic
Delete user traffic log objects.

vendor
Delete vendor objects.

type
The type of object you want to delete.

instancexml
The absolute path to the WebSphere Commerce configuration file.

db

I5/OS|(Optional) The name of the database. The name of the database as found in the relational database directory.

If you have configured the jdbcCustomizer parameter to use the toolboxJDBC driver, specify the host name of the machine where the database resides instead of the database name, for example, -db hostname.ibm.com.

(Oracle) 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.

I5/OS|  (Required) The user profile associated with the WebSphere Commerce 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.

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 in the following 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) Specifies the path and name of the output script to be saved. By default, it is saved as:

  • AIX|Linux|Solaris: WC_installdir/logs/assetclean_userName_timestamp_.sh

  • I5/OS:

     WC_userdir/instances/assetclean_userName_ timestamp_.sh

  • Windows: WC_installdir\logs\assetclean_userName_timestamp_.cmd

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.jdbc.app.DB2Driver.

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:

  • I5/OS| jdbcDriver=com.ibm.db2.jdbc.app.DB2Driver jdbcUrlPrefix=jdbc:db2://
    To configure the Database Cleanup utility to use the toolbox JDBC driver specify DB2/iSeries as the dbtype, and ensure that the customizer file contains the following line 
    jdbcDriver=com.ibm.as400.access.AS400JDBCDriver jdbcUrlPrefix=jdbc:as400://.

  • AIX|Linux|Solaris|Windows:

    jdbcDriver=COM.ibm.db2.jdbc.app.DB2Driver jdbcUrlPrefix=jdbc:db2

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 Concepts


Database Cleanup utility

 

Related tasks


Cleaning the database

 

Related Reference


Examples: Deleting objects