Indexes on expression

With the exceptions described in Restrictions on indexes on expression, the utility currently supports expressions that contain the following SQL functions:

• UPPER
• LOWER
• SUBSTR
• DATE
• DAY
• MONTH
• YEAR
• (BMC.DB2.SPE2210)  MOD

For all other functions that IBM supports for indexes on expression, the utility invokes DSNUTILB.

 Restrictions on indexes on expressionWhen any of the following conditions exists, the utility does not provide native support and invokes DSNUTILB instead:

• A SUBSTR function can have the following characteristics:
  • The function is on a numeric column.
  • The function uses columns in the table space to supply start and end values.
  • The function is on a LOB column (for example, an index on an inline LOB column).
• An expression contains nested columns.
• An UPPER or LOWER function can have the following characteristics:
  • The function includes a length parameter.
  • The locale name has a value other than UNI or blank.
  • The column contains mixed or DBCS data, and the locale name is blank.
  • For ASCII and EBCDIC objects, the column contains mixed data.
• A DATE, DAY, MONTH, or YEAR function can have the following characteristics:
  • The function references more than one column.
  • The column data type is not DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.
• (BMC.DB2.SPE2210)  A MOD function can have the following characteristics:
  • The function includes a length parameter
  • The column data type is DECIMAL