Create an Oracle TimesTen Data Store

This document will detail the steps required to create an Oracle TimesTen In-Memory Database data store.

Step One: Set up a Data Source Name

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 TTHOME/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 for 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

For this example the entry will be scratch_ttdb=TimeTen Data Manager 11.2.1 Driver.

The Data Source Specification section comes after the last entry in ODBC Data Sources section. Each data source specification starts with a tag with the format [DSN] where DSN is the data source name used in the entry in the ODBC Data Sources section.

The Data Source Specification details attributes of the data store. Oracle Timesten In-Memory Database has over 70 attributes divided into nine sections such as: Data Strore, First Connection, NLS, PL/SQL, IMDB, client and server. The Data Source Specification does not have to set values for all of the attributes as attributes not listed will use their default value. A complete list of all attributes can be found in the documentation Data Store Attributes.

At minimum the Driver, DataStore and DatabaseCharacaterSet need to be set in the Data Source Specification.

Driver-The path and file name of the TimesTen driver
DataStore-The path and name of the database files
DatabaseCharacterSet-the character set used by the database

Note if you are going to use a data store for In-Memory Database Cache (IMDB) the DatabaseCharacterSet must match the Oracle Database. You can find the character set of the Oracle Database with the following query.

SQL> select value
  2  from nls_database_parameters
  3  where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

SQL>

What follows is the Data Store Specification details used for this document. The DSN was created in the TTHOME/info/sys.odbc.ini so it is System DSN.

[scratch_ttdb]
Driver=/opt/TimesTen/tt1121/lib/libtten.so
DataStore=/opt/TimesTen/tt1121/info/db/SCRATCH_TTDB
DatabaseCharacterSet=WE8MSWIN1252

Step 2: Connect to the Data Store

The DataStore attribute is the complete path in which to store the data store files, /opt/Timesten/tt1121/info/db. This directory must exist or you will get the following error when attempting to make a connection.

[orattadmin@ora1 info]$ ttIsql

Copyright (c) 1996-2009, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=scratch_ttdb";
  830: Cannot create data store file. OS-detected error: No such file or directory
The command failed.
Command>

The files created will have the SCRATCH_TTDB in the name. It is important to note that what follows after the last / is NOT a directory but a file name specifier.

You should now be able to connect to the data store using the ttIsql utility. Note it will take a moment for the connection to complete. The data store is not loaded until the first connection is made.

[orattadmin@ora1 info]$ ttIsql

Copyright (c) 1996-2009, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

Command> connect "DSN=scratch_ttdb";
Connection successful: DSN=scratch_ttdb;UID=orattadmin;DataStore=/opt/TimesTen/tt1121/info/db/SCRATCH_TTDB;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesTen/tt1121/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)
Command>

Notice that after the connection successful message is the DSN along with the attributes we set earlier. Looking at the file system we can see that files were created in the path /opt/TimesTen/tt1121/info/db/.

Command> host ls -alh /opt/TimesTen/tt1121/info/db
total 232M
drwxr-xr-x 2 orattadmin timesten 4.0K May  6 14:03 .
drwxr-x--- 5 orattadmin timesten 4.0K May  6 14:03 ..
-rw-rw---- 1 orattadmin timesten  14M May  6 14:03 SCRATCH_TTDB.ds0
-rw-rw---- 1 orattadmin timesten  18M May  6 14:03 SCRATCH_TTDB.ds1
-rw-rw---- 1 orattadmin timesten 8.8M May  6 14:03 SCRATCH_TTDB.log0
-rw-rw---- 1 orattadmin timesten  64M May  6 14:03 SCRATCH_TTDB.res0
-rw-rw---- 1 orattadmin timesten  64M May  6 14:03 SCRATCH_TTDB.res1
-rw-rw---- 1 orattadmin timesten  64M May  6 14:03 SCRATCH_TTDB.res2
Command>

The .ds0 and .ds1 files are checkpoint files. The checkpoint files are written to at regular intervals or through application initiated checkpoint operations.

The .log0 is a log file. These files are similar to Oracle’s redo logs except they deleted when no longer needed for recovery. You can keep the logs by setting the LogPurge attribute to 0. Once changed TimesTen will rename logs no longer needed for recovery to the extension .arch.

The .res0, .res1, and ,res2 files are “backup” log files. These are pre-allocated space to be use when there is no space left on the device housing the log files. The .res files are written to when the data source cannot write the log files. Once space is available in the log directory, the data written to the .res files will be copied to the log files.