Optimizing distributed requests with server options

In a federated system, use parameters called server options to supply the global catalog with information that applies to a data source as a whole or to control how the federated database interacts with a data source.

About this task

About this task

Server options describe the capabilities of a particular data source and enhance the knowledge that the federated server has about that data source. For example, you can use these server options:
  • The VARCHAR_NO_TRAILING_BLANKS server option informs the optimizer that every VARCHAR column residing on the data source server is free of trailing blanks. Use this option only when you are certain that all VARCHAR2 columns for every object that is referenced by a nickname on the server has no trailing blanks. Otherwise, use a column option to specify the columns for individual objects on the server that have no trailing blanks. The column option is also named VARCHAR_NO_TRAILING_BLANKS.
  • The PLAN_HINTS server option provides Sybase data sources with statement fragments, called plan hints. Plan hints help the data source optimizer decide which index to use in accessing a table and which table join sequence to use in retrieving data for a result set.

Typically, the database administrator sets server options for a federated system. However, a programmer can make good use of the server options that help optimize queries. For example, for data sources SYB1 and SYB2, the PLAN_HINTS server option is set to the default, N (no, do not furnish this data source with plan hints). You write a distributed request that selects data from SYB1 and SYB2. You expect that the optimizers at these data sources can use the plan hints to improve their strategies for accessing this data. You can override the default with a setting of Y (yes, furnish the plan hints) while your application is connected to the federated database. When the connection to the data sources terminates, the setting automatically reverts to N.

Procedure

To set server options:

Procedure

Use the SET SERVER OPTION statement to set or change server options. To ensure that the setting takes effect, specify the SET SERVER OPTION statement immediately following the CONNECT statement. The server option is set for the duration of the connection to the federated database.

What to do next

Consider preparing the statement dynamically. The SET SERVER OPTION statement affects only dynamic SQL statements.

For static SQL, using the SET SERVER OPTION statement affects only the execution of the static SQL statement. Using the SET SERVER OPTION statement has no affect on the plans that the optimizer generates.