JDBC 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.

Table 1. Wrapper options for JDBC
Name Description
DB2_FENCED Required. Specifies whether the wrapper runs in fenced mode or in trusted mode. The only valid value is Y because the Db2® server only supports loading the JVM in fenced mode. The default is Y; the wrapper runs in fenced 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 JDBC
Name Description
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 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 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.
DATEFORMAT Specifies the date format that the data source uses. Use 'DD', 'MM', and 'YY' or 'YYYY' to specify the date format. You can specify a delimiter such as a space, a hyphen, or a comma. For example, the format 'YYYY-MM-DD' specifies a date such as 1958-10-01. The value can contain null values.
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 0. -1 specifies that the federated query optimizer determines the number of requests. 0 specifies that the data source cannot accommodate additional asynchronous requests.
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.
DRIVER_CLASS Specifies the JDBC driver library. You can register the server against multiple JDBC data sources if the JDBC driver conforms to the JDBC specification version 3.0 and later. See your JDBC driver documentation for the JDBC specifications and information on how to set the DRIVER_CLASS server option.
Example
In this example, the com.ibm.db2.jcc.DB2Driver JDBC driver library is specified:
Important: If you specify this option, you must also specify the URL server option.
DRIVER_PACKAGE Specifies the JDBC driver packages. Use a path separator to specify multiple driver class packages. Use a semicolon in the Windows operating systems and a colon in Linux and Unix operating systems.
Example
In this example, you specify multiple driver packages with a colon on Linux operating systems:
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.
JDBC_LOG Specifies whether the JDBC wrapper creates log files for error tracing. Valid values are Y and N. The default is N; log file are not created. If this server option is set to Y, the JDBC wrapper writes JDBC log files to the jdbc_wrapper_prod_id.log file, where prod_id is the product ID. The log file is stored in the directory specified by the Db2 database manager configuration parameter DIAGPATH. The default directory on UNIX systems is inst_home/sqllib/db2dump.
Recommendation: Setting this server option to YES will impact the performance of your system and you should not enable logging in production systems.
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.
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.
TIMEFORMAT Specifies the time format that the data source uses. Use 'hh12', 'hh24', 'mm', 'ss', 'AM', and 'A.M.' to specify the time format. For example, the format 'hh24:mm:22' specifies a time such as 16:00:00. The format 'hh12:mm:ss AM' specifies a time such as 8:00:00 AM. The value can contain null values.
TIMESTAMPFORMAT Specifies the timestamp format that the data source uses. Valid values are in the format that the DATEFORMAT option and the TIMEFORMAT option use. Specify 'n' for a tenth of a second, 'nn' for a hundredth of a second, 'nnn' for milliseconds, and so on, up to 'nnnnnn' for microseconds. For example, the format 'YYY-MM-DD-hh24:mm:ss.nnnnnn' specifies a timestamp such as 1994-01-01-24:00:00.000000. The value can contain null values.

TRUSTSTORE

Optional. Specifies the full path name of the truststore file to be used when SSL is enabled and server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.

TRUSTSTOREPASSWORD

Optional. Specifies the password of the truststore.
URL Specifies the JDBC connection string of the remote server.
The JDBC connection string consists of three parts that are all separated by a colon:
  • Database protocol
  • Database type name or connectivity driver name
  • Database identity through an alias or sub-name
Example
In this example, the JDBC connection string is jdbc:db2://cn.ibm.com:50471/testdb:

You can register the server against multiple JDBC data sources if the JDBC driver conforms to the JDBC specification version 3.0 and above. See your JDBC driver documentation for the JDBC specifications and information on how to set the URL server option.

Important: If you specify this option, you must also specify the DRIVER_CLASS server option.
VARCHAR_NO_TRAILING_BLANKS Specifies whether the data source contains VARCHAR columns that contain at least one trailing blank character. The default is N; VARCHAR columns contain at least one trailing blank character.

User mapping options

Table 3. User mapping options for JDBC
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.
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.

Column options

Table 4. Column options for JDBC
Name Description
NUMERIC_STRING Specifies whether the column contains strings of numeric characters that include blanks. Valid values are Y and N. The default is N; the column does not contain numeric strings that include blanks. If the column contains only numeric strings followed by trailing blanks, do not specify Y. When NUMERIC_STRING is set to Y for a column, the query optimizer recognizes that the column contains no blanks that could interfere with the sorting of the data in the column. Use this option when the collating sequence of a data source is different from the collating sequence that the federated server uses. Columns that use this option are not excluded from remote evaluation because of a different collating sequence.
VARCHAR_NO_TRAILING_BLANKS Specifies whether there is at least one trailing blank in the VARCHAR column.