Database Cleanup utility command script
The Database Cleanup utility (dbclean) removes unused or obsolete objects from the database.
Before you begin
Run this utility from the Utility server Docker container. For more information, see Running utilities from the Utility server Docker container.
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
- Optional: The absolute path to the HCL Commerce configuration file.
- db
- Optional: The name of the database, encoded as a JDBC type 4 pattern URL. If you leave this field empty, the utility reads the database information from the instance.
- 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.
- Optional: The hostname of the database. If you leave this field empty, then the utility reads the database information from the instance.
- Optional: The port number of the database. If you leave this field empty, then the utility reads the database information from the instance.
- dbtype
- Optional: The database type. The default value is DB2. You can also specify oracle.
- 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 value 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 and max parameters. Thechild_commit_count = (commit * commit); while (childCommitCnt >= max) { childCommitCnt /= 2; } if(0 == childCommitCnt) { childCommitCnt = commit; }
max
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
value 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 the max value to 35 and the commit value to 20, then 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 to be 35, then 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: /opt/WebSphere/CommerceServer90/logs/DBClean/.
- 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.
Removed as of HCL Commerce 9.1.15.0.
- 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://.
Removed as of HCL Commerce 9.1.15.0.
- 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.Removed as of HCL Commerce 9.1.15.0.
- 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.Note: This is true if dbclean runs in the online mode.
- dbapasswd
- Optional: The database administrator password. If the
database administrator password is not included as a parameter
on the command line, then the system prompts you to enter the
password.Note: This is true if dbclean runs in the offline mode.
- 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.
- For the cleanup progress, refer to the
DBClean.timestamp.log
log file, which is located in the
/opt/WebSphere/CommerceServer90/logs/DBClean/DBClean/
directory. The log level for the cleanup can be
NONE
,ERROR
,WARNING
,NORMAL
,INFO/VERBOSE
, orDEBUG
. By default the log level is set toDEBUG
. - If an unrecoverable error occurs during cleanup, refer to the utilities_root/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: utilities_root/instances/instance_name/logs/DBClean/clean.asset.object.type.timestamp.sh
Example: Delete user objects
Example: Offline mode
./dbclean.sh -object user -type registered
-instancexml /opt/WebSphere/CommerceServer90/instances/demo/xml/demo.xml
-db CD040302 -commit 500 -max 8000 -dbschema wcs
-dbauser Tomsmith -dbauserpwd myadminpassword -dbuser johnsmith
-dbpasswd mypasword -offlinemode yes -validatedel yes -prunelevel 2