1. Cache Guide
  2. Setting Up a Caching Infrastructure
  3. Configuring a TimesTen Database to Cache Oracle Database Data
  4. Specify Database Connection Definition for Cache
  5. Define a DSN for the TimesTen Classic Database

Define a DSN for the TimesTen Classic Database

A TimesTen database that caches data from an Oracle database can be referenced by either a system DSN or a user DSN. A TimesTen database is implicitly created the first time the instance administrator user connects to it using a DSN. When creating a DSN for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the connection attributes.

See Managing TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.

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 cache1 and cache1cs 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).

  • 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 DSN defines client-server access for the local cache1 server DSN.

All of these connection attributes can be set in a direct DSN or a connection string, unless otherwise stated.

  • 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';

  • 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 that was set in the tnsnames.ora file.

    For Microsoft Windows systems, the net service name of the Oracle database instance must be specified in the Oracle Net Service Name field of the TimesTen Cache tab within the TimesTen ODBC Setup dialog box.

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

  • UID specifies the name of the TimesTen cache administration user. The UID connection attribute can be specified in a direct DSN, a client DSN, or a connection string.

  • PwdWallet specifies the wallet in which credentials are stored for users. You can provide the TimesTen user name and password within a wallet. You can also provide the cache administrator users and respective passwords in a wallet. The cache administration user credentials are necessary when performing cache operations and connecting to the Oracle database.

  • If you are not using PwdWallet to specify a wallet, then use PWD to specify the password of the TimesTen cache administration user specified in the UID connection attribute. The PWD connection attribute can be specified in a Direct DSN, a Client DSN, or a connection string.

  • If you are not using PwdWallet to specify a wallet, then use OraclePWD to specify the password of the Oracle Database cache administration user that has the same name as the TimesTen cache administration user specified in the UID connection attribute.

    Note:

    See Create the TimesTen Users.

  • PassThrough can be set to control whether statements are to be run in the TimesTen database or passed through to be processed in the Oracle database. See Setting a Passthrough Level.

  • LockLevel must be set to its default of 0 (row-level locking) because cache does not support database-level locking.

  • ReplicationApplyOrdering and CacheAWTParallelism control parallel propagation of changes to TimesTen cache tables in an AWT cache group to the corresponding Oracle Database tables. See Improving AWT Throughput with Parallel Propagation to the Oracle Database.

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. The cache1 DSN specifies a TimesTen database that caches data from an Oracle database. 

[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