Indexes on expression

LOADPLUS natively supports reorganizing indexes that contain a key derived from certain expressions and reorganizing table spaces that contain those indexes.

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

  • Some content is unavailable due to permissions.

    MOD

  • Some content is unavailable due to permissions.

    DIGITS
  • (BMC.DB2.SPE2501)CHAR
  • (BMC.DB2.SPE2507) CONCAT

Related topic

Sizing and performance

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

Restrictions on indexes on expression

When any of the following conditions exists, the utility does not provide native support and invokes DSNUTILB instead:

  • A SUBSTR function has one of 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 has one of 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 has one of the following characteristics:
    • The function references more than one column.
    • The column data type is not DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.

  • Some content is unavailable due to permissions.

    A MOD function has one of the following characteristics:
    • The function includes a length parameter
    • The column data type is DECIMAL
  • (BMC.DB2.SPE2501)A CHAR function with a column data type REAL, DOUBLE, FLOAT(21), or FLOAT(53).

  • (BMC.DB2.SPE2507)A CONCAT function with a column data type REAL, DOUBLE, FLOAT(21), or FLOAT(53).

    Important

    PLUS Utilities supports concatenated key columns that are defined with the CONCAT statement (CONCAT(KEY_COL1, KEY_COL2). Concatenated key columns defined with || (KEY_COL1 || KEY_COL2) will require DSNUTILB.

​​​​​​

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*