Hope to see you at the IDUG North American Technical Conference in Phoenix, Arizona – May 12-16!

NEVER drop a WebSphere Commerce base index

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.

But I’m always growing and learning and trying new things, and that policy was something we started back on Commerce 5.6. So I noticed within the last 6 months the following statements in the Commerce Info center(http://publib.boulder.ibm.com/infocenter/wchelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.commerce.database.doc%2Fconcepts%2Fcsddatabase_main_dup.htm):

Indexes

The following changes are supported

  • Adding new indexes.
  • Altering an existing index
    • Adding a column or more to the end.
    • Dropping a column or more.
  • Dropping an existing index.

The following changes are not supported:

  • Adding unique indexes
  • Changing the uniqueness
  • Functions on columns and indexes.
  • Over-indexing
Dropping an existing index is explicitly supported, as long as it doesn’t “Change the uniqueness”. I’ve also been listening to the DB2Night Show a lot, and Scott Hayes is a big fan of dropping indexes. So when I was analyzing some SQL and finding indexes to support it, I came across an opportunity to try it out.
The SQL I was analyzing needed the following index for index-only access, and since the SQL in question was going to be executed quite frequently (via update and insert triggers on catalog-related tables), I was all for it. This is the new index:
CREATE UNIQUE INDEX WSCOMUSR.UI_CATGROUP01 ON WSCOMUSR.CATGROUP (IDENTIFIER , MEMBER_ID) INCLUDE (CATGROUP_ID) ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
And the old index:
CREATE UNIQUE INDEX WSCOMUSR.I0000067 ON WSCOMUSR.CATGROUP (IDENTIFIER , MEMBER_ID) ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS

The only difference between the two is an include column (sometimes also referred to as a covering index)  - catgroup_id. From the DBA’s point of view, this does not “Change the uniqueness” in any way. The uniqueness is still enforced on the same two columns and on them only. So I create the new one, and then drop the base Commerce index.

Fast forward a week, and a load using idresgen fails. Typically the error messages aren’t useful without cranking up the logging. Once the developer cranks up the logging, we see:

> 2012-04-27 19:07:00.245, <Thread-8>, com.ibm.wca.IdResGen.Handler.DefaultHandler: efaultHandler::initialize, S2
The Table [CATGROUP] did not define a resolve key, and there is no unique index on the table

That rings a bell for me and I go look to see if it’s the same table I remember dropping a single index on after creating a functionally equivalent one, and sure enough, it is. Perplexed, I verify the basics like the fact that it is indeed unique and so forth. Everything checks out. The first thing I try is to change the index name back to the Commerce name. Which makes no sense, of course, but none of this does.

The change in the index name predictably does not resolve the issue. So just to cover all my bases, I drop the index and re-create it without the include column. And to my utter shock, idresgen then works. I’m still a bit speechless. I did not change the uniqueness in any way, and I have no idea how Commerce would even be aware of the change.

So it’s back to my old policy – NEVER drop a WebSphere Commerce base index.

2 comments… add one

  • Did you open a PMR to get RCA for this? Because this sounds like typical application developer bad behavior.

    I’ve seen a packaged application use the query, ‘select distinct tabschema, tabname from syscat.columns’ to determine if a table exists – instead of just looking for an entry in syscat.tables – thereby completely removing the possibility of using aliases in the database.

    Reply
    • Database design questions with IBM WebSphere Commerce support are not a battle I’m fond of fighting. I suppose I should.

      Reply

Leave a Comment