Defining an ODBC Data Source - SQL Assistant

Defining an ODBC Data Source

An ODBC-based application like Teradata SQL Assistant accesses the data in a database through an ODBC data source.

After installing Teradata SQL Assistant on a workstation, start Teradata SQL Assistant. Then define a data source for each database.

The Microsoft ODBC Data Source Administrator maintains ODBC data sources and drivers and can be used to add, modify, or remove ODBC drivers and configure data sources. An About Box for each installed ODBC driver provides author, version number, module size, and release date.

Use the Microsoft ODBC Data Source Administrator program installed on the workstation to define a data source in one of two ways.

1 Do one of the following:

From the Windows desktop, select Start > Control Panel > Administrative Tools > Data Sources (ODBC).

From within SQL Assistant select ODBC in the dropdown list to the left of the toolbar and then select Tools > Define Data Source.

2 In the ODBC Data Source Administrator dialog box, decide what type of data source to create.

Table 1 describes the data source types.

Table 1: ODBC Data Source Types 

Data Source Description	Explanation
User DSN	An ODBC user data source stores information about how to connect to the indicated data provider.
System DSN	An ODBC system data source stores information about how to connect to the indicated data provider. A system data source is visible to all users on this machine, including NT services.
File DSN	An ODBC file data source connects to a data provider. File DSNs can be shared by users who have the same drivers installed. Note: File DSN is not recommended.

3 After selecting the DSN type, click Add.

For example, in the ODBC Data Source Administrator dialog box, click the System DSN tab to bring to the front. Click Add, then click OK.

4 In the Create New Data Source dialog box, select the appropriate driver and click Finish.

For example, locate the Teradata driver under the column Name, click to select the driver, and click Finish.

A dialog box appears for the selected database. This dialog box requests information that defines the location of the database and the connection parameters to be used when establishing a connection. The parameters vary from one vendor database to another. Table 2 describes the basic parameters used to connect to a database.

Table 2: ODBC Driver Setup for Teradata Database Dialog Box: Field Descriptions 

Select This Field...	To...
Name	Enter a name that identifies this data source. For example, in some cases there is more than one Teradata server to connect to or a user might have more than one logon depending on the function the user performs.
Description	Enter a description. This is solely a comment field to describe the data source name used.
Name(s) or IP address(es)	Enter the name(s) or IP address(es) of each LAN-connected node in the system, one per line. Entering only the first node name or IP address causes the client to communicate only with that node or IP address and can decrease system performance significantly. Define any names entered here in either Domain Name Services (DNS) or the local hosts file. The hosts file is located in the system32\drivers\etc subdirectory of the directory in which Windows is installed. Enter the name(s) or IP address(es) of the Teradata system. Note: Never enter both a name and an IP address.
Do not resolve alias name to IP address	When this option is selected, setup routine does not attempt to resolve alias names entered into the “Name(s) and IP address(es)” box at setup time. Instead it is resolved at connect time. When cleared, the setup routine automatically appends COPn (where n = 1, 2, 3, ..., 128) for each alias name entered. This causes other IP addresses associated with this server to be located, until a break in the sequence is detected.
Use Integrated Security	Select this option if logging on using integrated security measures.
Mechanism	Leave this field blank to use the default mechanism.
Parameter	The authentication parameter is a password required for the selected mechanism.
Username	Enter a user name.
Password	Enter a password to be used for the connection if using Teradata SQL Assistant in an unattended (batch) mode. Entering a password here is not secure.
Default Database (optional)	Enter the default database name. If the Default Database is not entered, the Username is used as the default. All tables, views, and macros are assumed to be in this default database unless explicitly prefixed by a database name in the query.
Account String (optional)	Enter one of the accounts that the DBA assigned to the Username when it was created.
Session Character Set	Use the drop down menu to select the character set. The default is ASCII.

Teradata SQL Assistant is ready to use.

For more information about using Microsoft ODBC Data Source Administrator, refer to the ODBC Data Source Administrator Help system.

Note: The SQL Assistant option Allow use of ODBC SQL Extensions in queries no longer exists. Clear the ODBC DSN option Disable Parsing to allow the use of ODBC SQL Extensions in queries.

ODBC Driver

Before using Teradata SQL Assistant to access the data in the database, first install an ODBC driver on the PC. Each database requires a driver that is designed specifically for that database system. Obtain the appropriate driver from an database vendor or from a third-party supplier.

Compatibility

Teradata SQL Assistant is certified to run with any Level 2 compliant 32-bit ODBC driver. The product also works with Level 1 compliant drivers, but might not provide full functionality. Consult the ODBC driver documentation to determine the driver’s conformance level. Most commercially available ODBC drivers conform to Level 2.