While this post is specific to WebSphere Commerce, it covers concepts that may apply to tuning other delete statements as well.
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.
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.
This post is specific to WebSphere Commerce. In my experience, usually when Clients upgrade from one WebSphere Commerce version to another (with 6 to 7 being the current focus), they also upgrade or change DB2, the OS, the Hardware – basically everything. To that end my upgrade experience assumes moving at least from one server to another. I’m also not providing a recipe here, but sharing a specific experience – your experience may be different.
This post is specific to WebSphere Commerce. NEVER drop a WebSphere Commerce base index. I believe I’ve stated this in at least one previous blog entry.
This post is specific to WebSphere Commerce. If you’ve always assumed that stagingprop copied the whole row and every change, I’ve found an execption. Stagingprop does not copy the OPTCOUNTER column.
This post is specific to WebSphere Commerce. We spent a fair amount of time on this, both on a SUSE server and a Red Hat Server. The problem manifested in different ways. The main thing that became obvious to me is that Commerce was not creating the Commerce database as the dbaUser specified in createInstance.properties file, but was instead using the id that was running the Commerce Instance creation. While one workaround is simply to allow this to happen, and grant dbadm and secadm to the user you wanted in the first place later, we were on the first server of at least 4 in a build and wanted to have a reproducible process.
So this is a good page in the Commerce info center. It sets forth naming standards for custom objects in WebSphere Commerce databases. I know for a fact that other naming standards work in daily operation. I wonder if using these naming standards makes upgrades go more smoothly (or like some Commerce features that is an intended future use of these). The only one that I will continue to ignore is limiting indexes to 254 bytes – I just can’t see complying with that one and still getting good performance. Take a look here.