Federated server options

The server options that you set refine the knowledge that the federated server has about the remote data source.

The previously listed factors that affect pushdown opportunities are characteristics of the database servers, and you can not change them. By carefully considering the following server options, it is possible to improve query performance:
  • COLLATING_SEQUENCE. If a data source has a collating sequence that differs from the federated database collating sequence, any order-dependent operations on character values cannot be remotely evaluated at the data source. An example is executing MAX column functions on a nickname character column at a data source with a different collating sequence. Because results might differ if the MAX function is evaluated at the remote data source, the federated database will perform the aggregate operation and the MAX function locally.
  • VARCHAR_NO_TRAILING_BLANKS. This option is for varying-length character strings that contain no trailing blanks. Some data sources, such as Oracle, do not apply blank-padded comparison semantics like the federated database does. This padding difference can cause unexpected results.
    For example:
    'HELLO' = 'HELLO         ' in Db2 
'HELLO' <> 'HELLO         ' in Oracle!
    If trailing blanks are present on VARCHAR columns on an Oracle data source, you should set this option to N (the default for Oracle). This option impacts performance, because the federated server must compensate for the difference in semantics, but guarantees a consistent result set. Setting this value to Y when an Oracle data source column contains trailing blanks can cause inconsistent results.

    If you are certain that all VARCHAR and VARCHAR2 columns at a data source contain no trailing blanks, consider setting this server option for a data source. Ensure that you consider all objects that can potentially have nicknames, including views.

    Recommendation: Set this option on a column by column basis using the VARCHAR_NO_TRAILING_BLANKS column option.

  • DB2_MAXIMAL_PUSHDOWN. This option specifies the primary criteria that the query optimizer uses when choosing an access plan. The query optimizer can choose access plans based on cost or based on the user requirement that as much query processing as possible be performed by the remote data sources. With DB2_MAXIMAL_PUSHDOWN set to Y, reducing network traffic becomes the overriding criteria for the query optimizer. The query optimizer uses the access plan that performs the fewest number of "sends" to the data sources. Setting this server option to Y forces the federated server to use an access plan that might not be the lowest cost plan. Using an access plan other than the lowest cost plan can decrease performance. When the DB2_MAXIMAL_PUSHDOWN server option is set to Y a query that results in a Cartesian product is not pushed down the remote data sources. Queries that will result in a Cartesian product will be processed by the federated database. The DB2_MAXIMAL_PUSHDOWN server option does not need to be set to Y for the federated server to pushdown query processing to the remote data sources. When this server option is set to N (the default), the query optimizer will pushdown query processing to the data sources. However, the primary criteria the optimizer uses when the option is set to N is cost instead of network traffic.

Server characteristics affecting global optimization describes the COMM_RATE, CPU_RATIO, and IO_RATIO server options that also can affect query performance.