Db2 anti-pattern: functions on indexed columns

The anti-pattern in one example

Assume SALES_ORDER has an index whose leading key is ORDER_TS. A developer wants every order from 2025 and writes YEAR(ORDER_TS) = 2025. The result is logically clear, but the search request is expressed in terms of a calculated year, while the ordinary index is ordered by complete timestamp values. Unless Db2 applies a documented transformation, supports that particular function form as indexable, or finds a matching expression index, the predicate can provide less useful index matching than a direct timestamp range.

1
2
3
4
5
6
7
8
9
10
-- Common anti-pattern
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS
FROM SALES_ORDER
WHERE YEAR(ORDER_TS) = 2025;
 
-- Usually the clearer search-key form
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS
FROM SALES_ORDER
WHERE ORDER_TS >= TIMESTAMP('2025-01-01-00.00.00')
  AND ORDER_TS <  TIMESTAMP('2026-01-01-00.00.00');

The rewrite is not a hint and does not force index access. Db2 remains cost based: a table space scan can still be cheaper when most rows qualify, the table is small, statistics favor a scan, or another access path wins. The improvement is that the optimizer now has two ordinary range boundaries on ORDER_TS. It can compare those boundaries with the ordered index keys without deriving YEAR for each candidate.

Sargability and IBM predicate terminology

Performance discussions call this property sargability, short for a searchable argument. A sargable condition exposes a column in a form that can delimit a useful search. IBM Db2 documentation uses more exact terms. A predicate can be indexable, used as a matching index predicate, used for index screening, evaluated at stage 1 after data access, or evaluated at stage 2. These labels are related but not interchangeable.

• Matching index access uses leading index key columns to establish ranges or probe values. This is normally the most valuable outcome for a selective predicate.
• Index screening can reject keys while Db2 scans an index, but a screening predicate does not necessarily narrow the initial matching range.
• Stage 1 predicates are handled by the data manager. Some are indexable and some are applied only after a data page is accessed.
• Stage 2 predicates are evaluated later and commonly require more processing for rows that survived earlier filtering.

IBM's documented processing order is useful: matching index predicates are applied during index access, then index screening, partition or page-range screening, other stage 1 predicates on data, and finally stage 2 predicates. Therefore, saying “the query uses the index” is not enough. A full index scan with zero matching columns can be much more expensive than a selective matching probe of the same index.

Why a function can break matching

A normal index stores keys derived from the indexed columns in key order. If LAST_NAME is indexed, Db2 can seek to values beginning at SMITH for LAST_NAME = 'SMITH'. For UPPER(LAST_NAME) = 'SMITH', the requested ordering is the ordering of uppercase results, not necessarily the ordering of the stored LAST_NAME keys. Db2 might have to compute UPPER for many values. Arithmetic has the same shape: SALARY + 1000 > 90000 asks about a derived value instead of directly exposing the SALARY boundary.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Expressions that deserve investigation
WHERE UPPER(LAST_NAME) = 'SMITH'
WHERE DECIMAL(AMOUNT, 12, 2) = :AMOUNT
WHERE SALARY + 1000 > 90000
WHERE SUBSTR(ACCOUNT_CODE, 3, 4) = '4711'
WHERE DATE(EVENT_TS) = CURRENT DATE
 
-- Direct forms, when semantics allow
WHERE LAST_NAME = :LAST_NAME_NORMALIZED
WHERE AMOUNT = :AMOUNT
WHERE SALARY > 89000
WHERE EVENT_TS >= TIMESTAMP(CURRENT DATE)
  AND EVENT_TS <  TIMESTAMP(CURRENT DATE + 1 DAY)

Do not rewrite algebra mechanically. SALARY + 1000 > 90000 becomes SALARY > 89000 only when data types, overflow behavior, null handling, decimal scale, and business meaning make the expressions equivalent. Likewise, removing UPPER is correct only when stored data and comparison rules provide the required case behavior. Performance never justifies changing the answer.

Functions are not all-or-nothing

The beginner rule “never put a function on an indexed column” is a useful warning but a poor technical conclusion. IBM's Db2 for z/OS predicate summary documents function forms that might be indexable under stated conditions, including certain predicates involving DATE, YEAR, and SUBSTR when the substring starts at position 1. Optimizer transformations and release improvements can also change what Db2 recognizes.

“Might be indexable” still does not promise matching access or a low-cost plan. The exact operator, argument types, constants, host variables, index definition, key sequence, statistics, function level, and surrounding Boolean expression all matter. An OR can change how otherwise useful predicates combine. A function on a non-leading key might screen but not match. Treat the anti-pattern as a reason to inspect the access path, not as a syntax ban.

Range rewrites for dates and timestamps

Date extraction is the most common case. Build a half-open interval: include the start and exclude the start of the next period. This avoids guessing the final timestamp of a day, month, or year. It remains correct if timestamp precision changes, and adjacent intervals never overlap.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- One calendar day
WHERE EVENT_TS >= TIMESTAMP(DATE('2025-08-15'))
  AND EVENT_TS <  TIMESTAMP(DATE('2025-08-15') + 1 DAY)
 
-- One calendar month
WHERE EVENT_DATE >= DATE('2025-08-01')
  AND EVENT_DATE <  DATE('2025-09-01')
 
-- One calendar year
WHERE EVENT_DATE >= DATE('2025-01-01')
  AND EVENT_DATE <  DATE('2026-01-01')
 
-- Parameterized reporting window
WHERE EVENT_TS >= :WINDOW_START
  AND EVENT_TS <  :WINDOW_END

Avoid BETWEEN with a fabricated value such as 2025-08-15-23.59.59.999999. It is easy to omit valid values when a column has greater precision, and application code must know Db2's exact representation. The next-boundary form is simpler. Also calculate stable boundaries once in the application or in a suitable non-column expression; do not repeatedly transform the indexed timestamp merely to manufacture a date.

Strings, case folding, and prefixes

String functions need semantic care. SUBSTR(CODE, 1, 3) = 'ABC' describes a prefix and is not the same as every arbitrary substring. IBM specifically documents potentially indexable SUBSTR forms when the start is 1. A range can sometimes express a prefix, but deriving a safe upper bound depends on encoding and collation; LIKE 'ABC%' is clearer when its wildcard and escape semantics fit the requirement. SUBSTR(CODE, 3, 3), TRIM(CODE), and UPPER(NAME) describe different derived key spaces and should be checked individually.

Case-insensitive search is often a data-design question. An application can store a separately normalized search column, enforce its value, and index it. That makes the search contract explicit. Alternatively, a supported expression-based index can index UPPER(LAST_NAME). Choose based on data governance, query frequency, DML overhead, collation requirements, and whether every application uses exactly the same normalization rule.

Expression-based indexes

Current Db2 for z/OS supports indexes based on supported general expressions. They are appropriate when an important, stable query genuinely needs the expression and a semantics-preserving base-column rewrite is unavailable. The expression in SQL must be compatible with the indexed expression; a merely similar calculation should not be assumed to match.

1
2
3
4
5
6
7
8
9
10
11
-- Illustrative expression-based index.
-- Validate allowed expressions and options for your Db2 level.
CREATE INDEX APP.IX_CUSTOMER_UPPER_NAME
    ON APP.CUSTOMER (UPPER(LAST_NAME));
 
SELECT CUSTOMER_ID, LAST_NAME
FROM APP.CUSTOMER
WHERE UPPER(LAST_NAME) = 'SMITH';
 
-- Always EXPLAIN the statement after creating the index.
-- An available index is not necessarily the cheapest access path.

Expression indexes are not free. Inserts and updates must calculate and maintain extra keys. The index consumes disk, buffer-pool space, log volume, utility time, and operational attention. IBM also documents restrictions for expression-based indexes; for example, some CREATE INDEX options that apply to ordinary indexes are unavailable. Check the SQL reference for the installed release rather than copying generic Db2 LUW syntax or an example from another function level.

Choose an expression index when

• The expression is required by business semantics and appears in frequent searches.
• The expression is supported, deterministic as required, and stable across applications.
• EXPLAIN shows that the index can provide useful matching for representative values.
• Measured read savings exceed the storage and write-maintenance cost.

Prefer a rewrite or data-model change when

• A direct range on the base column expresses exactly the same requirement.
• The expression varies among callers and would create many narrowly useful indexes.
• A normalized business attribute deserves its own governed and indexed column.
• The table is write heavy and the extra index would cost more than it saves.

Data types and implicit conversion

Function problems often begin as data-type problems. A numeric identifier stored as INTEGER should normally be compared with an INTEGER host variable, not with a character parameter that encourages conversion. A DATE column should receive a DATE value rather than a display string. Character length, encoding, decimal precision and scale, and timestamp precision can influence predicate eligibility and estimates.

1
2
3
4
5
6
7
8
9
10
11
-- Risky: transformation is applied to the indexed column
WHERE CHAR(ACCOUNT_ID) = :ACCOUNT_ID_TEXT
 
-- Preferred: bind an INTEGER host variable
WHERE ACCOUNT_ID = :ACCOUNT_ID_INTEGER
 
-- If a parameter marker needs an explicit type, cast the value side
WHERE ACCOUNT_ID = CAST(? AS INTEGER)
 
-- Preserve a DATE column as the search key
WHERE BUSINESS_DATE = CAST(? AS DATE)

Casting the value side is not universally safe. A failed conversion can raise an error, rounding can change results, and converting one side can still affect selectivity or compatibility. The best fix is usually to align the application parameter definition, table column, and business domain. For static SQL, inspect host-language declarations. For JDBC or other dynamic SQL, verify the setter method and parameter metadata rather than assuming the text shown in a trace is the bound type.

Composite indexes and matching columns

A function on one key can affect the rest of a composite index. For an index on (CUSTOMER_ID, ORDER_TS), equality on CUSTOMER_ID followed by a direct range on ORDER_TS can use two matching columns. If the query instead transforms ORDER_TS, Db2 might match CUSTOMER_ID only and inspect many orders for that customer. If the leading CUSTOMER_ID predicate is also hidden by a function, useful matching can disappear altogether.

1
2
3
4
5
6
7
8
9
10
-- Index: IX_ORDER_1 (CUSTOMER_ID, ORDER_TS)
 
-- Better shape for two-key matching
WHERE CUSTOMER_ID = :CUSTOMER_ID
  AND ORDER_TS >= :FROM_TS
  AND ORDER_TS <  :TO_TS
 
-- Investigate: second key is expressed as a derived value
WHERE CUSTOMER_ID = :CUSTOMER_ID
  AND DATE(ORDER_TS) = :ORDER_DATE

Equality predicates generally preserve the ability to continue matching into following keys, while a range typically ends matching for later keys. Index key order therefore remains important even after a function is removed. A rewrite can make a predicate eligible, but it cannot repair an index whose leading keys do not fit the query.

Verify with EXPLAIN instead of guessing

IBM recommends EXPLAIN to investigate selected access paths. Capture the original and rewritten statements under comparable catalog statistics, bind options, special registers, parameter assumptions, and Db2 function level. A test with an empty table or default statistics can produce a plan that says little about production.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXPLAIN PLAN SET QUERYNO = 9302 FOR
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TS
FROM SALES_ORDER
WHERE ORDER_TS >= TIMESTAMP('2025-01-01-00.00.00')
  AND ORDER_TS <  TIMESTAMP('2026-01-01-00.00.00');
 
SELECT QUERYNO,
       PLANNO,
       METHOD,
       ACCESSTYPE,
       MATCHCOLS,
       ACCESSCREATOR,
       ACCESSNAME,
       INDEXONLY
FROM PLAN_TABLE
WHERE QUERYNO = 9302
ORDER BY PLANNO;

• ACCESSTYPE identifies the broad access method, such as index access or a table space scan.
• ACCESSNAME identifies the selected index when applicable.
• MATCHCOLS shows how many index key columns are used for matching.
• INDEXONLY helps show whether Db2 can satisfy the needed columns from the index without fetching table data pages.
• Predicate tables and visual EXPLAIN tools can reveal whether each predicate is matching, screening, stage 1, or stage 2.

Do not optimize for MATCHCOLS alone. Compare estimated cost, cardinality, join order, sort activity, prefetch, partition access, and index-only potential. Then validate execution with accounting or monitoring data: elapsed time, CPU, getpages, synchronous reads, rows examined, and statement frequency. A microsecond saved on a rare query might not justify an index maintained by millions of updates.

A safe tuning workflow

• Identify the exact predicate and the index keys, including key order and data types.
• Confirm that the proposed rewrite returns identical rows for nulls, boundaries, rounding, case, encoding, and timestamp precision.
• Run EXPLAIN for both forms with current RUNSTATS and representative values.
• If no safe rewrite exists, evaluate a normalized column or expression-based index.
• Measure read benefit and write, logging, storage, REORG, RUNSTATS, COPY, and recovery costs.
• Recheck the access path after deployment and protect important static SQL through the site's normal bind and access-path management process.

Explain It Like I'm Five

Imagine a phone book sorted by full last name. If you ask for SMITH, the librarian can open near S and find it quickly. If you ask, “Please erase spaces, change every letter to uppercase, and then see whether the answer is SMITH,” the phone book is not necessarily sorted by that changed version. The librarian may need to change lots of names before comparing them. You can either ask using the name exactly as the book is sorted, keep a second phone book sorted by the changed names, or store a clean search name. EXPLAIN is the librarian's report showing which book and shortcut Db2 chose.

Exercises

• Rewrite MONTH(INVOICE_DATE) = 8 for August 2025 without including August from other years. Explain both boundaries.
• Given an index on (BRANCH_ID, TRANSACTION_TS), predict how matching might differ between a direct timestamp range and DATE(TRANSACTION_TS) = :D.
• Review CHAR(EMPLOYEE_ID) = :ID_TEXT. Specify a matching host-variable type and list conversion errors that testing should cover.
• Design an experiment that compares UPPER(LAST_NAME) with a normalized search column and an expression-based index. Include DML cost as well as SELECT response time.
• EXPLAIN an original and rewritten predicate in a test subsystem. Record ACCESSTYPE, ACCESSNAME, MATCHCOLS, INDEXONLY, estimated cost, and predicate classification.
• Find a function predicate in an application where removing the function would change results. Document why correctness requires keeping the expression or changing the data model.