Optimizing queries with IN predicates

You can control how Db2 optimizes IN predicates.

Procedure

Begin program-specific programming interface information.To control how Db2 optimizes IN predicates:

Set the value of the INLISTP subsystem parameter to a non-zero value.
When you set the INLISTP parameter to n a number in the range 1–5000, Db2 optimizes for an IN predicate with up to n values. If you set the INLISTP value to 0, the optimization is disabled. The default INLISTP setting is 1000.

Results

When you enable the INLISTP subsystem parameter, you enable the following optimizations for some queries that contain IN predicates:

  • Pushing the IN predicate down from the parent query block into the materialized table expression.
  • Moving a correlated IN predicate in a subquery that is generated by transitive closure up to the parent query block.