Case insensitive indices

In my experience DB2 is one of the most correctly working databases. Where others are, lets say, a bit more flexible, DB2 follows the given path down to the last bit. An example we just fell over: Case sensitivity.

These days we installed a new product which usually works with a database system made in Redmond. We wanted it to work with DB2 and were told that - in theory - there should be no big problem. Praxis showed that there's always Murphys law awaiting you.

At one point the software does a query with condition like „WHERE upper(NAME)='ABCD' “. While in the beginning there was no problem, we found that several data imports later this little phrase slowed down the whole system. The cause: Although there was a (case sensitive) index on the NAME column, DB2 had to do an upper() on every single row.

While DB2 v9 for z/OS allows you to build an index using the upper() function, DB2 v8 doesn't give you this possibility (yet). But there's a workaround. Here's how we got around.

First, We added a generated column, containing the upper case text:

-- alter table
SET INTEGRITY FOR A001.FOOBAR OFF ;

alter table A001.FOOBAR
  add column  NAME_UP
  GENERATED ALWAYS AS ( UPPER(NAME) )  ;

-- enable integrity again and fill column; may take a while
SET INTEGRITY FOR A001.FOOBAR
    IMMEDIATE CHECKED FORCE GENERATED ;

Then, we added an index on the newly created column:

-- drop old index; create an index using new upper_column
DROP    INDEX A001.FOOBAR_IDX1;
CREATE  INDEX A001.FOOBAR_IDX1 ON FOOBAR ( NAME_UP, XID, YID );

After that, the internal optimizer recognized that it could use the GENERATED column and its index, and the import jobs returned to the high velocity they had shown in their very first moments: Instead of hours or even days, everything was done within a couple of minutes.