17.20 - Teradata DSN Options - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2022-06-21
dita:mapPath
uqj1639470627591.ditamap
dita:ditavalPath
nkw1500504256726.ditaval

The keyword options in the following table can be added to the Data Source Specification and Default Data Source Specification sections of the odbc.ini file. The options can only be configured through the odbc.ini file. The following table lists the options that are configurable in the odbc.ini file.

ODBC Driver for Teradata has established specific application uses for available DSN settings. See DSN Settings for Third-Party Applications.

Keyword/Synonym Description
AccountStr=<account>

Or

Account=<account>

Specifies the account value to be entered during database logon. If unspecified, the database defaults to the account value specified when the user was created or modified.
This option can help isolate users by determining the applications users are running or restricting users from logging on.
CharacterSet=<charset name>

Or

Charset=<charset name>

Specifies the session character set. Default is ASCII.

Specify the character set for the session. It is strongly recommended to use the default ASCII session only for 7-bit ASCII characters. UTF8 is the recommended default session character set for all languages including US English. To use a different character set than is chosen by default, specify or select it here.

The available options:
  • ASCII
  • UTF8
  • UTF16
  • LATIN1252_0A
  • LATIN9_0A
  • LATIN1_0A
  • Shift-JIS (Windows, DOS compatible, KANJISJIS_0S)
  • EUC (Unix compatible, KANJIEC_0U)
  • IBM Mainframe (KANJIEBCDIC5035_0I)
  • KANJI932_1S0
  • BIG5 (TCHBIG5_1R0)
  • GB (SCHGB2312_1T0)
  • SCHINESE936_6R0
  • TCHINESE950_8R0
  • NetworkKorean (HANGULKSC5601_2R4)
  • HANGUL949_7R0
  • ARABIC1256_6A0
  • CYRILLIC1251_2A0
  • HEBREW1255_5A0
  • LATIN1250_1A0
  • LATIN1254_7A0
  • LATIN1258_8A0
  • THAI874_4A0
For user-defined session character sets not shown on the previous list, type the name of the user-defined session character set.
DateTimeFormat=[A|I]AA Specifies the format of DATE, TIME, and TIMESTAMP data types.
  • A - ANSI
  • I - Integer
The three-character specification represents:
  • First character = DATE format
  • Second character = TIME format
  • Third character = TIMESTAMP format

The recommended settings are either the AAA (default), or the IAA (optional) formats. Because the Integer data type has been deprecated for the TIME format, it is not recommended. For information, see "Integer Time" in Deprecated Features for ODBC Driver for Teradata 16.20 and Later Versions. The last character that represents TIMESTAMP is always ANSI.

DataSourceDNSEntries The DataSourceDNSEntries DSN option notifies ODBC Driver for Teradata how many entries are defined in DNS for the database name. The initial value of this option controls how ODBC Driver for Teradata resolves database names to IP addresses. If this value is not set, the default value is undefined (empty). If multiple database names are provided in ODBC DSN, the DataSourceDNSEntries option is applicable to all names.
If a database is identified by IP address instead of a name in the ODBC DSN or connection-string, the DataSourceDNSEntries option is ignored.

DataSourceDNSEntries=undefined (default setting) is recommended for best results. This setting enables ODBC Driver for Teradata to look up DNS dynamically and find all available COPs for a given database name. Using this approach, ODBC Driver for Teradata will automatically detect new nodes added to the database (and DNS) in the future, without ODBC modification.

DataSourceDNSEntries= 0 indicates that DNS does not contain cop entries for the database name. The database name will only be resolved by itself. No attempt will be made to resolve using a cop suffix. This behavior can be desirable in an environment utilizing DNS to load balance. When DNS is used for load balancing, administrators can configure DNS to provide a different IP address or multiple IP addresses in different order each time the database name is resolved using DNS.

DataSourceDNSEntries= value. Entering a non-zero value indicates that DNS contains cop entries for the database name and the last cop entry is value. The first connection attempt will chose a random number between 1 and value. Each subsequent connection will then increment to the next number (round-robin). This approach will not encounter costly DNS resolution failures (how costly depends on how the DNS is configured). However, if additional entries are added to DNS at a later time, they will not be discovered by ODBC Driver for Teradata unless the supplied value is increased.

DontUseHelpDatabase=[Yes|No]

Or

DontUseHelpDB=<[Yes|No]

Specifies whether the Help Database is used.
  • No (default) - The driver uses the HELP DATABASE command.
  • Yes - SQLTables uses a SELECT statement instead of the HELP DATABASE command when no wildcard characters are used in SQLTables.
SQLTables uses dbc.tables or dbc.tablesx, depending on the UseXViews setting.
DontUseTitles=[Yes|No] Specifies whether column names or column titles are returned.
  • No - Returns column titles, if they are defined; otherwise, returns column names.
  • Yes (default) - Returns column names rather than column titles, as required by some applications, such as Crystal Reports.

Column titles for SQLColumns are shown in the LABEL column.

EnableRedrive=[Yes|No|Default] Determines whether requests on the session participate in Redrive, or not, or use the database-side default value.

Default value is “Default”.

EnableUDFUpload=[Yes|No]
Specifies whether ODBC Driver for Teradata supports UDF source file uploads.
  • Yes - UDF source files are uploaded.
  • No (default) - UDF source files are not uploaded.

For more information, see ElicitFile in Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417, and Teradata Vantage™ - SQL External Routine Programming, B035-1147.

EnableExtendedStmtInfo=[Yes|No] Specifies whether extended statement information is used when it is available from the database.
  • Yes (default) - Extended statement information is requested and used.

    If extended statement information is available, the ODBC API function SQLDescribeParam is supported and SQLGetFunctions returns SQL_TRUE (supported) for SQL_API_SQLDESCRIBEPARAM.

  • No - Extended statement information is not used, even if the database supports it.

    If extended statement information is not available, SQLDescribeParam is not supported and SQLGetFunctions returns SQL_FALSE (not supported) for SQL_API_SQLDESCRIBEPARAM.

Teradata Database versions V2R6.2 and up support extended statement information, which includes additional metadata for parameters used in SQL requests and for columns in result sets.
EnableReadAhead=[Yes|No] Specifies whether ODBC Driver for Teradata performs read-ahead to receive the next response message while the current message is being processed.
  • Yes (default) - ODBC Driver for Teradata reads ahead by requesting the next response message from the database when the current response message being processed is not the last. The database can have one request active for each session at any point in time. An active request is either an SQL request which is executing or a request for the next part of the result from an earlier SQL request.
  • No - ODBC Driver for Teradata only requests the next response message from the database when the current response message has been processed by the driver.
IANAAppCodePage=<ODBC application code page> The current ODBC application code page is defined as IANAAppCodePage.

See ODBC Application Code Page Values (Linux/UNIX and Apple macOS) for a list of valid ODBC application code page values and cautionary information.

IgnoreODBCSearchPattern=[Yes|No]

Or

IgnoreSearchPat=<[Yes|No]

Specifies that characters _ and % work like regular wildcard characters for values given to table names, schema names, and so forth when passed to catalog functions, such as SQLTables.
This option is useful for applications, such as Microsoft Access, that do not support search patterns.
  • No (default, except for Microsoft Access) - Characters _ and % are processed as regular characters.
    This setting causes Microsoft Access to use the Data Source section of the odbc.inifile to process search patterns.
  • Yes - Characters _ and % are not processed. Instead use the following commands for a normal search pattern:
    • SQLTables
    • SQLColumns
    • SQLTablePrivileges
    • SQLProcedures
    • SQLProcedureColumns
    • SQLGetInfo
SQL_SEARCH_PATTERN_ESCAPE returns an empty string.
LoginTimeout=<integer>=0> Defines the number of paused seconds before a virtual circuit is established with the database for login.

Default is 20.

Enter an integer value greater than or equal to 0.

MaxRespSize=<integer≤16775168> Limits the databae response buffer size for SQL requests.

Default is 65536 (64K). The maximum integer value is 16775168.

This value can be adjusted dynamically if the database cannot send a result within the limited packet size defined:
  • If using a slow TCP/IP interface, such as PPP or SLIP, enter a smaller value.
  • If expecting large result sets in a LAN environment, enter a larger value.
MechanismName=<MechanismName>

Or

Authentication=<MechanismName>

Identifies the authentication mechanism used for connections to the data source.

Default is determined by a configuration option that is set by the TeraGSS program in an XML file called tdgssconfigure.

Valid values are as follows:
  • Empty - The same as omitting the keyword.
  • TD2 - Selects Teradata 2 as the authentication mechanism. Username and password are required.
  • TDNEGO – selects one of the Authentication Mechanisms automatically based on the policy without user involvement.
  • LDAP - Selects LDAP as the authentication mechanism. The application provides the username and password.
  • KRB5 - Selects Kerberos as the authentication mechanism. The application provides the username and password.
  • EXTERNALBROWSER- selects CloudSSO on Windows and Apple MacOS as the Authentication Mechanism. The users’ identity is obtained through Keycloak or PingFederate login using an external browser. The user is logged on without providing a username and password.

See Network Security for complete descriptions of authentication mechanisms.

MechanismKey=<Value>

Or

AuthenticationParameter=<Value>

Value = string

A string of characters regarded as a parameter to the authentication mechanism. It is opaque for ODBC Driver for Teradata and is passed on to the Teradata authentication software called to set the mechanism.

The odbc.ini file has no security. The MechanismKey is retained in unencrypted plain text and can be viewed by any user with read-access to the file.

You can use a Teradata Wallet reference string instead of a plain text MechanismKey value by specifying the $tdwallet() token. For example:

MechanismKey=$tdwallet(RefString)

For more information, see Teradata Wallet.

NoScan=[Yes|No] Enables or disables parsing of SQL statements by ODBC Driver for Teradata. When enabled, the driver transforms ODBC escape sequences to SQL.
  • No (default) - SQL statements are parsed by ODBC Driver for Teradata.
  • Yes - SQL statements are sent unmodified to the database without parsing by ODBC Driver for Teradata.
If the SQL statements contain ODBC-specific syntax, do not enable this option. Setting this option while using ODBC-specific syntax in the SQL statement results in database reporting errors.
OutputAsResultSet=[Yes|No] Determines how stored procedure output parameters are retrieved.
  • No (default) - The values of INOUT and OUT parameters are directly updated after the procedure call. Because of this, all parameter markers must be bound to variables before the call.
  • Yes - The values of INOUT and OUT parameters are returned as a result set. They can be retrieved from the response by calling SQLFetch and SQLGetData or by calling SQLBindCol followed by SQLFetch. The result set containing the output parameters will be the first result returned by the database preceding any result sets generated by the stored procedure.

For a code sample and additional information, see Stored Procedures Dynamic Result Sets.

PrintOption=[N |P] Specifies the print option for stored procedures.

N (default) - Disables the print option when stored procedures are created.

P - Enables the print option.

ReconnectCount=<value> The maximum number of times the driver should attempt to reconnect to the database (0-99).

Default is 20.

ReconnectInterval=<value> The number of seconds (1-300) between reconnect attempts.

Default is 30.

retryOnEINTR Controls whether ODBC Driver for Teradata retries the socket system calls on an EINTR or returns an SQL_ERROR.
The affected socket system calls are as follows:
  • connect()
  • select()
  • recv()
  • send()

Values are Yes for retries or No for no retries. Default is Yes.

ReturnGeneratedKeys=<value> Determines the result from requests that insert data into identity columns (INSERT, INSERT ... SELECT, UPSERT, MERGE-INTO). These requests can optionally return a result set containing identity column values (also known as auto-generated keys) for the inserted rows.
Auto-generated key retrieval is not supported in Teradata Database versions prior to V2R6.2 and the setting of ReturnGeneratedKeys has no effect when using a pre V2R6.2 database server.
  • C - Retrieves identity column only. Returns a row count of inserted rows and a result set that contains the auto-generated keys as a single column.
  • R - Retrieves entire rows. Returns a row count of inserted rows and a result set that contains the auto-generated keys of all columns of the rows just inserted.
  • N (default) or not set - No auto-generated key retrieval. The behavior of requests that insert into identify columns is unchanged.
SessionMode=[Yes|No] Specifies the mode (Teradata or ANSI) for sessions on the database. The selected mode applies for the duration of the session.

The default value is determined by the database based on the option used in the CREATE or MODIFY USER statement.

An application cannot set SessionMode programmatically. SessionMode can be set only while connecting.
SESSIONS=<value>

The number of FastExport data connections that the driver opens, to enable performance improvements for SELECT queries that meet the criteria of the FastExport protocol.

The number of AMPs (Access Module Processors) that are available for your database determines the maximum number of FastExport data connections that can be opened.

If you set this property to a number that is greater than the number of AMPs, the driver only opens a number of connections equal to the number of AMPs.

It is recommended that you do not set this property. When this property is not set, the number of FastExport connections is determined automatically based on the database settings.
SplOption=[Y | N] Specifies the stored procedure language (SPL) option when creating stored procedures.
  • N - Created without SPL text.
  • Y (default) - Created with SPL text.
SSLMode=[Allow|Disable|Prefer|Require|Verify-CA|Verify-Full] Default="Prefer"

This option specifies which TLS mode ODBC Driver for Teradata operates in.

Valid values:

Prefer

Driver prioritizes connecting with HTTPS port.

If that connection fails, then the driver falls back to connect with TDMST port.

When Teradata Gateway TLS settings set to “enable” with TLS certificate being correctly setup, ODBC driver for Teradata only attempts connection using HTTPS port. If the connection fails, then a corresponding error message is returned.

For more details, refer to Deterministic Behavior of Prefer/Allow SSLMode.

Allow

Driver prioritizes connecting with TDMST port.

If that connection fails, then the driver falls back to connect with HTTPS port.

When Teradata Gateway TLS setting is set to “enable” with TLS certificate being correctly setup, ODBC driver for Teradata only attempts connection using TDMST port. If the connection fails, then a corresponding error message is returned.

For more details, refer to Deterministic Behavior of Prefer/Allow SSLMode.

Disable

Driver only connects on TDMST port.

Require

Driver only connects on the HTTPS port.

Verify-CA

Same as “Require” mode with additional verification of the server Certificate Authority (CA) certificate against the configured CA certificates.

Verify-Full

Same as “Verify-CA” mode with additional host name identity validation.

SSLProtocol=[TLSv1.2] SSL Protocol

Currently only TLSv1.2 is supported.

SSLCA=<path> SSL CA File Name

The default is the default of the operating environment.

This option specifies the full path and name of a .pem file containing one or more certificates for trusted Root and Intermediate CAs. If SSL Mode is not set to Verify-CA or Verify-Full, SSL CA File Name or SSL CA Path will be ignored.

SSLCAPath=<path> SSL CA Path

This option specifies the full path of the directory containing the root certificates for trusted CAs. If SSL Mode is not set to Verify-CA or Verify-Full, SSL CA File Name or SSL CA Path will be ignored.

To use this option, use “c_rehash” tool to create symbolic links for certificate files that are used by OpenSSL.

For more details, refer to OpenSSL documentation:

For more details, refer to OpenSSL documentation: https://www.openssl.org/docs/man1.1.1/man1/c_rehash.html

l

TCPNoDelay=[Yes|No] Specifies whether Transmission Control Protocol (TCP) immediately sends small packets or waits to gather packets into a single, larger packet.
This option is valid for the Teradata Data Source Entry.
  • Yes (default) - TCP immediately sends small packets. This option avoids transmission delays so a larger number of small packets, including acknowledgments, can be sent over the network.
  • No - TCP gathers small packets into a single packet. This option can reduce network traffic, but it can also delay packet transmission. Refer to the TCP documentation for complete information.
TranslationDLL=<path> Specifies the name of the translation DLL. It is recommended that you assign a fully qualified pathname for the translation DLL.

User-defined session character sets can be used without a value for this option. Conversion is then based on the current application code page.

For details, see User-Defined Session Character Set Support and Translation DLLs.

TranslationOption=<integer> A 32-bit value with a specific meaning for a given translation DLL.

For example, it could specify a certain character set translation.

If unspecified, a zero value is passed as an option to SQLDriverToDataSource and SQLDataSourceToDriver of the Translation DLL.

TDMSTPortNumber=<integer> Specifies the number of the port that accesses the database.

Default is 1025.

Do not change this value unless instructed to do so by your DBA.
HTTPS_PORT=<integer> Specifies the number of the port that accesses the database via TLS connection.

Default is 443.

Do not change this value unless instructed to do so by your DBA.
Type=<Default|FastExport> Specifies whether the driver uses the FastExport protocol to improve the performance of SELECT queries that meet certain criteria.
  • Default: The driver does not use FastExport for any queries, and only runs queries using the standard protocol.
  • FastExport: When connected to a database that supports FastExport, the driver uses it to run queries that meet the FastExport criteria. For all other queries, the driver falls back to using the standard protocol.
UDFUploadPath=<path>

Specifies the fully qualified path where UDF source files will be found. If defined, the driver looks at this location for files the database requests, unless the database gives a fully qualified path as part of the file name (field must be empty).

For more information, see "ElicitFile" in Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 and Teradata Vantage™ - SQL External Routine Programming, B035-1147.

USE2XAPPCUSTOMCATALOGMODE=[Yes|No]

Or

2XAPPCUSTOMCATALOGMODE=[Yes|No]

Provides backwards compatibility for ODBC 2.x applications that use a noncompliant search patterns.
Earlier versions of ODBC Driver for Teradata allowed users to create search patterns other than the % search pattern stated in the ODBC Programmer’s Reference specification. On noncompliant systems, if a NULL value is passed to the SQLTables API for the SchemaName argument, the result is a search for tables by userid, DBC, and default database schema names, rather than the % search pattern.
  • No (default) - Use the % search pattern.
  • Yes - Allow searches by userid, DBC, and default database schema names.
UseDataEncryption=[Yes|No]

Or

DataEncryption=[Yes|No]

Enables the Teradata gateway and ODBC Driver for Teradata to communicate in an encrypted manner.
  • No (default) - Encrypt only logon information.
  • Yes - Enable data encryption.
UseXViews=[Yes|No] Specifies whether X tables are used.
X tables only contain information that users have permission to access. These tables are optional for Teradata, so check to ensure they exist before using the option.
  • No (default) -
    • SQLTables() and SQLProcedures() use dbc.tablesV and dbc.databasesV.
    • SQLColumns() and SQLProcedureColumns() use dbc.columnsV.
  • Yes -
    • SQLTables() and SQLProcedures() use dbc.tablesVX and dbc.databasesVX.
    • SQLColumns() and SQLProcedureColumns() use dbc.columnsVX instead of dbc.columnsV.
    • SqlStatistics() uses dbc.tablesizeVX.

SqlStatistics() uses dbc.tablesizeV.

The Linux/UNIX system version of ODBC Driver for Teradata recognizes the following options when the LANG environment variable contains a value recognized by ODBC Driver for Teradata for Japanese, Chinese, or Korean locales. These options are not available on Apple macOS.