This document will detail the steps to create an ODBC Client DSN to a remote Oracle TimesTen Database on Linux.
Data Source Names (DSN) can be either user or system. A User DSN is private to the user who created the DSN whereas a System DSN can be used any user on the machine in which the system DSN is defined. Keep in mind that a user DSN, the name and the attributes are private to the user who owns it, but the data store can be referenced by other User DSNs or System DSNs.
User DSNs are defined in HOME/.odbc.ini which can be overridden by setting the ODBCINI environment variable. System DSNs are defined in OTT_HOME/< instance name >/info/sys.odbc.ini. TimesTen first looks for a user DSN when resolving a name. If no matching name is found, TimesTen looks for a System DSN.
The odbc.ini file is divided into three sections: ODBC Data Sources, Data Source Specification, and the optional ODBC tracing options.
The ODBC Data Sources section is identified with [ODBC Data Sources]. The entry to be made in the ODBC Data Sources section needs to have the DSN and the driver name with the format DSN=DriverName.
Oracle TimesTen provides a Data Manager Driver and Data Client driver. The Data Manager Driver has two versions: Production and Debug. The Data Client driver is for use with client/server applications. Below is list of the Driver Name and the path and file name.
Name Driver TimesTen Data Manager 11.2.1 Driver $TTHOME/lib/libtten.so TimesTen Data Manager 11.2.1 Debug Driver $TTHOME/lib/libttenD.so TimesTen Data Client 11.2.1 Driver $TTHOME/lib/libttclient.so
In this example we will be using the TimesTen Data Client 11.2.1 Driver. Below is an example of the [ODBC Data Sources] section entry for out client DSN.
[ODBC Data Sources] odwinsample=TimesTen 11.2.1 Client Driver
Next we need to provide the Data Source Specification entry for odwinsample. The following is the format for the Data Source Specification.
[name] TTC_Server=host or logical server name TTC_Server_DSN=server dsn
[name] – The name used in the [ODBC Data Sources] section odwinsample in this example
TTC_Server- either the host name of the server hosting the TimesTen database or logical server name that is defined in ttconnect.ini
TTC_Server_DSN- Server DSN on the server pointed to by TTC_Server. A server DSN is defined in OTT_HOME/< instance name >/info/sys.odbc.ini for Unix systems and configured to use the Data Manager driver for Windows systems.
Given the format for the Data Source Specification what follows is specification used in this example.
[odwinsample] TTC_Server=ttODWIN_logical TTC_Server_DSN=sampledb_1121
In the example specification above TTC_Server is set to the logical server ttODWIN_logical. For information on defining logical servers see the posts: Configuring an Oracle TimesTen Logical Server Name on UNIX Based Systems and Configuring an Oracle TimesTen Logical Server Name on Windows. The TTC_Server_DSN is set to the Server DSN sampled_1121. This is all that is needed to connect to the remote database.
Below is the output of connecting to the Client DSN created in this document using the ttIsqlCS utility.
[orattadmin@ora1 ~]$ ttisqlcs "DSN=odwinsample;UID=odtest;PWD=password" Copyright (c) 1996-2009, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=odwinsample;UID=odtest;PWD=password"; Connection successful: DSN=odwinsample;TTC_SERVER=ttODWIN_logical;TTC_SERVER_DSN=sampledb_1121;UID=odtest;DATASTORE=C:\app\oracle\product\TimesTen\TT1121~1\DEMODA~1\sampledb_1121;DATABASECHARACTERSET=WE8MSWIN1252;CONNECTIONCHARACTERSET=US7ASCII;PERMSIZE=40;TEMPSIZE=32;TYPEMODE=0; (Default setting AutoCommit=1) Command>