Database Cleanup utility command script

The Database Cleanup utility (dbclean) removes unused or obsolete objects from the database.

Before beginning

Run this utility from the Utility server Docker container. 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 we 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, encoded as a JDBC type 4 pattern URL. If you leave this field empty, the utility read 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 specified by the dbuser or dbschema parameter. If not specified, the system prompts you to enter the password.

dbtype

Optional: The database type. Default is DB2.

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.

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 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 called DBClean.timestamp.log is created in the following directory: WC_eardir/xml/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 we are using DB2, the JDBC driver might be com.ibm.db2.jcc.DB2Driver. If we 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 we 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. We 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.

Default is jdbcDriver=com.ibm.db2.jcc.DB2Driver jdbcUrlPrefix=jdbc:db2://. Other combinations might work but are not tested.

sqlmode

Optional: We 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, we 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, we 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 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, we must increase the index sequentially for sqlParam% parameter in your utility command. For example, we 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, we 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. We can set the following values for this parameter:

yes

The Database Cleanup utility runs in the offline mode. The utility cannot detect whether the environment is offline. The utility prompts you to confirm that the environment is offline.

no

The Database Cleanup utility runs in the online mode. This value is the default value.

We can also set optional parameters prunelevel. threadCount, and validatedel to limit the scope of the offline cleanup operation. Before running the utility in the offline mode, review the following considerations and limitations:

• To run the utility offline, we must first take the WebSphere Commerce environment offline.

• When you run the utility offline, the utility temporarily disables the foreign key constraints for the 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, we are recommended to run the utility in the default online mode unless the objects to delete are in a large table hierarchy.

• Before running 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 the 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. We 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. We 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. We can use this parameter only when you run the utility offline. We 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 located in the WC_eardir/xml/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 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.

• To delete obsolete files 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

Runtime environmentThe following example command runs the utility to 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

Runtime environment The following example command runs the utility in the offline mode to delete the same information as the preceding example:

./dbclean.sh -object user -type registered 
-instancexml /opt/WebSphere/CommerceServer90/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

Related concepts
Database Cleanup utility

Related tasks
Running utilities from the Utility server Docker container
Running the Database Cleanup utility in a staging environment
Configure the Database Cleanup utility to delete more tables and columns