Connection String Options
The following is the full list of the options you can configure in the connection string for this provider.
Case Sensitive | Specifies case sensitivity in the request values. |
Catalog | The name of the catalog to use. |
Commit Failure | Specifies the behavior if a commit fails. |
Commit Interrupt | Specifies the behavior if a commit is interrupted. |
Compensate | If enabled, compensation blocks will be run if the transaction rolls back. Possible values: disabled or enabled. Default value is disabled. |
Connect Timeout | The time-out for initial connection, in seconds. |
Data Source | The name of the TDV data source. |
Default Catalog | The default catalog for a specified connection. |
Default Schema | The default schema for a specified connection. |
Direct Query Limit | Limits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time. |
Domain | The TDV domain to which the DataSource belongs. |
Enable Failover | Specifies whether to enable failover in the case a connection fails. |
Enable Fast Exec | Specifies whether to enable fast execution of queries. |
Enable Reconnect On Error | Specifies cluster reconnection behavior. |
Encrypt | Specifies whether to encrypt the connection using SSL. |
Fetch Bytes | The maximum number of rows to fetch for a batch based on batch size, in bytes. |
Fetch Rows | Maximum number of rows to fetch for a batch. |
Host | The name of the server running TDV Server. |
Ignore Trailing Spaces | Specifies whether to ignore trailing spaces at the end of values. |
Kerberos KDC | The Kerberos Key Distribution Center (KDC) service used to authenticate the user. |
Kerberos Realm | The Kerberos Realm used to authenticate the user with. |
Kerberos SPN | The Service Principal Name for the Kerberos Domain Controller. |
Locale | Value that defines the user's language and country. |
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
Logfile | A path to the log file. |
Maximum Column Size | The maximum column size. |
Max Log File Size | A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. |
Max Rows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
No Metadata | Blocks return of result-set metadata during query execution. |
Optimization Prepare | Specifies whether to optimize prepare requests sent to TDV. |
Other | Hidden properties needed only in specific use cases. Note: Streaming performance for the return of result sets can be improved by tuning the following hidden properties. These are set to true by default to improve performance. • enableFlood • stripDuplicates |
Param Mode | Controls the behavior of OUT parameters for stored procedures. |
Password | The user's password. |
Port | The port of the TDV server. |
Query Passthrough | Whether or not the provider will pass the query to TDV as-is. |
Readonly | You can use this property to enforce read-only access to TDV from the provider. |
Register Output Cursors | Specifies how to handle output cursors. |
Request Timeout | The time-out for query commands and other requests, in seconds. |
Session Timeout | Session inactivity time-out, in seconds. |
SSL Client Cert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
SSL Client Cert Password | The password for the TLS/SSL client certificate. |
SSL Client Cert Subject | The subject of the TLS/SSL client certificate. |
SSL Client Cert Type | The type of key store containing the TLS/SSL client certificate. |
SSL Server Cert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSO | The single-sign-on (SSO) type to use to authenticate. |
Strip Trailing Zeros | Determines whether decimal result values are to be returned with trailing zeroes removed. |
Tables | Restrict the tables reported to a subset of the available tables. For example: Tables=TableA,TableB,TableC. |
Trace Folder | The absolute directory to save the trace file. |
Trace Level | The level of information to log. |
User | The username provided for authentication with TDV Server. |
User Tokens | Authentication values that can be packaged for delivery. |
Verbosity | The verbosity level that determines the amount of detail included in the log file. |
Views | Restrict the views reported to a subset of the available tables. For example: Views=ViewsA,ViewsB,ViewsC. |
Case Sensitive
Specifies case sensitivity in the request values.
Data Type
bool
Default Value
false
Remarks
Specifies case sensitivity in the request values. By default (false), requests are not case-sensitive.
Catalog
The name of the catalog to use.
Data Type
string
Default Value
""
Remarks
This field allows you to limit the Catalog to the one explicitly specified. If not set, the connector will retrieve the available catalogs from the TDV server.
Commit Failure
Specifies the behavior if a commit fails.
Data Type
string
Default Value
""
Remarks
Specifies the behavior if a commit fails. Possible values are: rollback or bestEffort.
Commit Interrupt
Specifies the behavior if a commit is interrupted.
Data Type
string
Default Value
""
Remarks
Specifies the behavior if a commit is interrupted. Possible values are: ignore, log, fail.
Compensate
The correcting behavior.
Data Type
string
Default Value
"disabled"
Remarks
If enabled, compensation blocks will be run if the transaction rolls back. Possible values: disabled or enabled. Default value is disabled.
Connect Timeout
The time-out for initial connection, in seconds.
Data Type
int
Default Value
0
Remarks
This property was added for AWS Data Pipeline compatibility.
The time-out for initial connection, in seconds. Use 0 (zero) for infinite time-out.
Data Source
The name of the TDV data source.
Data Type
string
Default Value
""
Remarks
Data source refers to the TDV database name published in the Data Services node.
Default Catalog
The default catalog for a specified connection.
Data Type
string
Default Value
""
Remarks
The default catalog for a specified connection.
Default Schema
The default schema for a specified connection.
Data Type
string
Default Value
""
Remarks
The default schema for a specified connection.
Direct Query Limit
Limits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time.
Data Type
string
Default Value
"10000"
Remarks
Limits the number of rows returned when using the DirectQuery Mode. This limit only applies when aggregation is not being used. Queries with SUM, MIN, MAX, GROUP BY, and so on will not be limited.
Domain
The TDV domain to which the DataSource belongs.
Data Type
string
Default Value
""
Remarks
The TDV domain to which the DataSource belongs.
Typically the domain is 'composite' for installations with locally defined users.
Enable Failover
Specifies whether to enable failover in the case a connection fails.
Data Type
bool
Default Value
false
Remarks
When set to "True" and a connection to the main host fails, the connector will attempt to connect to other machines in the cluster. The additional machines in the cluster are retrieved from the main host during the initial connection.
Enable Fast Exec
Specifies whether to enable fast execution of queries.
Data Type
bool
Default Value
false
Remarks
Values are true or false (default).
Results are processed and returned immediately (instead of round trip) when a query is submitted, potentially improving performance of low latency queries.
Enable Reconnect On Error
Specifies cluster reconnection behavior.
Data Type
bool
Default Value
false
Remarks
Specifies cluster reconnection behavior.
Encrypt
Specifies whether to encrypt the connection using SSL.
Data Type
bool
Default Value
false
Remarks
When set to true, automatically passes messages to the SSL port for processing with the TDV SSL Certificate.
Fetch Bytes
The maximum number of rows to fetch for a batch based on batch size, in bytes.
Data Type
int
Default Value
131072
Remarks
The maximum number of rows to fetch for a batch based on batch size, in bytes.
Setting FetchBytes to a very large number can cause an Out Of Memory error in the server. The value set for FetchBytes affects the memory used on the client and the TDV server, so the value should be set based on the heap size configured.
Fetch Rows
Maximum number of rows to fetch for a batch.
Data Type
int
Default Value
500
Remarks
Maximum number of rows to fetch for a batch. Set to 0 (zero) to return an unlimited number of rows.
Host
The name of the server running TDV Server.
Data Type
string
Default Value
""
Remarks
This property should be set to the name or network address of the computer running TDV Server.
Ignore Trailing Spaces
Specifies whether to ignore trailing spaces at the end of values.
Data Type
bool
Default Value
false
Remarks
Specifies whether to ignore trailing spaces at the end of values.
Kerberos KDC
The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
Data Type
string
Default Value
""
Remarks
The Kerberos properties are used when using Windows Authentication. The connector will request session tickets and temporary session keys from the Kerberos Key Distribution Center (KDC) service. The Kerberos Key Distribution Center (KDC) service is conventionally colocated with the domain controller. If Kerberos KDC is not specified the connector will attempt to detect these properties automatically from the following locations:
• Java System Properties: Kerberos settings can be configured in Java using the config file krb5.conf, or using the system properties java.security.krb5.realm and java.security.krb5.kdc. The connector will use the system settings if KerberosRealm and KerberosKDC are not explicitly set.
• Domain Name and Host: The connector will infer the Kerberos Realm and Kerberos KDC from the configured domain name and host as a last resort.
Note: Windows authentication is supported in JRE 1.6 and above only.
Kerberos Realm
The Kerberos Realm used to authenticate the user with.
Data Type
string
Default Value
""
Remarks
The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name. If Kerberos Realm is not specified the connector will attempt to detect these properties automatically from the following locations:
• Java System Properties: Kerberos settings can be configured in Java using a config file (krb5.conf) or using the system properties java.security.krb5.realm and java.security.krb5.kdc. The connector will use the system settings if KerberosRealm and KerberosKDC are not explicitly set.
• Domain Name and Host: The connector will infer the Kerberos Realm and Kerberos KDC from the user-configured domain name and host as a last resort. This might work in some Windows environments.
Note: Kerberos-based authentication is supported in JRE 1.6 and above only.
Kerberos SPN
The Service Principal Name for the Kerberos Domain Controller.
Data Type
string
Default Value
""
Remarks
If the Service Principal Name on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, set the Service Principal Name here.
Locale
Value that defines the user's language and country.
Data Type
string
Default Value
""
Remarks
Value that defines the user's language and country.
Location
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type
string
Default Value
""
Remarks
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.
The schema files are deployed alongside the connector assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.
Logfile
A path to the log file.
Data Type
string
Default Value
""
Remarks
For more control over what is written to the log file, take a look at Verbosity.
Maximum Column Size
The maximum column size.
Data Type
string
Default Value
"16000"
Remarks
Some tools restrain the largest size of a column or the total size of all the columns selected. You can set the MaximumColumnSize to overcome these schema-based restrictions. The connector will not report any column to be larger than the MaximumColumnSize.
Set a MaximumColumnSize of zero to eliminate limits on column size, as shown in the following example:
SQLSetConnectAttr(hdbc, 20002, (SQLPOINTER)2048, 0);
The following are a few examples of how you can use this property to avoid compatibility issues with several tools:
• Oracle ODBC Gateway: Set MaximumColumnSize=4000 to avoid the ORA-28562 data truncation error. Note that Oracle ODBC Gateway additionally requires that you set the MapToWVarchar connection property to false.
• Microsoft Access: Set MaximumColumnSize=255 to report string fields as TEXT instead of MEMO in Access. MEMO fields have no length limit but have restrictions on joins and filters. TEXT fields have a fixed length but support more functionality in Access tables.
Max Log File Size
A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
Data Type
string
Default Value
"20MB"
Remarks
A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 20MB. Values lower than 100kB will use 100kB as the value instead.
Max Rows
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Data Type
string
Default Value
"-1"
Remarks
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
No Metadata
Blocks return of result-set metadata during query execution.
Data Type
bool
Default Value
false
Remarks
Blocks return of result-set metadata during query execution.
Optimization Prepare
Specifies whether to optimize prepare requests sent to TDV.
Data Type
bool
Default Value
true
Remarks
When set to "True" (default), the connector will submit the query in a single request to TDV.
When set to "False", the connector will submit an initial prepare request to TDV.
Other
Hidden properties needed only in specific use cases.
Data Type
string
Default Value
""
Remarks
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Param Mode
Controls the behavior of OUT parameters for stored procedures.
Data Type
string
Default Value
"normal"
Remarks
Controls the behavior of OUT parameters for stored procedures.
Valid values are:
normal | Report OUT parameters in procedure metadata as OUT parameters. |
return | Report OUT parameters as return values. |
omit | Omit OUT parameters from metadata. |
omitCursors | Omit output cursors from metadata. |
Password
The user's password.
Data Type
string
Default Value
""
Remarks
The password provided for authentication with the TDV Server.
Port
The port of the TDV server.
Data Type
int
Default Value
9401
Remarks
The port of the server hosting the TDV Server.
Default is 9401 (plaintext) and the SSL protected port is 9403.
Query Passthrough
Whether or not the provider will pass the query to TDV as-is.
Data Type
bool
Default Value
false
Remarks
Whether or not the connector will pass the query to TDV as-is.
Readonly
You can use this property to enforce read-only access to TDV from the provider.
Data Type
bool
Default Value
false
Remarks
If this property is set to true, the connector will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.
Register Output Cursors
Specifies how to handle output cursors.
Data Type
bool
Default Value
false
Remarks
Specifies how to handle output cursors.
Valid values are:
true | Bind or register output cursors as output parameters. |
false | Do not bind or register output cursors as output parameters; instead, use SQLMoreResults or Statement.getMoreResults() to access the cursors. |
Request Timeout
The time-out for query commands and other requests, in seconds.
Data Type
int
Default Value
0
Remarks
The time-out for query commands and other requests, in seconds.
Session Timeout
Session inactivity time-out, in seconds.
Data Type
int
Default Value
0
Remarks
Session inactivity time-out, in seconds. Set to 0 (zero) for infinite time-out.
SSL Client Cert
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
Data Type
string
Default Value
""
Remarks
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. Please refer to the SSLClientCertSubject field for details.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
MY | A certificate store holding personal certificates with their associated private keys. |
CA | Certifying authority certificates. |
ROOT | Root certificates. |
SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).
SSL Client Cert Password
The password for the TLS/SSL client certificate.
Data Type
string
Default Value
""
Remarks
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
SSL Client Cert Subject
The subject of the TLS/SSL client certificate.
Data Type
string
Default Value
"*"
Remarks
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=support@cdata.com". Common fields and their meanings are displayed below.
Field | Meaning |
CN | Common Name. This is commonly a host name like www.server.com. |
O | Organization |
OU | Organizational Unit |
L | Locality |
S | State |
C | Country |
E | Email Address |
If a field value contains a comma it must be quoted.
SSL Client Cert Type
The type of key store containing the TLS/SSL client certificate.
Data Type
string
Default Value
""
Remarks
This property can take one of the following values:
USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
MACHINE | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
PPKFILE | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
SSL Server Cert
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type
string
Default Value
""
Remarks
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine will be rejected.
This property can take the forms:
Description | Example |
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
SSO
The single-sign-on (SSO) type to use to authenticate.
Data Type
string
Default Value
"Disable"
Remarks
The single-sign-on (SSO) type to use to authenticate. Valid values are: Disable, Kerberos, and NTLM.
Valid on Windows platform only.
Default is "Disable" which forces the client to provide a user and password to authenticate.
Strip Trailing Zeros
Determines whether decimal result values are to be returned with trailing zeroes removed.
Data Type
bool
Default Value
false
Remarks
Determines whether decimal result values are to be returned with trailing zeroes removed.
Tables
Restrict the tables reported to a subset of the available tables. For example: Tables=TableA,TableB,TableC.
Data Type
string
Default Value
""
Remarks
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. For example: Tables=TableA,TableB,TableC
Trace Folder
The absolute directory to save the trace file.
Data Type
string
Default Value
""
Remarks
The absolute directory to save the trace file.
Trace Level
The level of information to log.
Data Type
string
Default Value
"error"
Remarks
The level of information to log. Valid values are: off, fatal, error (default), warn, info, debug, and all.
User
The username provided for authentication with TDV Server.
Data Type
string
Default Value
""
Remarks
The username provided for authentication with TDV Server.
User Tokens
Authentication values that can be packaged for delivery.
Data Type
string
Default Value
""
Remarks
Authentication values that can be packaged for delivery.
The URL can pass the user_tokens property to the server at the init command, in the form: " user_tokens=(" NAME "=" VALUE ( "," NAME "=" VALUE )* " )"
Verbosity
The verbosity level that determines the amount of detail included in the log file.
Data Type
string
Default Value
"1"
Remarks
The verbosity level determines the amount of detail that the connector reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:
1 | Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors. |
2 | Setting Verbosity to 2 will log everything included in Verbosity 1 and additional information about the request, if applicable. |
3 | Setting Verbosity to 3 will additionally log the body of the request and the response. |
4 | Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation. |
5 | Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands. |
The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.
Views
Restrict the views reported to a subset of the available tables. For example: Views=ViewsA,ViewsB,ViewsC.
Data Type
string
Default Value
""
Remarks
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. For example: For example: Views=ViewsA,ViewsB,ViewsC