Administration Guide

Server Options Affecting Federated Database Queries

A federated system is composed of a DB2 DBMS (the federated database) and one or more data sources. Data sources are identified to the federated database when you issue CREATE SERVER statements. When you issue these statements, you can also provide server options that refine and control aspects of federated system operations involving DB2 and the specified data source. Server options can be changed later using ALTER SERVER statements. Refer to the SQL Reference for more information about the CREATE SERVER and ALTER SERVER statements.
Note: You must install the distributed join installation option and set the database manager parameter FEDERATED to YES before you can create servers and specify server options.

Server options and their values facilitate query pushdown analysis, global optimization and other aspects of federated database operations. For example: in the CREATE SERVER statement, you can specify certain performance statistics as server option values. That is, you can set the cpu_ratio option to a value that indicates the relative speeds of the data source's and federated server's CPUs. And you can set the io_ratio option to a value that indicates the relative rates of the data source's and federated server's I/O devices. When you run CREATE SERVER, this data is added to the catalog view SYSCAT.SERVEROPTIONS, and the optimizer uses it in developing its access plan for the data source. If a statistic changes (as might happen, for instance, if the data source CPU is upgraded), you can use the ALTER SERVER statement to update SYSCAT.SERVEROPTIONS with this change. The optimizer then uses your update in developing its next access plan for the data source.

Table 45. Server Options and Their Settings

Option Valid Settings Default Setting
collating_sequence Specifies whether the data source uses the same default collating sequence as the federated database, based on the code set and the country information. If a data source has a collating sequence that differs from DB2's collating sequence, most operations depending on DB2's collating sequence cannot be remotely evaluated at a data source. An example is executing MAX column functions against 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, DB2 will perform the aggregate operation and the MAX function locally.
If your query contains an equal sign, it is possible to push-down that portion of the query even if the collating sequences are different (set to 'N'). For example, the predicate C1 = 'A' could be pushed-down to a data source. Of course, such queries cannot be pushed-down when the collating sequence at the data source is case-insensitive. When a data source is case-insensitive, the results from C1= 'A' and C1 = 'a' are the same, which is not acceptable in a case-sensitive environment (DB2).
Administrators can create federated databases with a particular collating sequence that matches the data source collating sequence. This approach may speed performance if all data sources use the same collating sequence or if most or all column functions are directed against data sources that use the same collating sequence.

'Y'
Data source's collating sequence is the same as federated database's.
'N'
Data source's collating sequence is not the same as federated database's.
'I'
Data source's collating sequence is different from federated database's and is case-insensitive (for example, 'TOLLESON' and 'TolLESon' are considered equal).

'N'
comm_rate Specifies the communication rate between a federated server and its associated data sources. Expressed in megabytes per second.
'2.0'
connectstring Specifies initialization properties needed to connect to an OLE DB provider. For the complete syntax and semantics of the connection string, see the "Data Link API of the OLE DB Core Components" in the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.
None
cpu_ratio Indicates how much faster or slower a data source's CPU runs than the federated server's CPU.
'1.0'
dbname Name of the data source database that you want the federated server to access. Required for DB2 family data sources; does not apply to Oracle** data sources.
None.
fold_id (See notes 1 and 4 at the end of this table.) Applies to user IDs that the federated server sends to data sources for authentication. Valid values are:

'U'
The federated server folds the user ID to uppercase before sending it to the data source. This is a logical choice for DB2 Family and Oracle** data sources (See note 2 at end of this table.)
'N'
The federated server does nothing to the user ID before sending it to the data source. (See note 2 at end of this table.)
'L'
The federated server folds the user ID to lowercase before sending it to the data source.

If none of these settings are used, the federated server tries to send the user ID to the data source in uppercase. If the user ID fails, the server tries sending it in lowercase.
None.
fold_pw (See notes 1, 3 and 4 at the end of this table.) Applies to passwords that the federated server sends to data sources for authentication. Valid values are:

'U'
The federated server folds the password to uppercase before sending it to the data source. This is a logical choice for DB2 Family and Oracle** data sources.
'N'
The federated server does nothing to the password before sending it to the data source.
'L'
The federated server folds the password to lowercase before sending it to the data source.

If none of these settings are used, the federated server tries to send the password to the data source in uppercase. If the password fails, the server tries sending it in lowercase.
None.
io_ratio Denotes how much faster or slower a data source's I/O system runs than the federated server's I/O system.
'1.0'
node Name by which a data source is defined as an instance to its RDBMS. Required for all data sources.
For a DB2 family data source, this name is the node specified in the federated database's DB2 node directory. To view this directory, issue the db2 list node directory command.
For an Oracle** data source, this name is the server name specified in the Oracle** tnsnames.ora file. To access this name on the Windows NT platform, specify the View Configuration Information option of the Oracle** SQL Net Easy Configuration tool.
None.
password Specifies whether passwords are sent to a data source.

'Y'
Passwords are always sent to the data source and validated. This is the default value.
'N'
Passwords are not sent to the data source (regardless of any user mappings) and not validated.
'ENCRYPTION'
Passwords are always sent to the data source in encrypted form and validated. Valid only for DB2 Family data sources that support encrypted passwords.

'Y'
plan_hints Specifies whether plan hints are to be enabled. Plan hints are statement fragments that provide extra information for data source optimizers. This information can, for certain query types, improve query performance. The plan hints can help the data source optimizer decide whether to use an index, which index to use, or which table join sequence to use.

'Y'
Plan hints are to be enabled at the data source if the data source supports plan hints.
'N'
Plan hints are not to be enabled at the data source.

'N'
pushdown

'Y'
DB2 will consider letting the data source evaluate operations.
'N'
DB2 will retrieve only columns from the remote data source and will not let the data source evaluate other operations, such as joins.

'Y'
varchar_no_trailing_blanks Specifies if this data source uses non-blank padded varchar comparison semantics. For varying-length character strings that contain no trailing blanks, some DBMS' s non-blank-padded comparison semantics return the same results as DB2's comparison semantics. If you are certain that all VARCHAR table/view columns at a data source contain no trailing blanks, consider setting this server option to 'Y' for a data source. This option is often used with Oracle** data sources. Ensure that you consider all objects that can potentially have nicknames (including views).

'Y'
This data source has non-blank-padded comparison semantics similar to DB2's.
'N'
This data source does not have the same non-blank-padded comparison semantics as DB2's.

'N'

Option	Valid Settings	Default Setting
collating_sequence	Specifies whether the data source uses the same default collating sequence as the federated database, based on the code set and the country information. If a data source has a collating sequence that differs from DB2's collating sequence, most operations depending on DB2's collating sequence cannot be remotely evaluated at a data source. An example is executing MAX column functions against 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, DB2 will perform the aggregate operation and the MAX function locally. If your query contains an equal sign, it is possible to push-down that portion of the query even if the collating sequences are different (set to 'N'). For example, the predicate C1 = 'A' could be pushed-down to a data source. Of course, such queries cannot be pushed-down when the collating sequence at the data source is case-insensitive. When a data source is case-insensitive, the results from C1= 'A' and C1 = 'a' are the same, which is not acceptable in a case-sensitive environment (DB2). Administrators can create federated databases with a particular collating sequence that matches the data source collating sequence. This approach may speed performance if all data sources use the same collating sequence or if most or all column functions are directed against data sources that use the same collating sequence. 'Y' Data source's collating sequence is the same as federated database's. 'N' Data source's collating sequence is not the same as federated database's. 'I' Data source's collating sequence is different from federated database's and is case-insensitive (for example, 'TOLLESON' and 'TolLESon' are considered equal).	'N'
comm_rate	Specifies the communication rate between a federated server and its associated data sources. Expressed in megabytes per second.	'2.0'
connectstring	Specifies initialization properties needed to connect to an OLE DB provider. For the complete syntax and semantics of the connection string, see the "Data Link API of the OLE DB Core Components" in the Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.	None
cpu_ratio	Indicates how much faster or slower a data source's CPU runs than the federated server's CPU.	'1.0'
dbname	Name of the data source database that you want the federated server to access. Required for DB2 family data sources; does not apply to Oracle** data sources.	None.
fold_id (See notes 1 and 4 at the end of this table.)	Applies to user IDs that the federated server sends to data sources for authentication. Valid values are: 'U' The federated server folds the user ID to uppercase before sending it to the data source. This is a logical choice for DB2 Family and Oracle data sources (See note 2 at end of this table.) 'N' The federated server does nothing to the user ID before sending it to the data source. (See note 2 at end of this table.) 'L'** The federated server folds the user ID to lowercase before sending it to the data source. If none of these settings are used, the federated server tries to send the user ID to the data source in uppercase. If the user ID fails, the server tries sending it in lowercase.	None.
fold_pw (See notes 1, 3 and 4 at the end of this table.)	Applies to passwords that the federated server sends to data sources for authentication. Valid values are: 'U' The federated server folds the password to uppercase before sending it to the data source. This is a logical choice for DB2 Family and Oracle data sources. 'N' The federated server does nothing to the password before sending it to the data source. 'L'** The federated server folds the password to lowercase before sending it to the data source. If none of these settings are used, the federated server tries to send the password to the data source in uppercase. If the password fails, the server tries sending it in lowercase.	None.
io_ratio	Denotes how much faster or slower a data source's I/O system runs than the federated server's I/O system.	'1.0'
node	Name by which a data source is defined as an instance to its RDBMS. Required for all data sources. For a DB2 family data source, this name is the node specified in the federated database's DB2 node directory. To view this directory, issue the db2 list node directory command. For an Oracle data source, this name is the server name specified in the Oracle tnsnames.ora file. To access this name on the Windows NT platform, specify the View Configuration Information option of the Oracle** SQL Net Easy Configuration tool.	None.
password	Specifies whether passwords are sent to a data source. 'Y' Passwords are always sent to the data source and validated. This is the default value. 'N' Passwords are not sent to the data source (regardless of any user mappings) and not validated. 'ENCRYPTION' Passwords are always sent to the data source in encrypted form and validated. Valid only for DB2 Family data sources that support encrypted passwords.	'Y'
plan_hints	Specifies whether plan hints are to be enabled. Plan hints are statement fragments that provide extra information for data source optimizers. This information can, for certain query types, improve query performance. The plan hints can help the data source optimizer decide whether to use an index, which index to use, or which table join sequence to use. 'Y' Plan hints are to be enabled at the data source if the data source supports plan hints. 'N' Plan hints are not to be enabled at the data source.	'N'
pushdown	'Y' DB2 will consider letting the data source evaluate operations. 'N' DB2 will retrieve only columns from the remote data source and will not let the data source evaluate other operations, such as joins.	'Y'
varchar_no_trailing_blanks	Specifies if this data source uses non-blank padded varchar comparison semantics. For varying-length character strings that contain no trailing blanks, some DBMS' s non-blank-padded comparison semantics return the same results as DB2's comparison semantics. If you are certain that all VARCHAR table/view columns at a data source contain no trailing blanks, consider setting this server option to 'Y' for a data source. This option is often used with Oracle data sources. Ensure that you consider all objects that can potentially have nicknames (including views). 'Y' This data source has non-blank-padded comparison semantics similar to DB2's. 'N'** This data source does not have the same non-blank-padded comparison semantics as DB2's.	'N'

Notes on Table 45:

This field is applied regardless of the value specified for authentication.
Because DB2 stores user IDs in uppercase, the values 'N' and 'U' are logically equivalent to each other.
The setting for fold_pw has no effect when the setting for password is 'N'. Because no password is sent, case cannot be a factor.
Avoid null settings for either of these options. A null setting may seem attractive because DB2 will make multiple attempts to resolve user IDs and passwords; however, performance might suffer (it is possible that DB2 will send a user ID and password four times before successfully passing data source authentication).

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]