• “expression op (subq)”.
DB2 9 has changed the rules. A note appeared after each of the aforementioned predicates, stating that it may now be indexable by an index on expression. Just like sargable and non-sargable vanished from the DB2 documentation years ago, so, too, does one more stage 1, and in this case, indexable, restriction. This is great news for programmers. The two previous examples could be considered indexable upon creation of the indexes in Figure 2.
A word of warning applies if you decide to use the functions UPPER or LOWER. When using an index on either function, it won’t work without locale. For more information on how to use locale, see the z/OS XL C/C++ Programming Guide (SC09-4765). There’s an open APAR (PK68295) that will remove the locale requirement when specifying the built-in scalar functions UPPER or LOWER.
Index on expression could be quite beneficial when working with a data warehouse. There are many situations where warehouse queries need to use expressions as the only way to solve a business problem when running a query.
You must follow a few rules to take advantage of this DB2 9 NFM feature. First, the expression must contain a referenced column from the table on which the index is being created. However, referenced columns and the result columns can’t be Large Object (LOB), XML, or Decimal Floating-Point (DECFLOAT) data types. There’s also a list of rules to follow if you want to use this feature. The expression can’t contain:
• A subquery
• An aggregate function
• A not deterministic function
• A function that has an external action