ODBC 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.
The following tables list the options that apply to this data source and identify the required options that you must specify.
Wrapper options
| 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. Important: If
you set this option to Y for a UNIX system,
you must also set the DB2_SOURCE_CLIENT_MODE wrapper option.
|
| DB2_SOURCE_CLIENT_MODE | Specifies that the client for the data source
is 32-bit and that the database instance on the federated server is
64-bit. The only valid value is 32-bit. This option is valid only
for UNIX. Important: If you set this
option, you must also set the DB2_FENCED wrapper option to Y.
|
| 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. |
| MODULE | Required for federated servers that run on a UNIX system. Specifies the full path of the library that contains the ODBC Driver Manager implementation or the SQL/CLI implementation. There is no default for UNIX. On a Microsoft Windows system, the default is odbc32.dll. |
Server options
| Name | Description |
|---|---|
| CODEPAGE | Specifies the code page that corresponds to the coded character set of the client configuration for the data source. On UNIX and Windows systems that use a non-Unicode federated database, the default is the code page that the federated database uses. 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. The CODEPAGE should be the same as IANAAppCodePage. For example, if IANAAppCodePage is 106 (indicating UTF-8), CODEPAGE is "1208". If IANAAppCodePage is not specified, CODEPAGE is the same as the data source database code page. |
| 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. If you specify a value with single quotes, you need to preserve the single quotes. Specify additional single quotes, '', to preserve the single quotes. These are two additional single quotes, not a double quote. For example: '''YYYY-MM-DD'''. |
| DBNAME | Specifies the name of the data source database that you want to access. |
| DB2_AUTHID_QUOTE_CHAR | Specifies the quote characters that are used for authid names such as schema and user names. If you do not specify this option, double quotes are used by default. |
| DB2_ID_QUOTE_CHAR | Specifies the quote characters that are used for delimited identifiers such as column names. If you do not specify this option, double quotes are used by default. |
| 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_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_TABLE_QUOTE_CHAR | Specifies the quote characters that are used for table names. If you do not specify this option, double quotes are used by default. |
| 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. |
| ENABLE_BULK_INSERT | Specifies whether bulk insert processing is enabled for the Netezza data source. Valid values are Y and N. The default is N. |
| 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. |
| HOST | Required in DSN-less connection mode. Specify the IP address of data sources. HOST and NODE cannot be dropped at the same time. |
| INFORMIX_SERVER_NAME | Required in DSN-less connection mode when accessing an Informix server. Specify the server name of the Informix server. |
| 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. Specifies the name of the node or the system DSN name that is assigned to the ODBC data source when the DSN is defined. 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. Possible values:
|
| PORT | Optional. The port number of the data source. The default depends on the type of the remote server (see Table 5). |
| 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. |
| SERVICE_NAME | Required in DSN-less connection mode when accessing an Oracle server. Specify the services name of the Oracle server. |
| SERVER_PRINCIPAL_NAME | Specifies the server principal name on remote
data source. The valid format of principal name is servicename/hostname.com@REALM |
| SSL_KEYSTORE | Optional. Specifies that the federation server
is to establish a connection with the data source using secure socket
layer (SSL) communication. The value of this option is the fully-qualified
file name of the truststore file that contains the SSL certificate
that is to be used. The truststore file must have one of the following
formats:
|
| SSL_KEYSTOREPASSWORD | Optional. Specifies the password of the truststore file specified by the SSL_KEYSTORE option. |
| 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. |
| 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. |
| SUPPORT_PARALLELISM | The valid value of this option is 'Y' or 'N', the
default value is 'N'. If the option set to 'Y' it indicates that this server supports parallel
fetching parallelism in federation server. Note: Parallel fetching is not a
specific functionality of remote data source, it only means federation server can uses some split
conditions to split the SQL query that references this data source. Once this option is set to ‘Y’,
query against this server's nickname can use parallel fetching.
Parallel fetching leverages all computing resource across all applicable database partitions. It
will split one query into multiple parts and then every database partition will execute the partial
query, that means there are multiple connections to the remote data source at the same time.
Compared to serial fetching, this will decompose the query job to multiple smaller jobs, and this
will increase performance. Nevertheless, some query cannot be split. In such cases, the query
compiler will generate a serial access plan even this option is set to 'Y'. This option is developed
to improve federation performance, it breaks the restriction that federation server can only execute
on the coordinator partition. This option can only be enabled in DPF or Big SQL environment. This
option can only be set to 'Y' for PostgreSQL, Greenplum and Netezza data sources. |
User mapping options
| Name | Description |
|---|---|
| CLIENT_PRINCIPAL_NAME | Specifies the kerberos client principal name. This option is also known as “Ticket Granting Ticket (TGT)”. |
| KERBEROS_KEYTAB | Specifies the client principal keytab file. This file holds the encrypted password of client principal. This file can be generated using kadmin tool. |
| 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
| 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. |
| Remote Server | Type | Default Port |
|---|---|---|
| Amazon Redshift | REDSHIFT | 5439 |
| Apache Hive data warehouse | HIVE | 10000 |
| Apache Spark SQL | SPARK_ODBC | 10001 |
| Cloudera Impala | IMPALA | 21050 |
| IBM BigSQL | BIGSQL | 32051 |
| Db2® for IBM i | DB2/ISERIES | 50000 |
| IBM Db2 for VM | DB2/VM | 50000 |
| IBM Db2 for z/OS® | DB2/ZOS | 50000 |
| IBM Db2 Universal Database | DB2/UDB | 50000 |
| IBM Netezza | NETEZZA | 5480 |
| Microsoft AZURE SQL | AZURE | 1433 |
| Microsoft SQL Server | MSSQL_ODBC | 1433 |
| Oracle | ORACLE_ODBC | 1521 |
| Oracle Cloud | ORACLE_CLOUD | 1521 |