Using host variables efficiently

When host variables or parameter markers are used in a query, the actual values are not known when you bind the package or plan that contains the query. Db2 uses a default filter factor to determine the best access path for an SQL statement. If that access path proves to be inefficient, you can do several things to obtain a better access path.

About this task

Begin program-specific programming interface information.Host variables require default filter factors. When you bind a static SQL statement that contains host variables, Db2 uses a default filter factor to determine the best access path for the SQL statement. Db2 often chooses an access path that performs well for a query with several host variables. However, in a new release or after maintenance has been applied, Db2 might choose a new access path that does not perform as well as the old access path. In many cases, the change in access paths is due to the default filter factors, which might lead Db2 to optimize the query in a different way.

Procedure

To change the access path for a query that contains host variables, use one of the following actions:

  • Create selectivity overrides for the SQL statement.
    The selectivity overrides form one or more weighted selectivity instances, which combine to describe the selectivity profile of the statement. Db2 can use the selectivity profile to choose a more efficient access path for the statement.
  • Bind the package or plan that contains the query with the options REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE).
  • Rewrite the query.
    End program-specific programming interface information.