Posted by 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://www.ibm.com/support/knowledgecenter/SSZLC2_7.0.0/com.ibm.commerce.developer.doc/concepts/csddatabase.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
CREATE UNIQUE INDEX WSCOMUSR.UI_CATGROUP01 ON WSCOMUSR.CATGROUP (IDENTIFIER , MEMBER_ID) INCLUDE (CATGROUP_ID) ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
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.
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.
Database design questions with IBM WebSphere Commerce support are not a battle I’m fond of fighting. I suppose I should.