This post is specific to WebSphere Commerce. If you’re supporting WebSphere Commerce and you don’t know what dbclean is, get yourself a cup of coffee and start researching.
This post does not cover why to run dbclean or what parameters to use with dbclean – search my blog for more posts on that using the search box at the upper right or google. This post covers the nitty gritty of things you might want to consider in actually running DBClean, and some things to check if you’re having problems. I literally get NOTHING when I google some of the topics in this post, so that tells me others might also find this useful.
Where to Run DBClean
If you have a single tier single environment, then “where” obviously isn’t an issue. But the clients I support have an absolute minimum of 5 different application servers that dbclean could be run from, and usually a few more. A client I was working on today had 15 different places I could have run dbclean from.
To run dbclean, you need:
- An app server and not a database server if you’re in a multi-tier implementation.
- Access on that app server to the WAS Administrative ID – at least while you’re setting dbclean up – I prefer to run dbclean with a dedicated ID
- An ID with minimally select and delete on all WebSphere Commerce tables – DATAACCESS is a better choice if possible
Most of the environments I support have a server that is dedicated as a build and deploy server. It has all the databases in various environments cataloged on it (dev, Stage, Prod, etc), and it also has an installation of WebSphere Commerce with a ‘Default’ WebSphere Commerce instance installed on it.
You can theoretically run dbclean from any app server that can connect to the database you want to run it against. If, for some reason, I don’t have the ability to run it from the build server, then I usually prefer the staging application server.
Remember that if you’re setting it up right most of dbclean’s work will be against the production database. You should have fewer than 5 items you’re also pruning in your staging environment, and you may have additional ongoing pruning to do in dev, QA, or other lower environments.
Using dbclean with a Different ID
I like to run dbclean using a different id than the WAS Administrator’s ID. The reasons for this are:
- I’d prefer that I’m the only one with access to the crontab to deal with changing or accessing the dbclean stuff that runs on a regular basis
- DBClean requires me to specify the password of a privileged database user’s id at the command line, and possibly store it in a file (encrypted, please). I want only a handful of people to have access to that.
- PCI requires the separation of duties as much as possible, and this will be a shared id with limited access and access controls
There are several things to set up for this other id – which I’ll call the dbclean ID – before you can run dbclean with it.
- If using a build server or other server that does not have the same WebSphere Commerce instance on it that your target WebSphere Commerce database belongs to, copy over the instance xml from the real instance to the same path it would be on if the instance did exist on this server. You can rename it. In my case, I copied over /opt/IBM/WebSphere/CommerceServer70/instances/comm01/xml/comm01.xml from an application server to my build server and called it /opt/IBM/WebSphere/CommerceServer70/instances/comm01/xml/dev_comm01.xml. I changed the name because I’ll be doing pruning on 4 total environments from this server, and I need to be able to keep their instance xml’s straight. Keep the permissions on these files as tight as possible, and be very careful with them, they may have sensitive information in them such as passwords or merchant keys.
- Add your dbclean id to the primary group of the WAS Administrative ID. This is the group that owns most files in /opt/IBM/WebSphere/CommerceServer70.
- Grant your WAS Administrative group full permissions on:
- Everything in /opt/IBM/WebSphere/CommerceServer70/bin (particularly dbclean.sh)
- /opt/IBM/WebSphere/CommerceServer/instances/default/logs/DBClean, creating this directory if it does not already exist
Potential Errors
I saw this error when I was attempting to use the default instance xml instead of copying over the real instance xml. If you’ve got instance xml issues you’ll probably see the same type of error.
/opt/IBM/WebSphere/CommerceServer70/bin/dbclean.sh -object order -type stale_guest -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/default/xml/default.xml -db WC036D01 -dbuser db2clean -dbschema wscomusr -dbpasswd xxxxxxx -dbtype db2 -commit 500 -max 1000000 -days 90
Exception in thread "main" java.lang.NullPointerException
at com.ibm.commerce.clean.util.InstanceXML.getDBType(InstanceXML.java:149)
at com.ibm.commerce.clean.util.DBCleanInput.getAdditionalParameters(DBCleanInput.java:175)
at com.ibm.commerce.clean.util.DBCleanInput.checkMendatoryArgs(DBCleanInput.java:162)
at com.ibm.commerce.clean.DBClean.parseArgs(DBClean.java:283)
at com.ibm.commerce.clean.DBClean.main(DBClean.java:68)
If you miss permissions on the areas I described above, you’ll also see errors.
Scheduling dbclean in the Crontab
I have scripts that I use to run dbclean that have reporting and encryption/decryption logic in them, so I’m never running dbclean directly from the crontab. However, I have in the past, and if you do, this sample of a crontab entry that works may help:
10 23 * * * . /db2home/db2inst1/.bash_profile; cd /opt/IBM/WebSphere/CommerceServer70/bin/; /usr/bin/sh ./dbclean.sh -object order -type stale_guest -instancexml /opt/IBM/WebSphere/CommerceServer70/instances/comm09p01/xml/comm09p01.xml -db wc009p01 -dbuser db2inst1 -dbpasswd xxxxxx -commit 1000 -days 60 > /db2home/db2inst1/data_cleanup/data_cleanup.order.stale_guest.out 2>&1
I’ve found the /usr/bin/sh to be useful in getting it to run.
