Database Cleanup utility command script
The Database Cleanup utility (dbclean) removes unused or obsolete objects from the database.
- Include the optional parameters, logon user ID, and password when you run the utility, even if you are currently running this utility with the same user ID.
- Log on to your system as a non-root user for WebSphere Commerce to run the utility from a command line.
- Run the utility script from a DB2 command line.
- Run the utility script from a Windows command prompt.
- Use the dbclean.sh shell script to run the utility and use
the following steps to run this shell script:
- Log on with a user profile that has a CCSID other than 65535.
- Start a Qshell session.
- Run the utility as follows: WC_installdir/bin/dbclean.sh parameters
Parameter values
- object
- Required. The name of the object to be deleted. For more information about the object names, see Database Cleanup utility objects.
- type
- Required. The type of object you want to delete. For more information about the type to specify with an object, see Database Cleanup utility objects.
- instancexml
- Required. 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.Note: For DB2 UDB databases, the DB2 Type 4 JDBC driver is used, 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 created the schema or Site Administrator of the database. If this parameter is not specified, the ID of the user who invokes the utility is used.
- dbschema
- Optional: The database schema name. This parameter allows a user to run the dbschema utility with a user ID other than the user ID 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.
- The default value is DB2.
- The default value is DB2/iSeries.
- check_object_only
- Optional: When the check_object_only option is set to yes, each child table
is checked to see whether the table is affected if their parent table is deleted. If the child table
is affected, the dbclean utility delete restricts, meaning that the parent
cannot be deleted. If the child table is not affected, the parent table can be deleted. For example,
if an OrderItem is to be deleted, it can 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 cleaned by using
dbclean. 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.
Do not include this parameter when you run the utility in the offline mode.
- commit
- Optional: The number of rows that are deleted in each commit. The default is 1000.If the offlinemode parameter is set to yes, the utility uses two commit counts. One for the root table, and the other for all child tables. The commit count for the root table uses the specified value for the commit parameter or the default value if no value is specified. The commit count for the child tables is determined by a formula.
This formula uses the specified values for the commit andchild_commit_count = (commit * commit); while (childCommitCnt >= max) { childCommitCnt /= 2; } if(0 == childCommitCnt) { childCommitCnt = commit; }
max
parameters. Themax
parameter value sets the limit for the size of the data set to delete in the cleanup operation. This value is used in the formula to ensure that the commit count is large enough to avoid over-committing, which can affect database performance. The commit parameter value is used to ensure that the commit count is not too large in comparison to themax
parameter value. By ensuring that the commit count is not too large, the utility can avoid any rapid consumption of resources, such as the transaction log file. - max
- Optional: The maximum number of objects to be deleted for the entire run is determined by the
commit parameter along with this max parameter value. The default is 100000.
The maximum number of rows are deleted according to a multiple of the commit value that meets or
exceeds the max parameter value.
For example, if you specify max 35 and commit 20, deletes are committed every 20 rows. The maximum number of rows that are deleted is 40 because 40 is the closest number that is a multiple of commit that exceeds the max parameter value. If you specify the max value to be 20 and commit 35, the maximum number of rows that are deleted is 35.
- 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 exist. If
this parameter is not specified, a log file that is called
dbclean.timestamp.log is created in the following directory:
- WC_installdir/instances/instance_name/logs
- WC_userdir/instances
- loglevel
- Optional: The level of logging to be performed during the database cleanup. Available log levels are NONE, ERROR, WARNING, NORMAL, INFO/VERBOSE, or DEBUG. The default is NONE.
- 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 might be com.ibm.db2.jcc.DB2Driver. If you are using Oracle, the JDBC driver might 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 might be jdbc:db2://.
- jdbcCustomizer
- Optional: Specifies the location of the customizer file to use with the Database Cleanup
utility. You can specify values for the jdbcDriver and jdbcUrlPrefix parameters in the format
argument=value on each line inside the file.
This parameter is deprecated. Consider using the
paramfile
parameter instead.The default value is
jdbcDriver=com.ibm.db2.jcc.DB2Driver jdbcUrlPrefix=jdbc:db2://
. Other combinations might work but are not tested. - sqlmode
- Optional: You can set the following values for this parameter:
- 1
- Direct mode. The DELETE statement that is retrieved from the CLEANCONF table is run directly without any utility modification. The result set from the SQL is committed to the database until the value of the max parameter is reached. When you set the value for the sqlmode parameter to be 1, ensure that you also set the value for the commit parameter to be 1.
- 0
- The default mode. The default utility behavior with all parameters respected including the commit counter. The default dbclean utility behavior can enhance SQLs retrieved from the CLEANCONF table.
Do not include this parameter when you run the utility in the offline mode.
- dbauser
- Optional: The database administrator user name. If the database administrator name is not included as a parameter on the command line, the name is retrieved from the instance_name.xml file. If the name is also not included in this file, you are prompted to enter the value when you run the utility.
- dbapasswd
- Optional: The database administrator password. If the database administrator password is not included as a parameter on the command line, the password is retrieved from the instance_name.xml file. If the password is also not included in this file, you are prompted to enter the value when you run the utility.
- sqlParam%
- Optional: Indicates that the value for this parameter is to replace a parameter marker in the
SQL statement for the object that is stored in the CLEANCONF database table. The
% character in the parameter represents the index of the parameter marker. The
index must start at 1. The sqlParam% parameter cannot be used with the days and
name SQL parameters. If multiple sqlParam% are used for parameter markers, you
must increase the index sequentially for sqlParam% parameter in your utility
command. For example, you can include the following sqlParam% parameters in the command line:
These parameters can be bound to the corresponding parameter markers,-sqlParam1 0 -sqlParam2 2 -sqlParam3 4
1
,2
, and3
, within the following SQL statement:
After the utility command runs, the parameter markers are replaced with the values for the sqlParam% in the command. For example,delete from member where member_id in (select users_id from users where (current_timestamp - lastupdatetimestamp)> ? and users_id > ? and not exists (select 1 from orders where orders_id=?))
delete from member where member_id in (select users_id from users where (current_timestamp - lastupdatetimestamp)> 0 and users_id > 2 and not exists (select 1 from orders where orders_id=4))
- paramfile
- Optional. Specifies the path to the parameter file that includes command-line arguments and
values. Each argument and value needs to be in the format
argument=value
with a single argument and value on each line in the file. Any passwords within this parameter file must be encrypted. - offlinemode
- Optional: Indicates that the Database Cleanup is to run offline and use threading and explicit
deletes to clean the database. By running the utility offline, you can reduce the performance impact
of deleting deeply or widely nested table hierarchies of user objects. For more information about
running the utility offline, see Database Cleanup utility. You can set the following values for this parameter:
- yes
- The Database Cleanup utility runs in the offline mode. The utility cannot detect whether your environment is offline. The utility prompts you to confirm that your environment is offline.
- no
- The Database Cleanup utility runs in the online mode. This value is the default value.
Before you run the utility in the offline mode, review the following considerations and limitations: - prunelevel
- Optional: An integer that indicates the number of levels below the root table within a table
hierarchy that the utility checks for object records to delete. If a table, and any child table that
is checked do not include objects to delete, the table is removed from the cleanup operation.
Removing tables reduces the size of the cleanup operation, which can improve the efficiency of the
cleanup process. You can use this parameter only when you run the utility offline. As you increase
the number of hierarchy levels to check, more tables and records are included in the checking
process and the number of checks increase, which can affect performance of the utility. The default
value is 2.
For more information about how the utility checks and cleans tables when the utility runs offline, see Database Cleanup utility.
- threadCount
- Optional: Indicates the maximum number of threads that the utility can execute concurrently. You can use this parameter only when you run the utility offline.
- validatedel
- Optional: Indicates whether the utility validates that objects are deleted from the database.
You can use this parameter only when you run the utility offline. You can set the following values
for this parameter:
- yes
- Run the validation check.
- no
- Do not run the validation check. This value is the default value.
To monitor the Database Cleanup utility, refer to the following files:
- For the cleanup progress, refer to the DBClean.timestamp.log log file, which is in the WC_installdir/instances/instance_name/logs/DBClean directory. The log level for the cleanup can be NONE, ERROR, WARNING, NORMAL, INFO/VERBOSE, or DEBUG. By default the log level is set to DEBUG.
- If an unrecoverable error occurs during cleanup, refer to the WC_installdir/instances/instance_name/logs/DBClean/System.timestamp.Properties file. The Database Cleanup utility automatically stops and the JVM properties are stored in this file.
- If you want to delete obsolete files that are associated with data assets, run the following script:
- WC_installdir/instances/instance_name/logs/DBClean/clean.asset.object.type.timestamp.bat
- WC_installdir/instances/instance_name/logs/DBClean/clean.asset.object.type.timestamp.sh
Example
-
- ./dbclean.sh -object user -type registered -instancexml WebSphere/WebSphereCommerce/instances/demo/xml/demo.xml -db CD040302 -commit 500 -max 8000 -check_object_only yes
- dbclean -object user -type registered -instancexml C:\WebSphere\WebSphereCommerce\instances\demo\xml\demo.xml -db CD040302 -check_object_only yes
-
- ./dbclean.sh -object user -type registered -instancexml WebSphere/WebSphereCommerce/instances/demo/xml/demo.xml db host:port:sid -commit 500 -max 8000 -check_object_only yes -dbtype oracle -dbuser johnsmith -dbpasswd mypasword
- dbclean -object objectname -type registered -instancexml C:\WebSphere\WebSphereCommerce\instances\demo\xml\demo.xml -db host:port:sid -commit 500 -max 8000 s-check_object_only yes -dbtype oracle -dbuser johnsmith -dbpasswd mypasword
Example: offline mode
-
- ./dbclean.sh -object user -type registered -instancexml WebSphere/WebSphereCommerce/instances/demo/xml/demo.xml -db CD040302 -commit 500 -max 8000 -db CD040302 -dbschema wcs -dbauser Tomsmith -dbauserpwd myadminpassword -dbuser johnsmith -dbpasswd mypasword -offlinemode yes -validatedel yes -prunelevel 2
- dbclean -object user -type registered -instancexml C:\WebSphere\WebSphereCommerce\instances\demo\xml\demo.xml -db CD040302 -dbschema wcs -dbauser Tomsmith -dbauserpwd myadminpassword -dbuser johnsmith -dbpasswd mypasword -offlinemode yes -validatedel yes -prunelevel 2
-
- ./dbclean.sh -object user -type registered -instancexml WebSphere/WebSphereCommerce/instances/demo/xml/demo.xml -db host:port:sid -commit 500 -max 8000 -dbtype oracle -db CD040302 -dbschema wcs -dbauser Tomsmith -dbauserpwd myadminpassword -dbuser johnsmith -dbpasswd mypasword -offlinemode yes -validatedel yes -prunelevel 2
- dbclean -object objectname -type registered -instancexml C:\WebSphere\WebSphereCommerce\instances\demo\xml\demo.xml -db host:port:sid -commit 500 -max 8000 -dbtype oracle -db CD040302 -dbschema wcs -dbauser Tomsmith -dbauserpwd myadminpassword -dbuser johnsmith -dbpasswd mypasword -offlinemode yes -validatedel yes -prunelevel 2