DB2 Anti-Pattern: Bad Join and Predicate Patterns

Start with the intended row relationship

Before tuning syntax, describe the result in plain language. Is each order connected to exactly one customer? Can a customer have no orders? Are only active accounts eligible to match, or should customers without active accounts still appear? These questions determine the join type and where predicates belong. Indexes cannot repair SQL that models the wrong relationship.

Use a distinct correlation name for every table and qualify shared column names. For each join, identify the complete business key, including tenant, location, effective date, or version columns when they are part of the relationship. Joining only on ACCOUNT_ID when the real key is (BANK_ID, ACCOUNT_ID) can pair rows from different banks even though the statement contains an ON clause.

Cartesian products and missing join predicates

A Cartesian product combines every row from one input with every row from another input. CROSS JOIN requests that behavior explicitly and is valid for tasks such as generating all combinations from two tiny reference sets. The anti-pattern is an accidental Cartesian product caused by a missing or incomplete relationship predicate. If 20,000 orders meet one filter and 4,000 customers meet another, the intermediate result can reach 80 million combinations before later predicates remove most of them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Anti-pattern: CUSTOMER has no relationship to ORDERS
SELECT O.ORDER_ID,
       C.CUSTOMER_NAME
  FROM ORDERS   AS O,
       CUSTOMER AS C
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Correct relationship expressed explicitly
SELECT O.ORDER_ID,
       C.CUSTOMER_NAME
  FROM ORDERS AS O
  JOIN CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Explicit CROSS JOIN is appropriate only when all combinations are intended
SELECT S.SIZE_CODE,
       C.COLOR_CODE
  FROM PRODUCT_SIZE AS S
 CROSS JOIN PRODUCT_COLOR AS C;

ANSI JOIN syntax does not make bad joins impossible, but it separates relationship predicates in ON from final filters in WHERE and makes omissions easier to review. Check row counts during development. If two one-to-many tables are joined through their parent, multiplication might be valid rather than Cartesian, but it can still duplicate parent values. Do not hide that multiplication with DISTINCT until the relationship and required result grain are understood.

Non-sargable predicates, functions, and casts

Tuning discussions call a predicate sargable when it exposes a useful search argument. IBM terminology is more precise: predicates can be indexable or non-indexable, matching or index screening, and stage 1 or stage 2. An ordinary index on ORDER_TS is ordered by complete timestamp values. YEAR(ORDER_TS) asks Db2 about a calculated value, so it can provide less useful matching than direct timestamp boundaries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Often less useful for matching on ORDER_TS
WHERE YEAR(ORDER_TS) = 2026
 
-- Half-open range exposes the indexed timestamp
WHERE ORDER_TS >= TIMESTAMP('2026-01-01-00.00.00')
  AND ORDER_TS <  TIMESTAMP('2027-01-01-00.00.00')
 
-- Function and arithmetic anti-patterns
WHERE UPPER(LAST_NAME) = 'SMITH'
WHERE SALARY + 1000 > 90000
WHERE DATE(EVENT_TS) = CURRENT DATE
 
-- Possible direct forms, only when semantics remain identical
WHERE LAST_NAME = :NORMALIZED_LAST_NAME
WHERE SALARY > 89000
WHERE EVENT_TS >= TIMESTAMP(CURRENT DATE)
  AND EVENT_TS <  TIMESTAMP(CURRENT DATE + 1 DAY)

Never perform an algebraic rewrite without checking nulls, numeric overflow, decimal scale, timestamp precision, encoding, collation, and case rules. Removing UPPER changes the answer unless stored data and comparison semantics already provide the required normalization. Db2 also supports documented special cases and expression-based indexes, so “a function always disables an index” is inaccurate. The safe rule is to expose the base column when possible and inspect the real access path.

Implicit conversions and mismatched data types

A predicate can contain no visible function and still require conversion. Comparing an INTEGER identifier with character input, a DATE with a display string, or a DECIMAL with an incompatible scale asks Db2 to reconcile different types. Which operand is converted depends on SQL data type compatibility and precedence. Conversion can affect matching, selectivity estimates, errors, rounding, and result meaning.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Risky: conversion is deliberately placed on the indexed column
WHERE CHAR(ACCOUNT_ID) = :ACCOUNT_ID_TEXT
 
-- Preferred: bind a host variable whose type matches ACCOUNT_ID
WHERE ACCOUNT_ID = :ACCOUNT_ID_INTEGER
 
-- Parameter marker explicitly typed on the value side
WHERE ACCOUNT_ID = CAST(? AS INTEGER)
 
-- Match a DATE column with a DATE parameter
WHERE BUSINESS_DATE = CAST(? AS DATE)
 
-- Join columns should also use compatible definitions
ON O.CUSTOMER_ID = C.CUSTOMER_ID

The best correction is usually at the interface boundary: define COBOL host variables, Java setters, stored procedure parameters, and table columns from the same business domain. Casting the value side can preserve the search column, but it can raise an error for invalid text and is not a universal cure. Compare column definitions on both sides of every join, including length, encoding, precision, scale, nullability, and timestamp precision.

OR predicates: readable logic can produce broad access

OR is valid SQL, not an automatic defect. Db2 can use techniques such as list prefetch, multiple index access, or predicate transformations when cost estimates support them. Trouble appears when unrelated conditions cover a large share of the table, when one OR branch is non-indexable, or when a long generated chain obscures duplicates and selectivity. Parentheses are essential because AND has higher precedence than OR.

Consolidate only equivalent conditions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Repetitive equality predicates
WHERE STATUS = 'N'
   OR STATUS = 'H'
   OR STATUS = 'R'
 
-- Equivalent and clearer
WHERE STATUS IN ('N', 'H', 'R')
 
-- Parentheses preserve the intended business rule
WHERE REGION_ID = :REGION_ID
  AND (STATUS = 'N' OR PRIORITY_CODE = '1')
 
-- Without parentheses, PRIORITY_CODE = '1' applies to every region
WHERE REGION_ID = :REGION_ID
  AND STATUS = 'N'
   OR PRIORITY_CODE = '1';

Splitting OR into UNION ALL can let each branch use a different index, but it is safe only when the branches cannot return the same row or when duplicates are intentionally allowed. UNION removes duplicates through additional work and is not a free tuning trick. If branches overlap, add mutually exclusive conditions or preserve the original semantics deliberately. Compare both forms with representative parameter values because the best plan depends on data distribution.

NOT, not-equal, and NULL pitfalls

Negative predicates often describe most of a table. STATUS <> 'CLOSED' can qualify many different values, so an index probe might be less attractive than a scan. Rewrite a negative test as a small positive set only when the domain is controlled and the sets are truly equivalent. Remember that NULL is not equal to or different from a value; ordinary comparisons with NULL evaluate to UNKNOWN.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Dangerous when EXCLUDED_CUSTOMER.CUSTOMER_ID can contain NULL
SELECT C.CUSTOMER_ID
  FROM CUSTOMER AS C
 WHERE C.CUSTOMER_ID NOT IN
       (SELECT E.CUSTOMER_ID
          FROM EXCLUDED_CUSTOMER AS E);
 
-- Clear anti-join: return customers with no equal exclusion row
SELECT C.CUSTOMER_ID
  FROM CUSTOMER AS C
 WHERE NOT EXISTS
       (SELECT 1
          FROM EXCLUDED_CUSTOMER AS E
         WHERE E.CUSTOMER_ID = C.CUSTOMER_ID);
 
-- If NULL status should count as not closed, say so explicitly
WHERE STATUS <> 'CLOSED'
   OR STATUS IS NULL;

NOT IN is especially surprising. If its list or subquery contains NULL, a candidate that matches no known value still cannot be proven different from the unknown value. The result becomes UNKNOWN and is rejected by WHERE. Filtering NULL from the subquery can work when that is the business rule, but a correlated NOT EXISTS normally communicates the anti-join directly. Confirm that the correlation includes every key column.

Outer join predicate placement changes meaning

A LEFT JOIN preserves every qualifying left row. When no right row matches, Db2 supplies NULL for right-side columns. A right-side predicate in WHERE is evaluated after the join. If it requires R.STATUS = 'OPEN', the null-extended rows fail, and the result behaves like an inner join for that condition. Moving the predicate to ON changes which right rows are eligible to match while retaining unmatched left rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Preserve customers, matching only their open accounts
SELECT C.CUSTOMER_ID,
       A.ACCOUNT_ID
  FROM CUSTOMER AS C
  LEFT JOIN ACCOUNT AS A
    ON A.CUSTOMER_ID = C.CUSTOMER_ID
   AND A.ACCOUNT_STATUS = 'OPEN';
 
-- Different meaning: require an open account
SELECT C.CUSTOMER_ID,
       A.ACCOUNT_ID
  FROM CUSTOMER AS C
  LEFT JOIN ACCOUNT AS A
    ON A.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE A.ACCOUNT_STATUS = 'OPEN';
 
-- Find customers with no account at all
SELECT C.CUSTOMER_ID
  FROM CUSTOMER AS C
  LEFT JOIN ACCOUNT AS A
    ON A.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE A.CUSTOMER_ID IS NULL;

Predicate movement is not merely formatting. A left-table filter in ON can also have different effects from the same filter in WHERE because preserved left rows remain. State the desired result first, then place each predicate according to whether it defines a match or filters the completed result. Test matched rows, unmatched rows, multiple matches, NULL values, and inactive right rows.

LIKE and the leading wildcard problem

An index orders character keys from their beginning. LIKE 'SMI%' provides a known prefix, so Db2 may derive a useful range. LIKE '%SMI%' allows any beginning and normally cannot provide that narrow starting point. Db2 might still scan an index for screening or index-only access, but “uses an index” does not mean “selectively matches an index.”

1
2
3
4
5
6
7
8
9
10
11
-- Leading wildcard: contains search
WHERE LAST_NAME LIKE '%SMI%'
 
-- Anchored prefix: ordinary index matching may be possible
WHERE LAST_NAME LIKE 'SMI%'
 
-- Treat user wildcard characters deliberately
WHERE PRODUCT_CODE LIKE :SEARCH_PATTERN ESCAPE '\'
 
-- Do not silently change contains into prefix search:
-- the two predicates return different rows.

If contains search is a core requirement, redesign it rather than deleting the wildcard. Options include a governed normalized search column, a suitable expression-based index for a stable expression, limited domain-specific token tables, or search technology intended for text retrieval. Validate support in the installed Db2 for z/OS release. Never advertise a prefix search as contains search merely to obtain a faster plan.

Expression rewrites that preserve searchable columns

Good rewrites move calculation away from the indexed column and expose an equality, prefix, or range. Date extraction becomes a half-open interval. Simple arithmetic can move to the constant side. Repeated equality OR conditions can become IN. A nullable anti-membership test can become NOT EXISTS. These are patterns to evaluate, not blind substitutions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Month extraction to half-open date range
WHERE INVOICE_DATE >= DATE('2026-08-01')
  AND INVOICE_DATE <  DATE('2026-09-01')
 
-- Arithmetic moved from the column, if types and overflow make it equivalent
WHERE QUANTITY > 100
-- instead of: WHERE QUANTITY + 10 > 110
 
-- Adjacent ranges can sometimes be consolidated
WHERE SCORE >= 10
  AND SCORE < 30
-- instead of: (SCORE >= 10 AND SCORE < 20)
--          OR (SCORE >= 20 AND SCORE < 30)
 
-- Direct equality with a correctly typed value
WHERE EMPLOYEE_ID = :EMPLOYEE_ID

Boundary testing is mandatory. Include the first value, the next interval boundary, NULL, negative values, maximum precision, invalid input, and values near arithmetic limits. For timestamps, an exclusive next-period boundary avoids guessing the final representable fraction of a second. For strings, collation and encoding can make a hand-built upper prefix unsafe.

Indexes, join access, and EXPLAIN

A rewrite gives the optimizer better choices; it does not force a specific choice. Db2 is cost based. A table space scan can be best when most rows qualify, a table is small, clustering supports sequential access, or catalog statistics indicate that random data access would cost more. Composite index order matters too. With an index on (CUSTOMER_ID, ORDER_TS), equality on CUSTOMER_ID followed by a timestamp range can provide useful matching. A range on the first key generally limits matching on later keys.

Read the complete access path

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
EXPLAIN PLAN SET QUERYNO = 9340 FOR
SELECT O.ORDER_ID,
       C.CUSTOMER_NAME
  FROM ORDERS AS O
  JOIN CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_TS >= :FROM_TS
   AND O.ORDER_TS <  :TO_TS
   AND O.ORDER_STATUS IN ('N', 'H');
 
SELECT QUERYNO,
       PLANNO,
       METHOD,
       ACCESSTYPE,
       MATCHCOLS,
       ACCESSCREATOR,
       ACCESSNAME,
       INDEXONLY,
       PREFETCH
  FROM PLAN_TABLE
 WHERE QUERYNO = 9340
 ORDER BY PLANNO;

• PLANNO and METHOD help describe join sequence and join method. Read all PLAN_TABLE rows for the query, not one row in isolation.
• ACCESSTYPE and ACCESSNAME identify the broad access method and selected index where applicable.
• MATCHCOLS reports how many index key columns participate in matching. Zero matching columns can still accompany an index scan.
• INDEXONLY and PREFETCH provide additional evidence about data access, but neither alone proves that a plan is good.
• Predicate explain details can show matching, screening, stage, sequence, and estimated filtering. Availability depends on the EXPLAIN process and tables used at the site.

Compare original and rewritten SQL with current RUNSTATS, equivalent bind options, representative parameter values, and the same Db2 function level. Inspect estimated cardinality as well as access type; a wrong row estimate can lead to a poor join order. Then measure elapsed time, CPU, getpages, synchronous reads, rows examined, returned rows, and execution frequency. A faster single test with different data is not proof.

A safe review checklist

• Write the intended result grain and optional relationships in plain language.
• Verify that every table has a complete join path using compatible data types.
• Look for row multiplication before adding DISTINCT or aggregation.
• Keep searchable columns free of unnecessary functions, arithmetic, and casts.
• Check OR grouping, overlap, selectivity, and possible UNION duplicate behavior.
• Review NOT IN, not-equal, and every nullable operand using three-valued logic.
• Place outer join predicates according to matching versus final filtering semantics.
• Distinguish anchored prefix search from leading-wildcard contains search.
• Prove semantic equivalence with boundary and NULL tests.
• Use EXPLAIN and runtime evidence before and after deployment.

Explain It Like I'm Five

Imagine two boxes of cards: one box has children and the other has their library books. The child number tells you which book belongs to which child. If you forget that rule, you pair every child with every book and make a giant, incorrect pile. Predicates are instructions for finding the right cards. “Starts with SAM” lets you open a sorted box near SAM, but “has AM anywhere” may require checking almost every card. A LEFT JOIN says to keep children who have no book, so throwing away every blank book card afterward breaks that promise. EXPLAIN is Db2 showing how it plans to search and pair the cards.

Exercises

• Correct a query that joins ORDERS and CUSTOMER without an ON condition. Predict the maximum intermediate row count when 800 orders and 200 customers qualify.
• Rewrite DATE(EVENT_TS) = DATE('2026-08-15') as a half-open timestamp range and test values exactly at both boundaries.
• Compare NOT IN and NOT EXISTS using an exclusion table containing 7, 9, and NULL. Explain the result for candidate value 12.
• Write two LEFT JOIN queries: one that preserves customers without active accounts and one that returns only customers with active accounts.
• Review a generated OR chain. Decide whether IN, consolidated ranges, or UNION ALL is equivalent, and document possible duplicate rows.
• EXPLAIN a leading-wildcard LIKE and an anchored-prefix LIKE. Compare ACCESSTYPE, ACCESSNAME, MATCHCOLS, estimated rows, and measured getpages.