Teradata DSN Options - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-25
dita:mapPath
fxv1527114222338.ditamap
dita:ditavalPath
Audience_PDF_product_legacy_odbc_include.ditaval
dita:id
B035-2526
lifecycle
previous
Product Category
Teradata Tools and Utilities

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. For details, 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 above list, type the name of the user-defined session character set.

DateTimeFormat=[A|I]A A

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 New Teradata ODBC Driver. The last character that represents TIMESTAMP is always ANSI.

DataSourceDNSEntries The DataSourceDNSEntries DSN option notifies the ODBC Driver for Teradata how many entries are defined in DNS for the database name. The initial value of this option controls how the 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 the ODBC Driver for Teradata to lookup 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 Teradata 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 the 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.

EnableUDFUpload=[Yes|No]
Specifies whether the ODBC driver will support 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® Database 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 the ODBC Driver performs read-ahead to receive the next response message while the current message is being processed.
  • Yes (default) - the ODBC Driver 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 - the ODBC Driver 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.
integer 0>LoginTimeout=< Defines the number of paused seconds before a virtual circuit is established with Teradata Database for login.

Default is 20.

Enter an integer value greater than or equal to 0.

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

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

This value can be adjusted dynamically if Teradata 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.

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

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 Teradata 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.
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.
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 Teradata Database.

Default is 1025.

Do not change this value unless instructed to do so by Technical Support.
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® Database 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]

No (default) - Encrypt only logon information.
  • Enables the Teradata gateway and ODBC Driver for Teradata to communicate in an encrypted manner.
  • 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.
  • Yes -
    • SQLTables() and SQLProcedures() use dbc.tablesVX and dbc.databasesVX.
    • SQLColumns() and SQLProcedureColumns() use dbc.columnsVX instead of dbc.columnsV.
    • SqlStatistics() uses dbc.tablesizeVX.
  • No (default) -
    • SQLTables() and SQLProcedures() use dbc.tablesV and dbc.databasesV.
    • SQLColumns() and SQLProcedureColumns() use dbc.columnsV.

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 OS X.
ClientKanjiFormat={SJIS|EUC|Big5|Network Korean| GB}
Using this feature is not recommended, because it is deprecated.
Specifies which character set to use for the user's choice of character set format:
  • SJIS
  • EUC
  • Big5
  • Network Korean
  • GB

Default is set by the user.

The data returned is translated from the session character set to the ClientKanjiFormat for SJIS and EUC only.

For Big5, NetworkKorean, and GB, this value should match the session character set.