Database Cleanup utility command script

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.

child_commit_count = (commit * commit);
while (childCommitCnt >= max)
{ childCommitCnt /= 2; }
if(0 == childCommitCnt)
{ childCommitCnt = commit; }

This formula uses the specified values for the commit and max parameters. The 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 the max 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:

-sqlParam1 0 -sqlParam2 2 -sqlParam3 4

These parameters can be bound to the corresponding parameter markers, 1, 2, and 3, within the following SQL statement:

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=?))

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

You can also set optional parameters prunelevel. threadCount, and validatedel to limit the scope of the offline cleanup operation.

Before you run the utility in the offline mode, review the following considerations and limitations:

• To run the utility offline, you must first take your HCL Commerce environment offline.
• When you run the utility offline, the utility temporarily disables the foreign key constraints for your database. The constraints are disabled to ensure that the utility can delete the identified objects. When the constraints are disabled, the data integrity preservation during the cleanup process is lost until the constraints are enabled again after the process completes. To avoid disabling the constraints, you are recommended to run the utility in the default online mode unless the objects that you want to delete are in a large table hierarchy.
• Before you run the utility offline, you should disable replication services, such as HADR, and synchronize any replicated database tables. If you do not disable the services, the services can replicate the disabling of referential integrity checks and foreign key constraints into the replicated environment, which does not need the checks or constraints disabled. After the utility completes running offline, enable the replication services again. For more information about how to disable these services, review the documentation that is available from your database provider.

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.

Example: Delete user objects

./dbclean.sh -object user -type registered 
-instancexml /opt/WebSphere/CommerceServer90/instances/demo/xml/demo.xml 
-db CD040302 -commit 500 -max 8000 -check_object_only yes

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