Microsoft SQL Server options
To configure how the federated server and its users interact with a data source, set and modify wrapper, server, user mapping, and column options.
Wrapper options
The following tables list the options that apply to this data source and identify the required options that you must specify.
| 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
| Name | Description |
|---|---|
| CODEPAGE | Specifies the code page that corresponds to the coded character set of the data source client configuration. On UNIX and Microsoft Windows systems that use a non-Unicode federated database, the default is the code page of the federated database. On UNIX systems that use a Unicode federated database, the default is 1208. On Windows systems that use a Unicode federated database, the default is 1202. |
| 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 a case-insensitive. 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 how much faster or slower the data source CPU is when compared to federated server CPU. Valid values are greater than 0 and less than 1x1023. 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 CPU speed; a 1:1 ratio. A setting of 0.5 indicates that the federated server CPU speed is 50% slower than the data source CPU. A setting of 2 indicates that the federated CPU is twice as fast as the data source CPU. |
| CPU_RATIO | Specifies how much faster or slower the CPU of the data source runs when compared to the speed of the federated server's CPU. Valid values are greater than 0 and less than 1x1023. The default is 1.0. Values can be expressed in any valid double notation, for example, 123E10, 123, or 1.21E4. |
| DBNAME | Required. Specifies the alias for the database that you want to access. The value is case-sensitive. |
| 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. If more than one access plan meets this criteria, plan that has the lowest cost is chosen. |
| 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. Important: If you set this option to Y, you must also specify
the XA_OPEN_STRING_OPTION.
|
| 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. |
| 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 1x1023. 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. |
| NODE | Required. If the federated server uses Microsoft Windows, the value for NODE is the system DSN name that you specify for the Microsoft SQL Server. If the federated server uses UNIX or Linux®, the value for NODE is defined in the odbc.ini file. The value is case-sensitive. |
| 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 Version 9 and earlier. Thus, the names might not closely match the data source names. |
| PASSWORD | Specifies whether or not passwords are sent to a data source. The default is Y. |
| 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. |
| XA_OPEN_STRING_OPTIONS | Required when DB2_TWO_PHASE_COMMIT is set to Y. Specifies the resource manager ID of the Microsoft SQL Server Registry. |
User mapping options
| Name | Description |
|---|---|
| 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. |