15.10 - Configuring a DSN Using ODBC Administrator Tool - ODBC Driver for Teradata

ODBC Driver for Teradata User Guide

prodname
ODBC Driver for Teradata
vrm_release
15.10
category
User Guide
featnum
B035-2509-035K

The ODBC Administrator Tool from Apple is available for download at http://support.apple.com/kb/DL895. This is the ODBC administrator tool recommended by Teradata. Once you have installed it, follow the following procedure to configure a DSN.

1 In the Finder under Favorites select Applications > Utilities > ODBC Administrator.

2 The User DSN tab shows a list of DSNs saved in ~/Library/ODBC/odbc.ini file. To modify or remove an existing DSN, select it, and press the respective button. To add a new user DSN click Add button.

3 Select Teradata and press OK.

4 In the ODBC driver Setup for Teradata Database dialog, fill in the required information and press OK to save the DSN.

Note: The OK button does not become available until the Name or IP Address field is filled in.

 

Table 2: ODBC Driver Setup (Apple OS X) 

Field, Check Box, or Button

Description

Data Source group box

Name

Enter the Data Source Name (DSN) that the application is to refer to when executing SQLConnect or SQLDriverConnect. The entered DSN is the name that will appear in the Data Sources dialog box during a manual connection.

Description

[Optional] Enter descriptive text about the data source in this box.

This is a comment field that is not retrievable by an SQL or ODBC command.

Teradata Server Info

Name or IP address

Perform one of the following:

  • Enter the Teradata Database name (alias or FQDN) without COP suffix.
  • Enter the name or IP address of the LAN-connected node in your system, one per line.
  • After providing the name, ODBC Driver for Teradata dynamically detects all associated COP entries.

    You must define COP names or the name without COP suffix in either a Domain Name Services (DNS) or the local hosts file.

    Authentication group box

    Mechanism

    Default = determined by a configuration option set in an XML file by TeraGSS program, tdgssconfigure.

    Specify the desired security checking mechanism. Valid values are:

  • 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 Lightweight Directory Access Protocol (LDAP) as the Authentication Mechanism. The application provides the username and password.
  • Parameter

    Users cannot enter parameters in this field. Instead, click the Change button located to the right of the field, and a new dialog box opens, allowing the parameter to be entered.

    Once a parameter is entered, the text will be masked with * characters.

    Indicate a string of characters to be regarded as a parameter of the authentication mechanism. The string is opaque to ODBC Driver for Teradata and is passed to the Teradata authentication software called to set the mechanism.

    Enclose characters [] {} () , ; ? * = ! @ in braces.

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

    $tdwallet(WalletRefString)

    For more information, see ODBC Driver for Teradata User Guide.

    Username

    Default = Cleared

    Specify a username for the default Teradata Database.

    Shows the default username that was specified during the data source(s) configuration of the driver. The default value can be overridden here. If required, the user is prompted for additional information. The username and password are Teradata-specific, not to be confused with other user ids and passwords.

    The username is interpreted in the context of the authentication mechanism.

    If the authentication mechanism allows fully qualified usernames, then the username can contain a domain or realm; for example, {judy@linedata}. Values containing a character such as @ must be enclosed in braces.

    SSO is indicated by the absence of a Username and Password.

    Teradata Wallet String

    Enter and save a Teradata Wallet reference string as password for the user. Do not need to enclose the Teradata Wallet reference string in $tdwallet() token.

    For more information, see ODBC Driver for Teradata User Guide.

    Optional group box

    Default Database

    Default = Cleared

    Specify a default database.

    Account String

    Default = Cleared

    Enter a user in Teradata Database while configuring the data source rather than having to provide account information during configuration of ODBC Driver for Teradata.

    This information can be used to help isolate users, to find out what application the users are running, or to restrict users from logging on.

    Session Character Set

    Default = ASCII

    Specify the character set for the session. To use a different character set than is chosen by default, specify or select it here.

    The options available from the drop-down list are:

  • ASCII
  • UTF8
  • UTF16
  • LATIN1252_0A
  • LATIN9_0A
  • LATIN1_0A
  • SJIS
  • EUC
  • BIG5
  • GB
  • NetworkKorean
  • IBM Mainframe (KANJIEBCDIC5038_0I)
  • Buttons

    OK

    Click to enable the driver to use any changes that have been made in the dialog box. Note that this button does not become available unless the Name box in the Data Source group box and the Teradata Server Info Name(s) or IP address(es) group box are filled in.

    Cancel

    Click to cancel any changes made to the dialog box and abort the current driver and data source selection.

    ?

    Click to obtain detailed help about this dialog box.

    Options

    Click to display the Teradata ODBC Driver Options dialog box and configure additional options and formats.

    5 (Optional) Click Options to configure additional options.

     

    Table 3: Teradata ODBC Driver Options (Apple OS X) 

    Field, Check Box, or Button

    Description

    Use Column Names

    Default = Selected

    Determines whether ODBC Driver for Teradata returns column names or column titles

    Disable Async

    Default = Cleared

    Determines whether ODBC Driver for Teradata operates in synchronous or asynchronous mode

    Using this feature is not recommended, because it is deprecated.

    Use X Views

    Default = Cleared

    Determines whether the X View is used

    No HELP DATABASE

    Default = Cleared

    Determines whether the Help Database is used

    Ignore Search Patterns

    Default = Cleared

    Determines whether search pattern characters _ and % are used in search patterns or are passed as regular characters.

    Enable Reconnect

    Default = Cleared

    Causes ODBC Driver for Teradata to determine if sessions on Teradata are to be reconnected after a system crash or reset is detected.

    Run in Quiet Mode

    Default = Selected

    Controls whether message boxes appear.

    Using this option is not recommended, because it is deprecated. For more information, see the ODBC Driver for Teradata User Guide.

    Disable Parsing

    Default = Cleared

    Disables or enables parsing of SQL statements by ODBC Driver for Teradata. When parsing is enabled, the driver parses SQL statements and transforms ODBC escape sequences into SQL.

  • When selected, ODBC Driver for Teradata does not parse SQL statements.
  • When cleared, SQL statements are parsed.
  • Enable Legacy Parser

    Default = Cleared

  • When selected, ODBC Driver for Teradata parses SQL statements using the legacy parser.
  • When cleared, SQL statements are parsed using the new parser.
  • Note: This setting is ignored if parsing is disabled ("Disable Parsing" is selected). No mapping takes place if the parser is disabled.

    Disable CALL to EXEC Conversion

    When selected, CALL statements remain as CALL statements.

    Default = Selected. (When selected, do not select the Disable Parsing option.)

    When cleared, ODBC Driver for Teradata converts CALL statements within an escape clause (that is, {CALL <name>(..)}) to EXEC statements.

    Note: Clearing this option is not recommended, because it is deprecated. For details, see the ODBC Driver for Teradata User Guide.

    Use Regional Settings for Decimal Symbol

    Default = Selected

    When selected, ODBC Driver for Teradata uses regional settings to determine the decimal symbol.

    If cleared, ODBC Driver for Teradata uses a “.” character as the decimal symbol and ignores the regional settings.

    Use Native Large Object Support

    Default = Selected

    Leaving the default value selected is recommended, because this setting is deprecated. For details, see the ODBC Driver for Teradata User Guide.

    Enables LOB support. Impacts data type mapping within ODBC Driver for Teradata. See the description for this option on UNIX systems in the ODBC Driver for Teradata User Guide.

    Return Output Parameters As ResultSet

    Note: Using this feature is not recommended, because it is deprecated. For details, see the ODBC Driver for Teradata User Guide.

    Default = Cleared

    Determines how stored procedure output parameters are retrieved by ODBC Driver for Teradata

    When selected–the values of INOUT and OUT parameters are returned as a result set. The values can be retrieved by calling SQLFetch and SQLGetData, or SQLBindCol followed by SQLFetch. The result set containing the output parameters is the first returned by the database, preceding any generated by a stored procedure.

    When cleared–the values of INOUT and OUT parameters are directly updated after the procedure call. All parameter markers must therefore be bound to variables before the call.

    Enable Data Encryption

    Default = Cleared

    When Enable Data Encryption is checked – the option directs the ODBC Driver to use Data Encryption, causing ODBC Driver for Teradata and Teradata Database to communicate with each other in encrypted manner.

    When Enable Data Encryption is cleared–Data Encryption is disabled.

    Enable Extended Statement Information

    Default = Selected

    Determines whether extended statement information is to be used by ODBC Driver for Teradata, provided that it is available from Teradata Database. Database versions from V2R6.2 and up support extended statement information, including metadata for parameters used in SQL requests and columns in result sets.

    When EnableExtendedStmtInfo is Selected – ODBC Driver for Teradata requests and uses extended statement information from the database if supported. If extended statement information is available, then the ODBC API function SQLDescribeParam is supported and SQLGetFunctions returns SQL_TRUE (supported) for SQL_API_SQLDESCRIBEPARAM.

    When EnableExtendedStmtInfo is cleared – ODBC Driver for Teradata does not request or use extended statement information from the database, even if supported. If extended statement information is unavailable, SQLDescribeParam is not supported and SQLGetFunctions returns SQL_FALSE (not supported) for SQL_API_SQLDESCRIBEPARAM.

    Session Mode

    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 Teradata Database CREATE or MODIFY USER statement.

    DateTime Format

    Assigns the ANSI formats for DATE, TIME, and TIMESTAMP.

    The default setting is IAA. Because the Integer data type has been deprecated for the TIME format, it is not recommended. For information, see the ODBC Driver for Teradata User Guide.

    Return Generated Keys

    Default = No

    Determines the result from requests that insert 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. The setting of Return Generated Keys has no effect when using a pre-V2R6.2 database server.

    Valid values are No, Identity Column, and Whole Row:

  • No = Auto-generated key retrieval is disabled (default)
  • Identity Column = Retrieve identity column only
  • Whole Row = Retrieve entire row(s)
  • When Return Generated Keys is set to Identity Column or Whole Row, a request that inserts into tables containing identity columns returns two results: a row count with the number of inserted rows and a result set containing either the auto-generated keys as a single column or the complete rows inserted. The insert request becomes similar to a macro that first inserts and then selects the identity column or all columns of the rows just inserted.

    When Return Generated Keys is set to No, the behavior of requests that insert into identity columns is not changed.

    Warning group box

    Advanced

    Click to bring up the Teradata ODBC Driver Advanced Options dialog box. The dialog box contains further setting options available; however, it is strongly recommended NOT to change these settings.

    6 (Optional) Click Advanced in the Teradata ODBC Driver Options dialog to configure advanced options.

    Note: It is strongly recommended NOT to change the default settings in the Teradata ODBC Driver Advanced Options dialog.

     

    Table 4: Teradata ODBC Driver Advanced Options (Apple OS X) 

    Field, Check Box, or Button

    Description

    Application Catalog Database

    Default = Blank

    Use this option for applications (such as PowerBuilder) that require a database for their system catalog tables.

    Enter a database name in the text box.

    Using this feature is not recommended, because it is deprecated. For details, see the ODBC Driver for Teradata User Guide.

    Maximum Response Buffer Size

    Default = 65536 (64K)

    Enter the value used to try to limit the Teradata response buffer size for SQL requests. This value can be adjusted dynamically if Teradata cannot send a result within the limited packet size defined.

    Redisplay Reconnect Wait

    This feature has been deprecated for ODBC Driver for Teradata 14.10.

    For more information, see the ODBC Driver for Teradata User Guide.

    TDMST Port Number

    Default = 1025

    Lists the port number to use to access Teradata Database. Do not change this value unless instructed to do so by Technical Support.

    State Check Level

    Default = 0

    Provides enhanced ODBC state checking that detects SQLState 24000 according to the requirements in the ODBC SDK. This is a user‑defined option.

    Using this feature is not recommended, because it is deprecated. For details, see the ODBC Driver for Teradata User Guide.

    Translation DLL Name

    Specifies the translation DLL path. Translation DLL is used to convert between session character set and application character set.

    Translation Option

    Specifies the translation DLL option. The option is used by translation DLL.

    Login Timeout

    Default = 20

    Defines a value corresponding to the number of seconds to wait when establishing a virtual circuit with Teradata for login. Enter an integer value.

    ProcedureWithPrintStmt

    Default = N

    Activates the print option when creating stored procedures.

    ProcedureWithSPLSource

    Default = Y

    Specifies the SPL option when creating stored procedures.

    Data Source DNS Entries

    The Data Source DNS Entries 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 Data Source DNS Entries option is applicable to all names.

    Note: If a database is identified by IP address instead of a name in the ODBC DSN or connection-string, the Data Source DNS Entries option is ignored. The database is identified in the Name(s) or IP address(es) field described in ODBC Driver Setup (Apple OS X).

    Data Source DNS Entries=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 the Teradata database (and DNS) in the future, without ODBC modification.

    Data Source DNS Entries = 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.

    Data Source DNS Entries = 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.

    Enable DSN Tracing

    The Enable DSN Tracing option controls whether DSN tracing is enabled or disabled.

  • Unchecked (default) = disabled
  • Checked = enabled
  • Path: Specifies the absolute path of the trace file.

    The default is /tmp/ODBC.Trace.xxxxx. If the entry is missing, a default pathname of /tmp/ODBC.Trace.xxxxx is used, where xxxxx is the pid of the creating process.

    Disable PREPARE

    Default = Cleared

    Enables ODBC Driver for Teradata to bypass asking Teradata to perform Prepare before executing a request.

    Using this feature is not recommended, because it is deprecated.

    Use TCP_NODELAY

    Default = Selected

    Valid for the Teradata DSN in ODBC Driver for Teradata.

    Transmission Control Protocol (TCP) provides an option called TCP_NODELAY to control the transmission of data.

    Use NULL for Catalog Name

    Default = Cleared

    When this option is selected – NULL values are assumed for the Catalog Name parameters in any of the Catalog API functions, even if the application passes a value.

    When this option is cleared and a value is passed for the Catalog Name parameter instead of NULL – ODBC Driver for Teradata returns an error because catalogs are not supported by Teradata Database.

    Disable Prepare for SQL with comments or parenthesis

    Default = Cleared

    Allows Prepare to be executed for SQL request statements enclosed in parentheses or preceded by comments.

    Using this feature is not recommended, because it is deprecated.

    Enable Read Ahead

    Default = Selected

    When this option is selected - 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.

    When this option is cleared - the ODBC Driver only requests the next response message from the database when the current response message has been processed by the driver.

    Retry system calls (EINTR)

    Default = Selected

    When this option is selected – the ODBC Driver is responsible for retrying the socket system calls when they have been interrupted by some event such as a SIGALRM.

    When this option is cleared – the ODBC Driver returns an SQL_ERROR to the ODBC Application. The ODBC Application is responsible for recovery from the interrupted socket system calls.

    Custom options group box

    Use DATE data for TIMESTAMP parameters

    Default = Cleared

    When this option is selected– ODBC Driver for Teradata is directed to send DATE data for parameters bound as SQL_C_TIMESTAMP and SQL_TIMESTAMP.

    This option should not be enabled for applications that are not using Microsoft Access Jet databases.

    This option should only be selected for this circumstance, as this results in truncation of SQL_C_TIMESTAMP data to contain only the DATE portion.

    Enable Custom Catalog Mode for 2.x Applications

    Default = Cleared

    Provides backwards compatibility for ODBC 2.x applications that have taken advantage of a defect in the ODBC Driver where the functionality of the Catalog APIs are noncompliant with the ODBC Programmers Reference specification.

    The behavior when a NULL value is passed to the SQLTables API for the SchemaName argument results in a search for tables belonging to the userid, DBC, and default database schema names, rather than a % search pattern as noted in the ODBC Programmers Reference Specification.

    Return Empty string in CREATE_PARAMS column for SQL_TIMESTAMP

    Default = Cleared

    Returns an empty string for the CREATE_PARAMS column of SQLGetTypeInfo for SQL_TIMESTAMP data type, and disallows MC-ACCESS from using any TIMESTAMP precision value in Create Table text.

    Return max. CHAR/VARCHAR length as 32K

    Default = Cleared

    Returns a value of 32000 (in general, could be 64000 also) for COLUMN_SIZE column of SQLGetTypeInfo for SQL_CHAR and SQL_VARCHAR data types. This allows MS-ACCESS to handle column size value returned by ODBC Driver for Teradata without any numeric overflow.

    User DSNs will be saved in the ~/Library/ODBC/odbc.ini file.

    System DSNs will be saved in the /Library/ODBC/odbc.ini file.

    Note: To be able to save a DSN, the user need write permission to the respective odbc.ini file.