IBM database options

To configure how the federated server and its users interact with an IBM® data source, set and modify wrapper, server, user mapping, nickname, and column options.

Wrapper options

The following tables list the options that apply to IBM data sources and identify the required options that you must specify.

Table 1. Wrapper options for IBM data sources
Name	Description
DB2_FENCED	Required. Specifies whether the wrapper runs in fenced mode or in trusted mode. Valid values are Y and N. The default is N; the wrapper runs in trusted mode.
DB2_UM_PLUGIN	Specifies the implementation of the user mapping plug-in. For a plug-in written in Java™, specifies a case-sensitive string for the class name that corresponds to the user mapping repository class. For example, `"UserMappingRepositoryLDAP"`. For a plug-in written in C, specifies any valid C library name.
DB2_UM_PLUGIN_LANG	Specifies the language of the user mapping plug-in. Valid values are Java and C. The default is Java.

Server options

Table 2. Server options for Db2 data sources
Name	Description
APP_ISOLATION_ENABLE	Specifies the isolation level for a client connection. Valid values are Y and N. The default is Y; the federated server does not set the isolation level for a connection to the client. In this case, the client can use the isolation level set in configuration file, for example db2cli.ini. Y specifies that the federated server obtains the isolation level from the application and sets the application isolation as a connection attribute.
COLLATING_SEQUENCE	Specifies whether the data source uses the same default collating sequence as the federated database. Valid values are Y, N, and I. I specifies it is not case-sensitive. The default is Y. The collating sequence specified for the federated server must match the collating sequence on the remote data source.
COMM_RATE	Specifies the communication rate, in megabytes per second, between the federated server and the data source server. Valid values are whole numbers the are greater than 0 and less than 2147483648. The default is 2.
CPU_RATIO	Specifies the relative speeds of the federated server and data source server CPUs. For example, a setting of: 1 indicates that the CPUs of the federated server and the data source server run at the same speed 0.5 indicates that the federated CPU runs half as fast as the data source CPU 2 indicates that the federated CPU runs twice as fast as the data source CPU Valid values are greater than 0 and less than 10²³. The default is 1. Values can be expressed in any valid double notation, for example, 123E10, 123, or 1.21E4.
DATE_COMPAT	Specifies whether the date_compat parameter is applied to the database. Valid values are Y and N. The default is N. This server option is valid only for Db2® Database, 9.7 or later. In Federation component, when you issue a CREATE SERVER statement, this server option is automatically configured based on the configuration of your data source. If you attempt to manually configure this server option, you receive the SQL1841N message.
DBNAME	Required. Specifies the database to use for the initial remote database connection. This is the database alias for the remote database that is cataloged on the federated server by using the CATALOG DATABASE command or the Configuration Assistant.
DB2_MAXIMAL_PUSHDOWN	Specifies the primary criteria that the query optimizer uses to choose an access plan. Valid values are Y and N. The default is N; the query optimizer chooses the plan that has the lowest estimated cost. Y specifies that the query optimizer choose the access plan that pushes down the most query operations to the data source.
DB2_MAX_ASYNC_REQUESTS_ PER_QUERY	Specifies the maximum number of concurrent asynchronous requests from a query. Valid values are from -1 to 64000. The default is 1. -1 specifies that the federated query optimizer determines the number of requests. 0 specifies that the data source cannot accommodate additional asynchronous requests.
DB2_TWO_PHASE_COMMIT	Specifies whether the federated server connects to the data source in two-phase commit protocol or one-phase commit protocol. Valid values are Y and N. The default is N; the federated server uses the one-phase commit protocol to connect. Y specifies that the federated server uses two-phase commit protocol to connect.
DB2_UM_PLUGIN	Specifies the implementation of the user mapping plug-in. For a plug-in written in Java, specifies a case-sensitive string for the class name that corresponds to the user mapping repository class. For example, `"UserMappingRepositoryLDAP"`. For a plug-in written in C, specifies any valid C library name.
DB2_UM_PLUGIN_LANG	Specifies the language of the user mapping plug-in. Valid values are Java and C. The default is Java.
FED_PROXY_USER	Specifies the authorization ID to use to establish all outbound trusted connections when the inbound connection is non-trusted. The user whose ID is specified in this option must have a user mapping that specifies both the REMOTE_AUTHID and REMOTE_PASSWORD options. Restriction: This server option is valid only for Db2 Database 9.5 and later and Db2 for z/OS® 9 and later.
FOLD_ID	Specifies the case for the user ID that is sent to the data source. There is no default value. The federated server sends the user ID in uppercase; then if the uppercase user ID fails, the server sends the user ID in lowercase. Valid values are U (uppercase), L (lowercase), and N (null). Avoid using the null setting, which might result in poor performance.
FOLD_PW	Specifies the case for the password that is sent to the data source. There is no default value; the federated server sends the password in uppercase; then if the uppercase password fails, the server sends the password in lowercase. Valid values are U (uppercase), L (lowercase), and N (null). Avoid using the null setting, which might result in poor performance.
IO_RATIO	Specifies how much faster or slower the data source I/O system runs when compared to the federated server I/O system. Valid values are greater than 0 and less than 10²³. The default is 1.0. Values can be expressed in any valid double notation, for example, 123E10, 123, or 1.21E4. A setting of 1 indicates that the federated server and the data source server have the same I/O speed; a 1:1 ratio. A setting of 0.5 indicates that the federated server speed is 50% slower than the data source speed. A setting of 2 indicates that the federated speed is twice as fast as the data source speed.
NO_EMPTY_STRING	Specifies whether the remote data source server can contain empty strings. Valid values are Y and N. The default value varies depending on your remote data source. For remote Oracle data sources, the default is Y; all empty string values are converted to NULL values. For all other remote data sources, the default is N; the data source can contain empty strings. You can improve your systems performance by setting this option to Y in system configurations where the federated server is in VARCHAR2 compatible mode but the remote data source is not VARCHAR2 compatible.
NUMBER_COMPAT	Specifies whether the data source server supports the NUMBER data type. Valid values are Y and N. The default is N; the data source server does not support the NUMBER data type. In systems where the federated server does not support the NUMBER data type but the data source server does, you must set the NUMBER_COMPAT option to Y because the data source server can return DECFLOAT results that are outside of the range of the DECIMAL data type and cause the SQLSTATE 560BD error. Restriction: This server option is valid only for Db2 Database 9.7 and later. In Federation component, when you issue a CREATE SERVER statement, this server option is automatically configured based on the configuration of your data source. If you attempt to manually configure this server option, you receive the SQL1841N message.
OLD_NAME_GEN	Specifies how to convert the column names and index names that are in the data source into nickname column names and local index names for the federated server. Valid values are Y and N. The default is N; the generated names closely match the names in the data source. Y specifies that the generated names are the same as the names that were created in IBM WebSphere® Federation Server 9 and earlier. Thus, the names might not closely match the data source names.
PASSWORD	Specifies whether the password should be validated at the remote data source. Valid values are Y and N. Y specifies that passwords are sent to the remote server. N specifies that passwords are not sent to the remote server. This server option is optional for all relational servers.
PUSHDOWN	Specifies whether the federated server allows the data source to evaluate operations. Valid values are Y and N. The default is Y; the data source evaluates operations. N specifies that the federated server send SQL statements that include only SELECT with column names. Predicates, such as WHERE=; column and scalar functions, such as MAX and MIN; sorts, such as ORDER BY OR GROUP BY; and joins are not included in any SQL that the federated server sends to the data source.
SAME_DECFLT_ROUNDING	Specifies whether the rounding mode of both the federated server and data source server are using the same DECFLOAT rounding mode settings. Valid values are Y and N. The default is N; the federated server and remote server have different DECFLOAT rounding mode settings. Important: If you set this option to Y when the rounding modes are different between the federated server and data source server, you might receive incorrect DECFLOAT rounding results. To configure an existing federated server and data source server that use the same DECFLOAT rounding mode setting, use the ALTER SERVER statement. Restriction: This server option is valid only for Db2 Database 9.5 and later. In Federation component 9.7 Fix Pack 2 and later, when you issue a CREATE SERVER statement, this server option is automatically configured based on the configuration of your data source. If you attempt to manually configure this server option, you receive the SQL1841N message.
VARCHAR2_COMPAT	Specifies whether the remote data source is VARCHAR2 compatible. The valid values Y and N. The default value varies depending on the remote data source. For remote Oracle data sources, the default is Y; the data source is VARCHAR2 compatible. For all other remote data sources, the default is N; the data source is not set to VARCHAR2 compatible mode. You must set this server option to Y if your Db2 Database, ODBC, or JDBC data source is configured in VARCHAR2 compatible mode. In Federation component , when you issue a CREATE SERVER statement, this server option is automatically configured based on the configuration of your data source. If you attempt to manually configure this server option, you receive the SQL1841N message.
SSL_KEYSTORE	Optional. Specifies the SSL client keystore database file. If you set this option, the federation server uses SSL to establish a connection with the remote data source. The value of this option is a fully-qualified file name. Important: If you set this option, then the SSL_KETSTASH option must also be set. The keystore database file is generated by IBM GSKit tool.
SSL_KEYSTASH	Optional. Specifies the SSL client keystore database password file, which is also known as the stash file. If you set this option, the federation server uses SSL to establish a connection with the remote data source. The value of this option is a fully-qualified file name. Important: If you set this option, then the SSL_KETSTORE option must also be set. The keystore database file is generated by IBM GSKit tool.
SSL_SERVERCERTIFICATE	Optional. Specifies the server signer certificate. If you set this option, the federation server uses SSL to establish a connection with the remote data source. The value of this option is a fully-qualified file name. The certificate file is usually received from the remote data source. You can use this option either alone or in combination with the SSL_KEYSTORE and SSL_KEYSTASH options. When you use this option together with SSL_KEYSTORE and SSL_KEYSTASH, the federation server creates a default keystore database internally and adds a certificate with the name SSL_SERVERCERTIFICATE to the default keystore database.

User mapping options

Table 3. User mapping options for Db2 data sources
Option	Description
FED_PROXY_USER	Specifies the authorization ID to use to establish all outbound trusted connections when the inbound connection is non-trusted. The user whose ID is specified in this option must have a user mapping that specifies both a REMOTE_AUTHID and a REMOTE_PASSWORD. If you specify the FED_PROXY_USER user mapping option, you must also specify the FED_PROXY_USER server option. Restriction: This server option is valid only for Db2 Database 9.5 and later and Db2 for z/OS 9 and later.
ACCOUNTING_STRING	Required if accounting information must be passed. Specifies a DRDA accounting string. Valid values include any string that has 255 characters or fewer.
REMOTE_AUTHID	Specifies the remote user ID to which the local user ID is mapped. If you do not specify this option, the ID that is used to connect to the federated database is used.
REMOTE_PASSWORD	Specifies the remote password for the remote user ID. If you do not specify this option, the password that is used to connect to the federated database is used.
USE_TRUSTED_CONTEXT	Specifies whether the user mapping is trusted. Valid values are Y and N. The default is N; the user mapping is not trusted and can be used only in non-trusted federated outbound connections. Y specifies that the user mapping is trusted and can be used in both trusted and non-trusted outbound federated connections. Restriction: This server option is valid only for Db2 Database 9.5 and later and Db2 for z/OS 9 and later.