1 Creating TimesTen Data Stores

TimesTen ODBC drivers

TimesTen includes two versions of the Data Manager ODBC driver: a production version and a debug version.

• Use the production version of the TimesTen Data Manager driver for most application development and for all deployment.

• Use the debug version of the TimesTen Data Manager driver only if you encounter problems with TimesTen itself. This version performs additional internal error checking and is slower than the production version. On UNIX, the TimesTen debug libraries are compiled with the -g option to display additional debug information.

TimesTen also includes the TimesTen Client ODBC driver for use with client/server applications.

On Windows, the production version of the TimesTen Data Manager is installed by default. To install the debug version, choose Custom setup. To install the TimesTen Client driver, choose either Typical or Custom setup. The following table lists the ODBC drivers for Windows:

On UNIX, depending on the options selected at install time, TimesTen installs the Client driver and/or both the production version and the debug version of the TimesTen Data Manager ODBC driver. The following table lists the TimesTen ODBC drivers for UNIX platforms.

TimesTen JDBC driver and driver manager

The TimesTen JDBC driver uses the ODBC driver to access the TimesTen data stores. For each JDBC method, the driver executes a set of ODBC functions to perform the appropriate operation. Since the JDBC driver depends on ODBC for all data store operations, the first step in using JDBC is to define a TimesTen data store and the ODBC driver that will access it on behalf of JDBC.

JDBC is installed with the TimesTen Data Manager. JDBC allows Java applications to issue SQL statements to TimesTen and process the results. JDBC is the primary interface for data access in the Java programming language.

The JDBC API is implemented using a driver manager that can support multiple drivers connecting to different databases. The TimesTen JDBC driver is implemented using native methods to bridge to the TimesTen native API.

The JDBC driver manager in the DriverManager class keeps track of all the JDBC drivers that have been loaded and are available to the Java application. The application may load several drivers and access each driver independently. For example, both the TimesTen JDBC Client/Server driver and the TimesTen JDBC direct driver can be loaded onto a machine. Then Java applications can access data stores either on the local machine or a remote machine.

For a list of the functions supported by TimesTen, see the Oracle TimesTen In-Memory Database Java Developer's Guide.

User and system DSNs

DSNs are resolved using a two-tiered naming system, consisting of user DSNs and system DSNs:

• A user DSN can be used only by the user who created the DSN. On Windows, user DSNs are defined from the User DSN tab of the ODBC Data Source Administrator. On UNIX, user DSNs are defined in the file $HOME/.odbc.ini or in a file named by the ODBCINI environment variable. This file is referred to as the "user ODBC.INI file." Although a user DSN is private to the user who created it, it is only the DSN, consisting of the character-string name and its attributes, that is private. The underlying data store can be referenced by other users' user DSNs or by system DSNs.

• A system DSN can be used by any user on the machine on which the system DSN is defined. On Windows, system DSNs are defined from the System DSN tab of the ODBC Data Source Administrator. On UNIX, system DSNs are defined in the file install_dir/info/sys.odbc.ini. This file is referred to as the "system ODBC.INI file."

When looking for a specific DSN, TimesTen first looks for a user DSN with the specified name. If no matching user DSN is found, TimesTen looks for a system DSN with the specified name. If a user DSN and a system DSN with the same name exist, TimesTen uses the user DSN. On UNIX, if there are multiple DSNs with the same name in the same ODBC.INI file, TimesTen uses the first one in the file.

Data Manager and Client DSNs

DSNs that use the TimesTen Data Manager in either the production version or the debug version are called "Data Manager DSNs." DSNs that use the TimesTen Client are called "Client DSNs."

Data Manager DSNs define what is referred to as a direct driver connection to the data store. A Data Manager DSN refers to a data store using a path name. A data store path name is a path name that specifies the location of the data store, for example: C:\data\chns\AdminDS or /home/chns/AdminDS. This name is not a file name. The actual files used by the data store have file suffixes, for example: C:\data\chns\AdminDS.ds0 or /home/chns/AdminDS.log2. A Data Manager DSN that refers to a given TimesTen data store must be defined on the same system on which the data store resides. In addition, TimesTen creates dsName.resn files for each data store. These files are used internally by TimesTen for maintaining logs.

A Client DSN refers to a TimesTen data store indirectly by specifying a hostname, DSN pair, where the hostname represents a machine on which TimesTen Server Daemon is running and the DSN refers to a system DSN that is defined on that host. We refer to this host as the "server machine" and the DSN as a "Server DSN."

On UNIX, all user DSNs including both Client DSNs and Data Manager DSNs that are created by a specific user are defined in the same user ODBC.INI file. Similarly, all system DSNs are defined in the same system ODBC.INI file.

The following table indicates the types of DSN supported by TimesTen, whether to create a user or system DSN and the location of the DSN.

The remainder of this chapter describes Data Manager DSNs and the connection attributes that can be defined for them. For more information about Client DSNs and Server DSNs, see "Working with the TimesTen Client and Server".

Connection attributes for Data Manager DSNs

There are four types of TimesTen Data Manager attributes:

• Data store attributes are associated with a data store when it is created and cannot be modified by subsequent connections.

• First connection attributes are set when the TimesTen database is loaded into memory. Only the instance administrator can load a database with first connection attribute settings. By default, TimesTen loads an idle database, which is a database with no connections, into memory when a first connection is made to it. These attributes persist for all subsequent connections until the last connection to the data store is closed.

• General connection attributes are set by each connection and persist for the duration of the connection. Different concurrent connections may use different values.

• Cache Connect attributes allow you to enter the Oracle Service Identifier for the Oracle instance from which data will be loaded into TimesTen.

For a complete description of each attribute, see "Data Store Attributes" in the Oracle TimesTen In-Memory Database Reference.

On Windows, you specify data store attributes in the ODBC Data Source Administrator.

On UNIX, you specify data store attributes in the ODBC.INI file. Attributes that do not appear in the ODBC.INI file assume their default value.

Thread programming with TimesTen

TimesTen supports multithreaded application access to data stores. When a connection is made to a data store, any thread may issue operations on the connection.

Typically, a thread issues operations on its own connection and therefore in a separate transaction from all other threads. In environments where threads are created and destroyed rapidly, better performance may be obtained by maintaining a pool of connections. Threads can allocate connections from this pool on demand to avoid the connect and disconnect overhead.

TimesTen allows multiple threads to issue requests on the same connection and therefore the same transaction. These requests are serialized by TimesTen, although the application may require additional serialization of its own.

TimesTen also allows a thread to issue requests against multiple connections, managing activities in several separate and concurrent transactions on the same or different data stores.

Specify the ODBC driver

Specify the ODBC driver in the ODBC Data Source Administrator.

1. On the Windows Desktop, choose Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). This opens the ODBC Data Source Administrator.

2. Choose User DSN if you want to create a user DSN or System DSN if you want to create a system DSN. For a description of user and system DSNs, see "User and system DSNs".

3. Do one of the following:

   • Select an existing data source and click Configure.

   • Click Add, choose the appropriate TimesTen driver from the list. Click Finish. This displays the TimesTen ODBC Setup dialog.

For a list of TimesTen ODBC drivers, see "TimesTen ODBC drivers".

Specify the DSN

On the Data Store tab of the TimesTen ODBC Setup dialog, specify a data source name, a data store path name, and a database character set. The Data Store Path Name cannot reference a mapped drive. See Figure 1-1.

Figure 1-1 Data store

Description of "Figure 1-1 Data store"

For an explanation of DSNs and data store path names, see "Data source names". For an explanation of database character sets, see "Choosing a database character set". The description field is optional.

Specify the connection attributes

Indicate the desired connection attributes under the First Connection, General Connection, and NLS Connection tabs of the TimesTen ODBC Setup dialog. See Figure 1-2, Figure 1-3,and Figure 1-4. If you are using the Cache Connect for Oracle feature, specify the connection attributes shown in Figure 1-5. If you are using a multithreaded client/server configuration, specify the connection attributes shown in Figure 1-6.

For a description of the connection attributes, see "Data Store Attributes" in Oracle TimesTen In-Memory Database Reference.

Figure 1-2 First Connection Attributes

Description of "Figure 1-2 First Connection Attributes"

Figure 1-3 General Connection Attributes

Description of "Figure 1-3 General Connection Attributes"

Figure 1-4 NLS Connection Attributes

Description of "Figure 1-4 NLS Connection Attributes"

Figure 1-5 IMDB Cache Attributes

Description of "Figure 1-5 IMDB Cache Attributes"

Figure 1-6 Server Attributes

Description of "Figure 1-6 Server Attributes"

Click OK when you are finished.

Create a user ODBC.INI file

On UNIX, user DSNs are defined in the file $HOME/.odbc.ini or in a file named by the ODBCINI environment variable. This file is referred to as the "user ODBC.INI file." System DSNs are defined in the install_dir/info/sys.odbc.ini file. This file is referred to as the "system ODBC.INI file."

The syntax for user and system ODBC.INI files are the same. The system ODBC.INI file is created when TimesTen is installed on the machine. Users must create their own user ODBC.INI file.

Specify the DSN

Specify the data source name in the ODBC.INI file. The DSN appears inside square brackets at the top of the DSN definition on a line by itself. For example:

[AdminDS]

Specify the ODBC driver

To set the TimesTen driver, specify the DRIVER attribute in the ODBC.INI file. For example:

[AdminDS]
DRIVER=install_dir/lib/libtten.so

For a list of TimesTen ODBC drivers, see "TimesTen ODBC drivers".

Specify the data store path name

Specify the data store path name in the ODBC.INI file. For example:

DataStore=/users/robin/FixedDs

where FixedDs is the prefix for data store files. For more information, see "Data source names".

DatabaseCharacterSet=US7ASCII

Setting up a temporary data store

This example illustrates how to set up a temporary data store.

On Windows, you can use the settings in the TimesTen ODBC Setup dialog to set up a temporary data store. See Figure 1-7 and Figure 1-8.

Figure 1-7 Data Store

Description of "Figure 1-7 Data Store"

Figure 1-8 First Connection Attributes

Description of "Figure 1-8 First Connection Attributes"

To set up a temporary data store on UNIX, create the following entries in your ODBC.INI file. For a list of drivers for all UNIX platforms, see the table in "TimesTen ODBC drivers".

The text in square brackets is the data source name.

[TempDs]
Driver=install_dir/lib/libtten.so
DataStore=/users/robin/TempDs
#this is a temporary data store
Temporary=1
#create data store if it is not found
AutoCreate=1
#log data store updates to disk
Logging=1
LogPurge=1
DatabaseCharacterSet=US7ASCII

Specifying PL/SQL connection attributes in a DSN

You can specify values for PL/SQL general connection attributes. For a complete list of PL/SQL connection attributes, see "Data Store Attributes" in the Oracle TimesTen In-Memory Database Reference.

The following are a few of the PL/SQL connection attributes:

• PLSCOPE_SETTINGS - Controls whether the PL/SQL compiler generates cross-reference information.

• PLSQL_OPTIMIZE_LEVEL - Sets the optimization level that is used to compile PL/SQL library units.

• PLSQL_MEMORY_ADDRESS - Specifies the virtual address, as a hexadecimal value, at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. This memory address must be identical in all connections to your database and in all processes that connect to your database.

• PLSQL_MEMORY_SIZE - Determines the size, in megabytes, of the PL/SQL shared memory segment.

This example creates the PLdsn DSN, enables PL/SQL and sets the PL/SQL shared memory segment size to 32 MB. enables cross references.

[PLdsn]
Datastore=/users/user1/PLdsn
PermSize=32
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
PLSQL=1
PLSQL_MEMORY_SIZE=32

For more information about these attributes, see "Data Store Attributes" in the Oracle TimesTen In-Memory Database Reference for information about PL/SQL connection attributes. For more examples, see "PL/SQL connection attributes" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

Creating multiple DSNs to a single data store

You can create two or more DSNs that refer to the same data store but have different connection attributes.

This example creates two DSNs, AdminDSN and GlobalDSN. The DSNs are identical except for their connection character sets. Applications that use the US7ASCII character set can connect to the TTDS data store by using AdminDSN. Applications that use multibyte characters can connect to the TTDS data store by using GlobalDSN.

For Windows, use the ODBC Data Source Administrator to define AdminDSN as shown in Figure 1-9. AdminDSN is created with the AL32UTF8 database character set. Figure 1-10 shows that US7ASCII is the connection character set for AdminDSN.

Figure 1-9 Creating AdminDSN using TTDS data store

Description of "Figure 1-9 Creating AdminDSN using TTDS data store"

Figure 1-10 Setting the connection character set for AdminDSN

Description of "Figure 1-10 Setting the connection character set for AdminDSN"

GlobalDSN is also created with the AL32UTF8 database character set, as shown in Figure 1-11. Figure 1-12 shows that the connection character set for GlobalDSN is AL32UTF8.

Figure 1-11 Creating GlobalDSN using TTDS data store

Description of "Figure 1-11 Creating GlobalDSN using TTDS data store"

Figure 1-12 Setting the connection character set for GlobalDSN

Description of "Figure 1-12 Setting the connection character set for GlobalDSN"

The next example shows how to specify the DSNs on UNIX. It uses the TimesTen Data Manager ODBC driver for Solaris.

The text in square brackets is the data source name.

[AdminDSN]
Driver=install_dir/lib/libtten.so
Datastore=/data/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=US7ASCII

[GlobalDSN]
Driver=install_dir/lib/libtten.so
DataStore=/data/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

Connecting to a data store without using a DSN

Using the ttIsql utility, you can connect to a data store without a predefined Data Source Name by specifying:

• The name or path name of driver using the Driver attribute, and

• The data store path and filename prefix using the DataStore attribute

On Microsoft Windows systems, the value of the Driver attribute should be the name of the TimesTen ODBC Driver. For example, the value can be TimesTen Data Manager 11.2.1.

On UNIX systems, the value of the Driver attribute should be the pathname of the TimesTen ODBC Driver shared library file. The file resides in the install_dir/lib directory.

C:\ ttIsql
ttIsql <c> 1996-2009, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
Command> connect "Driver=TimesTen Data Managers 11.2.1; DataStore=C:\sales\admin";

Temporary and permanent memory

TimesTen manages data store space using two separate memory partitions within a single contiguous memory space. One partition contains permanent data and the other contains temporary data.

• Permanent data includes the tables and indexes that make up a TimesTen data store. When a data store is loaded into memory, the contents of the permanent data partition are read from files stored on disk. The permanent data partition is written to disk during checkpoint operations.

• Temporary data includes locks, cursors, compiled commands, and other structures needed for command execution and query evaluation. The temporary data partition is created when a data store is loaded into memory and is destroyed when it is unloaded.

The connection attributes that control the size of the data store when it is in memory are PermSize and TempSize. The PermSize attribute specifies the size of the permanent data partition and the TempSize attribute specifies the size of the temporary data partition.

See "Data Store Attributes" in the Oracle TimesTen In-Memory Database Reference for further description of these attributes.

Changing data store size

The sizes of the permanent and temporary data partitions are set when a data store is loaded into memory and cannot be changed while the data store is in memory. To change the size of either partition, you must unload the data store from memory and then reconnect using different values for the PermSize or TempSize attributes.

Modifying the size and unloading the data store is described in the following sections:

• Estimating and modifying the data partition sizes for the data store

• Unloading the data store from memory

• Monitoring PermSize and TempSize attributes

PermSize + TempSize + LogBufMB + 12MB overhead

Receiving out-of-memory warnings

TimesTen provides two general connection attributes that determine when a low memory warning should be issued: PermWarnThreshold and TempWarnThreshold. Both attributes take a percentage value.

To receive out-of memory warnings, applications must call the built-in procedure ttWarnOnLowMemory.

These attributes also set the threshold for SNMP warning. See "Diagnostics through SNMP Traps" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

Backing up and restoring a data store

TimesTen's backup and restore facility allows you to create backups of TimesTen data stores and restore the data store at a later time. The primary use for the backup and restore facility is to allow the restoration of a recent state of a data store that has been lost.

Every data store backup contains the information needed to restore the data store as it existed at a the backup point; the time the backup began. Restoration of a data store from a given backup restores the modifications of all transactions that committed before the backup point.

TimesTen supports both full and incremental backups. An incremental backup moves the backup point of an existing backup forward in time by augmenting the backup with all of the log records created since its backup point.

TimesTen writes a data store backup to a location specified by a backup path, which consists of a directory name and an optional basename. You must specify the backup directory and basename when the backup is created. The basename defaults to the basename of the data store itself if you do not specify a basename.

TimesTen also allows stream backups. A stream backup writes the data store backup file to stdout.

A set of files containing backup information for a given data store, residing at a given backup path is referred to as a backup instance. A given backup instance must be explicitly enabled for incremental backups.

An incremental backup can only augment an existing incremental-enabled backup of the same data store. Restoring a data store from a backup causes all existing incremental-enabled backups of this data store to become incremental backups to become incremental-disabled.

TimesTen supports the creation of up to eight incremental-enabled backup instances for each data store. If you attempt to start an incremental backup in a ninth backup path, TimesTen returns an error. Incremental backups are supported only for permanent disk-logging data stores.

Information about incremental backups is not retained across data store recovery or restoration. Existing backup instances continue to be usable for restoring the data store, but incremental backups to those instances will have to be reenabled.

A backup operation is atomic: If it completes successfully, it will produce a backup that can be used to restore a data store to the state of its backup point. If it fails for any reason, it leaves the files of any existing backup intact and its backup point unchanged.

The files of the existing backup may be modified by a failed full or incremental backup, but not in a way that compromises the ability to restore from them.

An incremental backup typically completes much faster than a full backup, as it has less data to copy. The performance gain of incremental backups over full backups comes at the cost of increased disk usage and longer restoration times. Use incremental backups in concert with full backups in order to achieve a balance between backup time, disk usage, and restoration time.

The backup types supported by TimesTen are:

For details about using TimesTen's backup and restore facility, see "ttBackup" and "ttRestore" in Oracle TimesTen In-Memory Database Reference.

The user ODBC.INI file

On UNIX, user DSNs are defined in the file $HOME/.odbc.ini or in a file named by the ODBCINI environment variable. This file is referred to as the "user ODBC.INI file." Although a user DSN is private to the user who created it, it is only the DSN, containing the character-string name and its attributes, that is private. The underlying data store can be referenced by other user DSNs or by system DSNs.

TimesTen supports data sources for the TimesTen Data Manager and data sources for the TimesTen Client in the .odbc.ini file.

For information on how to create a copy of the .odbc.ini file in your home directory and how to override the name and location of the .odbc.ini file, see "Data source names".

The system ODBC.INI file

On UNIX, system DSNs are defined in the install_dir/info/sys.odbc.ini file. This file is referred to as the "system ODBC.INI file." A system DSN can be used any connection within the TimesTen installation.

Searching for a DSN

See "Searching for a DSN" for the rules of precedence that TimesTen follows when searching for a DSN.

ODBC Data Sources

Each entry in the optional ODBC Data Sources section lists a data source and a description of the driver it uses. The data source section has the following format:

[ODBC Data Sources]
data-source-name=driver-description

The data-source-name is required. It identifies the data source to which the driver connects. You choose this name.

The driver-description is required. It describes the driver that connects to the data source.

Data Source specification

Each data source listed in the ODBC Data Sources section has its own data source specification. The data store specification for TimesTen Data Manager data stores has the format shown in Table 1-1.

For example, the data source sampledb_1121 could have a data source specification that includes the following:

[sampledb_1121]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/sampledb_1121
...

The data store specification for TimesTen Client configurations has the format shown in Table 1-2.

For example, the client/server data source sampledbCS_1121 that connects to sampledb_1121 on the TimesTen Server ttserver could have a data source specification that includes the following:

[sampledbCS_1121]
TTC_Server=ttserver
TTC_SERVER_DSN=sampledb_1121
TTC_Timeout=30

ODBC.INI file example

The following example shows portions of a UNIX .odbc.ini file:

...
[ODBC Data Sources]
sampledb_1121=TimesTen 11.2.1 Driver
...
 
[sampledb_1121]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/sampledb_1121
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=US7ASCII
...
 
########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 11.2.1 Client.
# The Server Name is set in the TTC_SERVER attribute.
# The Server DSN is set in the TTC_SERVER_DSN attribute.
#########################################################################
 
[ODBC Data Sources]
sampledbCS_1121=TimesTen 11.2.1 Client Driver
...
 
[sampledbCS_1121]
TTC_SERVER=localhost
TTC_SERVER_DSN=sampledb_1121
...