How Db2 modifies IN predicates

Db2 automatically modifies some queries that contain IN predicates to enable more access path options.

Begin program-specific programming interface information.

Db2 converts an IN predicate that has only one item in the IN to an equality predicate. A set of simple, Boolean term, equal predicates on the same column that are connected by OR predicates can be converted into an IN predicate. For example, Db2 mgiht convert the following boolean predicates.

C1=5 or C1=10 or C1=15

The following single IN predicate is the result.

C1 IN (5,10,15)
Db2 also generates additional predicates through transitive closure for queries that contain IN predicates, under the following conditions:
  • The INLISTP subsystem parameter is set to a positive value.
  • The number of elements in the list on the right side of the IN keyword is not greater than the value of the INLISTP subsystem parameter.
  • No correlated column references exist in expressions on the left side of the IN keyword.
  • No columns defined by field procedures are referenced on the left side of the IN keyword
  • The values in the list on the right side of the IN keyword must be simple constants. For example, the list must not include expressions involving constants, parameter markers, host variables, or subselects.
End program-specific programming interface information.