DB2 for zOS Index on Expression

DB2 for z/OS version 9 brought in a new feature called "Index on Expression". Yes, you can create indexes on frequently used expressions in your queries. For example, if you often do a SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', then creating an index on UPPER(LAST_NAME) will be useful.

Until V8, you can create index on just columns, not on expressions/functions etc. So, you would have created an index on LAST_NAME, but DB2 would have ignored it because when you use a function on a column, DB2 ignores index access. But starting in V9, you can create an index on UPPER(LAST_NAME) and DB2 will use this index.

Now, to the tricky part ...

I tried to create an index on UPPER(LAST_NAME), DB2 complained that I need to specify 'locale' name. After reading a little bit about 'locale names', I specified UPPER(LAST_NAME, 'En_US') and DB2 happily created the index. I also ran runstats on the table. However, when I did an explain on the query SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', DB2 didn't seem to pick up the newly created index. That puzzled me.

After reading a little further, I found that I need to specify the same 'locale' in the query too. After specifying the 'locale', I did an explain and DB2 picked up the index. This is the modified query:

SELECT FIRST_NAME WHERE UPPER(LAST_NAME, 'EN_US') = 'SMITH'

Keywords: DB2, z/OS, Index, Expression, Upper, Locale, function

UNIQUE WHERE NOT NULL - Explained

I always had confusion over the meaning of indexes created with UNIQUE WHERE NOT NULL clause on DB2 for z/OS. The manuals don't seem to do a good job explaining it. I understand it now and here is how it works.

It means its ok to have multiple rows with null values in columns included in the index, but its not ok to have rows where the combination of the columns included in the index is not unique and all the columns are not null. I tested this on a three column index. I was able to insert rows rows shown below just fine, the only duplicate exception I got was where column a, b, c were the same value for two different rows and none of the 3 columns contained a null value.

  COL1  COL2  COL3
  ----  ----  ----
   A     B     C
   A     B     -
   A     B     -
   A     -     -
   A     -     -
   -     -     -
   -     -     -

CREATE UNIQUE WHERE NOT NULL INDEX PRE.IDX1
       ON PRE.TEST
       (COL1   ASC
       ,COL2   ASC
       ,COL3   ASC)
       USING STOGROUP ASTERISK
             PRIQTY 12
             SECQTY 10800
             ERASE NO
       FREEPAGE 0
       PCTFREE 0
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 2097152  K;

CREATE TABLE PRE.TEST
       (COL1     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL2     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL3     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       )
       IN DB.TS;