Enabling asynchrony optimization

To enable asynchrony optimization, you specify the number of asynchrony TQ operators for a given query and set a server option for the data source.

About this task

Restrictions

Asynchrony optimization requires:
  • A federated system with the database partitioning feature (DPF) that includes more than one logical database partition.
  • Access to data sources through fenced wrappers.
This optimization does not support these objects:
  • Nicknames on a data source that are accessed through a trusted wrapper.
  • Queries with insert, update, or delete operations.

Procedure

To enable asynchronous processing:

Procedure

  1. Set one or more of the following parameters:
    • Set the FEDERATED_ASYNC database manager configuration parameter to a value between 0 and 32 767 inclusive, or to ANY. MAXAGENTS is a database configuration parameter that specifies the maximum number of ATQs allowed per query. The value ANY allows the optimizer to determine the number of ATQs for a given access plan. The default is 0.
    • Optionally, set the FEDERATED_ASYNCHRONY bind and precompile options for the static statements in the package to override the configuration parameter setting for the query. The default is 0.
    • Optionally, set the CURRENT FEDERATED ASYNCHRONY special register to dynamically override the database manager configuration parameter setting and the bind option for the query.
    These parameters form the following hierarchy:
    1. Special register
    2. Bind option
    3. Database manager configuration parameter
    The special register value, if specified, takes precedence over the bind option, which in turn takes precedence over the database manager configuration parameter.
  2. Set the DB2_MAX_ASYNC_REQUESTS_PER_QUERY server option to a numeric value. This server option specifies the maximum number of asynchronous requests that the server allows for the query.
    The range for the server option is -1 to 64000.
    • The default is 1. Therefore, one SHIP operator or one RPD operator that belongs to a server is considered for asynchrony in a query.
    • The default for the ODBC data source only is 0. Asynchrony requires multiple cursors per connection. This value should be 0 for the ODBC wrapper unless the data source supports multiple cursors per connection.