This blog promotes knowledge sharing through experience and collaboration. For more product information, visit our WebSphere Commerce CSE page. For easier navigation, utilize the Categories to find posts that match your interest.
Offline Mode: DBClean's New Option in Fix Pack 9
One of the WebSphere Commerce site maintenance task is keeping the database in good shape by performing regular cleanups to purge unwanted data. This task is crucial before exporting and importing data between environments.
Prior to Fix Pack 9, DBClean would be run to clean object all at once. If you had not performed this task regularly, you could face a longer down-time need as there may be millions of objects that need to be removed. Since this is not always feasible in a production-live environment, Fix Pack 9 introduces DBClean's offline mode.
Before covering the details on offline mode, let's start with a quick view at how DBClean works using lower Fix Pack versions.
DBClean uses delete statements stored in the CLEANCONF table to perform deletes against a particular table. Once the delete executes, it then cascades from the root table (which is the particular table we want to delete from) to its descendant tables.
For instance, in a high traffic environment you're likely to accumulate millions of guest user objects. To remove these unwanted objects from the site's database we would be running the DBClean script for user objects of type guest; and consequently executing the following query from the CLEANCONF table.
DELETE from MEMBER where member_id in (select users_id
where (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ? and (users_id > 0))
The execution will do a cascade delete to all descendant tables of the MEMBER table (and its child tables).
If you look at the MEMBER table information in Knowledge Center, you see 99 child tables being referenced. Each child table has its own child tables, and so on. This becomes a deeply and widely nested schema.
If you were to delete a few hundred MEMBER records (even a thousands), this task may be okay. However, if you had more than a million records that need to be cleaned, this becomes a very long and expensive task.
Offline mode has been introduced to resolve the need to clean records in this type of scenarios.
The solution removes the use of cascade deletes generating explicit deletes for each table in the hierarchy. The explicit deletes are all executed concurrently in a bottom-up approach.
The offline mode cleanup starts by shrinking the hierarchical tree by checking tables to if there is data to be deleted. If no records found, the child table and subsequent descendant child tables are pruned from the tree. The following image illustrates an example where T1 is the root table (in our example it would be the MEMBER table); and '1' and '2' are the two first level of the hierarchy that will check against. The blue tables T5 and T7 do not have any records referenced from their parent tables. Therefore, we proceed removing them (and their subsequent child tables) from the table hierarchy.
Next, it will disable the foreign-key relationship among the tables of the reduced hierarchy, and generate the explicit deletes for each.
As previously mentioned, the deletes will be executed in a bottom-up approach, and can concurrently be executed at the same hierarchy level. The following image illustrates, the sequence of execution.
After generating the delete statements, it temporarily disables referential integrity checking for each disabled constraint to ensure that there are no foreign key contraint violations when performing the deletes.
Once the deletes are executed, it will enable the foreign key constraints and referential integrity check. This task may be time consuming as it kicks off the check for each table to ensure there are no holes in the data; nor any orphan records.
How to run DBClean using offlineMode?
The following are the new optional parameters introduced in FixPack 9.
Note: prior to running dbclean, you must bring the WebSphare Commerce environment offline; and turn off any replications services that would read or alter the database.
From the command line you'd be typing:
dbclean -dbtype db2 -db mall -dbuser wcs -dbpasswd **** -object guest -type user -offlineMode yes -pruneLevel 1 -threadCount 4 -validateDel no
Database Cleanup Utility
Database Cleanup utility command script
Database maintenance guidelines