While this post is specific to WebSphere Commerce, it covers concepts that may apply to tuning other delete statements as well.
The request from the developers was something along the lines of “Help, Ember, this query needs to perform better”. Sometimes the query I’m working on is not one that shows up as a problem from the database administrator’s perspective, but one that is especially important in some part of application functioning. In this case, this query is related to the performance of searches done on the website – a particularly problematic area on this client.
Today we are going to talk about some random DB2 features that can’t stand in a blog of their own, but are worth discussing nonetheless. These are tidbits I had discovered during “DB2’s Got Talent” presentations, IDUG conferences, or “Hey, look what I discovered” moments.
Like many applications, WebSphere Commerce puts all tables in
USERSPACE1 unless they need larger page sizes. This actually works just fine for smaller and midrange implementations, but we have about one build a year that requires something else – either because of standards that client DBAs adhere to or because they actually are busy enough for I/O and separate buffer pools to matter. I recently got the experience to set up a framework and plan for this for a larger client and thought I’d share a few of my methods and thoughts.
Go-lives bring all kinds of opportunities to find problem SQL. A new site going live or a new site design going live can put a completely different load of SQL on a database. Depending on the level of load testing done, there can be some problems still to find. This post is an example of just one problem SQL statement (in this case, a delete) found and mitigated through database level analysis.
I don’t often make changes to existing tables. The WebSphere Commerce database comes with defintions in place. We make customizations that are largely adding columns or adding tables or adding indexes. It is pretty rare that we have to alter one of the handful of custom tables in some way.
I work on a team of DBAs, and we will frequently talk through an issue together to find the root cause. We are all capable of getting there on our own, but sometimes we realize that we’re not seeing something or think one of the others might see an issue faster.
One of my most popular posts that’s specific to WebSphere Commerce is The 10 Commerce Tables You Should Be Familiar With. This is an updated version of that post, since the preferred method of dealing with attributes has changed. As a result, this post is only applicable to WebSphere Commerce 7 and above, and some of the content is identical between the two posts.