Task 2: Create a Data Source Name (DSN) for the TimesTen Database

A TimesTen database that caches data from an Oracle database can be referenced by either a system DSN or a user DSN.

The operating system user that installed and created the TimesTen instance is called the instance administrator. When this instance administrator connects using a DSN for the first time, a TimesTen database is implicitly created.

This example is going to reference the TimesTen database with a system DSN. On UNIX or Linux, the system DSN is located in the timesten_home/conf/sys.odbc.ini file. As described in Connecting to a TimesTen Database, the sys.odbc.ini file contains the DSN definitions.

This example defines two ODBC Data Source Names (DSNs).

Note:

ODBC is TimesTen’s native API, though TimesTen also provides, or supports, many other commonly used database APIs such as JDBC, Oracle Call Interface, ODP.NET, cx_Oracle (for Python) and node-oracledb (for Node.js). See Connecting to TimesTen With ODBC and JDBC drivers in the Oracle TimesTen In-Memory Database Operations Guide.

Direct connection: The cache1 DSN is a direct mode, or server DSN. It uses the TimesTen 22.1 Driver. It defines the parameters and connectivity for a database hosted by this TimesTen instance. Tools, utilities, and applications running on this host (myhost) can connect through this DSN using TimesTen’s low latency direct mode connectivity mechanism.
Client-server connection: This database is also accessible remotely using TimesTen’s client-server connectivity. The cache1cs DSN is a client DSN and uses the TimesTen 22.1 Client Driver. It defines connectivity parameters for a server DSN that tools, utilities, and applications can connect to using TimesTen’s client-server connectivity mechanism. In this example, the client DSN, cache1cs, defines client-server access for the local cache1 server DSN.

When creating a DSN for a TimesTen database that caches data from an Oracle database, the following connection attributes are important for your cache environment:

DataStore specifies the fully qualified directory path name of the database and the file name prefix. This name is not a file name. In this example, DataStore is set to /disk1/databases/database1.
PermSize specifies the allocated size of the database's permanent region in MB. The PermSize value must be smaller than the physical RAM on the machine. Set this to a value that enables you to store all of your data. The PermSize value could be from a few GB to several TB. This example sets the permanent region to 1024 MB.
TempSize indicates the total amount of memory in MB allocated to the temporary region for the database. This example sets the temporary region to 256 MB.
LogBufMB specifies the size of the internal transaction log buffer for the database. This example sets the transaction log buffer to 256 MB.
LogFileSize specifies the maximum size of transaction log files in megabytes. This example sets the maximum size of transaction log files to 256 MB.
DatabaseCharacterSet must match the Oracle database character set. In this example, the database character set is AL32UTF8.
Note:

You can determine the Oracle database character set by running the following query in SQL*Plus as any user:
```
SQL> SELECT value FROM nls_database_parameters 
       WHERE parameter='NLS_CHARACTERSET';
VALUE
---------------------------------------------------------------------------
AL32UTF8
```
ConnectionCharacterSet specifies the character encoding for the connection. Generally, you should choose a connection character set that matches your terminal settings or data source. In this example, the connection character set is AL32UTF8.
OracleNetServiceName must be set to the net service name of the Oracle database instance. This example sets this to orcl. This is the same name as was set in the tnsnames.ora file in step 1.
CacheAdminWallet=1 specifies that credentials for the Oracle cache administration user that are registered with the ttCacheUidPwdSet built-in procedure are stored in an Oracle Wallet, rather than in memory.

Then, there is an entry for the client DSN. The client DSN specifies the location of the TimesTen database with the following attributes:

The TTC_Server_DSN attribute specifies the server DSN of the intended database.
The TTC_Server attribute specifies the server (and the port number if you do not want to use the default port number) for the database.

In the sys.odbc.ini file, create a TimesTen DSN cache1 and set the following connection attributes:

[ODBC Data Sources]
cache1=TimesTen 22.1 Driver
cache1cs=TimesTen 22.1 Client Driver

[cache1]
DataStore=/disk1/databases/database1
PermSize=1024
TempSize=256
LogBufMB=256
LogFileSize=256
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
OracleNetServiceName=orcl
CacheAdminWallet=1

[cache1cs]
TTC_SERVER_DSN=CACHE1
TTC_SERVER=myhost/6625
ConnectionCharacterSet=AL32UTF8

You can use the default settings for all the other connection attributes.