Skip Headers
Oracle® TimesTen In-Memory Database Operations Guide
Release 11.2.1

Part Number E13065-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Managing TimesTen Databases

A TimesTen database is a collection of elements such as tables, views, and sequences that can be accessed and manipulated through SQL. Each TimesTen database is created when the first connection initiates. The database instance is freed only when all existing connections have been disconnected. The configuration for the TimesTen database is contained within the first connection attributes.

Thus, this chapter describes first how to configure for a connection to the TimesTen database, because the configuration and management for your TimesTen database is contained in attributes within the connection definition.

Once you have created a database, you can perform the following:

The main topics are as follows:

Connecting to TimesTen with ODBC and JDBC drivers

As described in "TimesTen connection options" in the Oracle In-Memory Database Cache Introduction, applications use the TimesTen ODBC driver to access a TimesTen database. The application can use the ODBC direct driver, the Windows ODBC driver manager, the ODBC client driver or the ODBC driver indirectly through a provided interface to access the TimesTen database.

Figure 1-1 shows how the application can use different drivers and interfaces to access the TimesTen database.

Figure 1-1 Application access to TimesTen database diagram

Usage of ODBC driver
Description of "Figure 1-1 Application access to TimesTen database diagram"

Consider the following points:

For more information on how to compile an application that uses the TimesTen driver manager, see Oracle TimesTen In-Memory Database C Developer's Guide, Oracle TimesTen In-Memory Database Java Developer's Guide and Oracle TimesTen In-Memory Database TTClasses Guide.

The following sections describe how to define TimesTen databases:

Connecting using TimesTen ODBC drivers

TimesTen includes the following ODBC drivers:

  • TimesTen Data Manager driver: A TimesTen ODBC driver for use with direct connect applications.

  • TimesTen Client driver: A TimesTen Client ODBC driver for use with client/server applications.

TimesTen includes the following two versions of the Data Manager ODBC driver:

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

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

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.

Table 1-1 lists the ODBC drivers for Windows:

Table 1-1 ODBC drivers provided for Windows platforms

Platform Version Name

Windows

Production

TimesTen Data Manager 11.2.1 Driver.

Windows

Debug

TimesTen Data Manager 11.2.1 Debug Driver.

Windows

Client

TimesTen Client 11.2.1 Driver


On UNIX, depending on the options selected at install time, TimesTen may install the Client driver and both the production version and the debug version of the TimesTen Data Manager ODBC driver.

Table 1-2 lists the TimesTen ODBC drivers for UNIX platforms.

Table 1-2 ODBC drivers provided for UNIX platforms

Platform Version Location and name

HP-UX

Production

install_dir/lib/libtten.sl

TimesTen Data Manager 11.2.1 Driver.

HP-UX

Debug

install_dir/lib/libttenD.sl

TimesTen Data Manager 11.2.1 Debug Driver.

HP-UX

Client

install_dir/lib/libttclient.sl

TimesTen Client 11.2.1 Driver.

Solaris

Linux

Production

install_dir/lib/libtten.so

TimesTen Data Manager 11.2.1 Driver.

Solaris

Linux

Debug

install_dir/lib/libttenD.so

TimesTen Data Manager 11.2.1 Debug Driver.

Solaris

Linux

Client

install_dir/lib/libttclient.so

TimesTen Client 11.2.1 Driver.

AIX

Production

install_dir/lib/libtten.a

TimesTen Data Manager 11.2.1 Driver.

AIX

Debug

install_dir/lib/libttenD.a

TimesTen Data Manager 11.2.1 Debug Driver.

AIX

Client

install_dir/lib/libttclient.a

TimesTen Client 11.2.1 Driver.


Connecting using the TimesTen JDBC driver and driver manager

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

As shown in Figure 1-1, the TimesTen JDBC driver uses the ODBC driver to access TimesTen databases. 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 database operations, the first step in using JDBC is to define a TimesTen database and the ODBC driver that will access it on behalf of JDBC.

The TimesTen JDBC API is implemented using native methods to bridge to the TimesTen native API and provides a driver manager that can support multiple drivers connecting to separate databases. The JDBC driver manager in the DriverManager class keeps track of all 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 Client JDBC driver and the TimesTen direct driver can be loaded by an application. Then, Java applications can access databases either on the local machine or a remote machine.

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

Specifying Data Source Names to identify TimesTen databases

When you connect from an application, you use a Data Source Name (DSN) to uniquely identify the particular TimesTen database to which you want to connect. Specifically, a DSN is a character-string name that identifies a TimesTen database and a collection of connection attributes that are to be used when connecting to the database. On Windows, the DSN also specifies the ODBC driver to be used to access the database.

Note:

If a user tries to use a DSN that has connection attributes for which they do not have privileges, such as first connection attributes, they receive an error. For more information on first connection attribute privileges, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.

Even though the DSN uniquely identifies a TimesTen database, a database can be referenced by multiple DSNs. The difference between each of these unique DSNs is in the specification of the connection attributes to the database. This provides convenient names to different connection configurations for a single database.

Note:

According to the ODBC standard, when an attribute occurs multiple times in a connection string, the first value specified is used, not the last value.

A DSN has the following characteristics:

The following sections describe how to configure and manage your DSNs:

Overview of user and system DSNs

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

  • 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 database can be referenced by other users' user DSNs or by system DSNs.

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

  • A system DSN can be used by any user on the machine on which the system DSN is defined to connect to the TimesTen database.

    • 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 sys.odbc.ini file, which is referred to as the system ODBC.INI file.

      • In a non-root installation, the file is located in install_dir/info/sys.odbc.ini.

      • In a root installation, the file is located in /var/TimesTen/InstanceName/sys.odbc.ini or /var/TimesTen/sys.odbc.ini.

Defining DSNs for direct or client/server connections

DSNs are created to uniquely identify a database, whether local or remote. The following explains the type of DSN to use for either a direct or client/server connection:

  • Data Manager DSN: A DSN that specifies a local database uses the TimesTen Data Manager ODBC driver, which is the direct driver. You can use either the production version or the debug version of the TimesTen Data Manager driver.

    A Data Manager DSN refers to a database using a path name and a filename prefix. The database path name specifies the directory location of the database and the prefix for the database, such as C:\data\chns\AdminDS or /home/chns/AdminDS.

    Note:

    This path name and prefix does not define a file name, but the name of the directory where the database is located and the prefix for all database files. The actual files used by the database append file suffixes, such as C:\data\chns\AdminDS.ds0 or /home/chns/AdminDS.log2.

    A Data Manager DSN that refers to a given TimesTen database must be defined on the same system on which the database resides. TimesTen creates dsName.resn files for each database. These files are used internally by TimesTen for maintaining logs.

    If multiple Data Manager DSNs refer to the same database, they must all use exactly the same database path name, even if some other path name identifies the same location. For example, you cannot use a symbolic link to refer to the database in one DSN and the actual path name in another DSN. On Windows, you cannot use a mapped drive letter in the database path name.

  • Client DSN: A Client DSN specifies a remote database and uses the TimesTen Client. A Client DSN refers to a TimesTen database indirectly by specifying a hostname, DSN pair, where the hostname represents the server machine on which TimesTen Server is running and the DSN refers to a Server DSN that specifies the TimesTen database on the server host.

  • Server DSN: A Server DSN is always defined as a system DSN and is defined on the server system for each database on that server that will be accessed by client applications. The format and attributes of a server DSN are very similar to those of a Data Manager 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.

DSN type User or System DSN? Location of DSN
Data Manager DSN Can be a user or system DSN Located on the machine where the database resides.
Client DSN Can be a user or system DSN Located on any local or remote machine.
Server DSN Must be a system DSN Located on the machine where the database resides.

For more information about Client DSNs and Server DSNs, see "Working with the TimesTen Client and Server".

Connection attributes for Data Manager DSNs or Server DSNs

There are four types of TimesTen Data Manager DSN or Server DSN attributes:

Note:

For a complete description of all attributes, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.
  • Data Store attributes are associated with a database when it is created and cannot be modified by subsequent connections. They can only be changed by destroying and re-creating the database.

    The following are some of the more popular data store attributes:

    • DataStore: Directory name and file name prefix of the database.

    • LogDir: Directory name of the database transaction log files. By default, the transaction log files reside in the checkpoint files directory. Placing the transaction log files and checkpoint files on different disks can improve system throughput.

    • DatabaseCharacterSet: Required character set specification that defines the storage encoding.

  • First connection attributes are used 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 database is closed. First connection attributes can be modified only when the TimesTen database is unloaded and then the instance administrator reconnects with different values for the first connection attributes.

    The following are some of the more popular first connection attributes:

    • PermSize: Configures the allocated size of the database's permanent partition. The permanent partition contains persistent database elements. Only the permanent partition is written to disk during a checkpoint operation.

    • TempSize: Configures the allocated size of the database's temporary partition. The temporary partition contains transient data generated when executing statements.

      Note:

      Your system must have sufficient main memory to accommodate the entire database. For more details on setting partition sizes, see "Specifying the size of a database".
  • General connection attributes are set by each connection and persist for the duration of the connection. Each concurrent connection can have different values.

    Note:

    If you provide connection attributes in the connection string, this overrides the connection attributes set in the DSN. See "Connecting to a database using a connection string" for details.

    The user name and password can be specified within the general connection attributes in the DSN. When the user name is specified in the connection attributes for the DSN, this defines the user name to be used in the connection, whether using a direct or client/server connection. Any user configuration in the general connection attributes or on the connection string itself has precedence over the user name provided when initiating the connection, if different.

    If you do not set the user name/password general connection attributes, the user and password defaults to the operating system user who initiates the connection.

    The first connection to the TimesTen database can ONLY be initiated by the instance administrator. In this case, the user name/password fields in the connection string must be blank and the instance administrator should be the one initiating the first connection.

    When you initiate a client/server connection, the passwords sent for the connection are all encrypted by the client/server protocol. For external users, you provide only the user name, as the password is verified by the operating system.

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

Note:

See "Working with the TimesTen Client and Server" for a description of the connection attributes that can be used with the TimesTen Client ODBC driver.

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

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

Defining a Data Manager DSN

The following sections describe how to create a Data Manager DSN on either platform:

Creating a Data Manager DSN on Windows

The following sections describe how to create a DSN on Windows:

Note:

For additional examples of setting up a Data Manager DSN, see "DSN examples".

Specify the ODBC driver

Specify the ODBC driver in the ODBC Data Source Administrator.

Note:

JDBC users need to specify the ODBC driver to be used by the JDBC driver, as described in "Connecting using the TimesTen JDBC driver and driver manager".
  1. On the Windows Desktop, choose Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). This opens the ODBC Data Source Administrator.

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

  3. Perform one of the following:

    • Select an existing TimesTen data source and click Configure.

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

Note:

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

Specify the Data Manager DSN

On the Data Store tab of the TimesTen ODBC Setup dialog, specify a data source name (DSN), a database directory path and prefix, and a database character set. The database directory path cannot reference a mapped drive. See Figure 1-2.

Figure 1-2 Data Store tab

TimesTen ODBC Setup page
Description of "Figure 1-2 Data Store tab"

For an explanation of the DSN, database path and prefix, see "Specifying Data Source Names to identify TimesTen databases". 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 as shown in Figure 1-3, Figure 1-4,and Figure 1-5. In addition, if you are using IMDB Cache for Oracle, specify the connection attributes shown in Figure 1-6. If you are using a multithreaded client/server configuration, specify the connection attributes shown in Figure 1-7.

Note:

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

Figure 1-3 First Connection Attributes

First connection attributes
Description of "Figure 1-3 First Connection Attributes"

Figure 1-4 General Connection Attributes

General connection attributes
Description of "Figure 1-4 General Connection Attributes"

Figure 1-5 NLS Connection Attributes

NLS connection attributes
Description of "Figure 1-5 NLS Connection Attributes"

Figure 1-6 IMDB Cache Attributes

Cache conect attributes
Description of "Figure 1-6 IMDB Cache Attributes"

Figure 1-7 Server Attributes

Server attributes
Description of "Figure 1-7 Server Attributes"

Figure 1-8 PL/SQL Attributes

PL/SQL connection attributes

Click OK when finished.

Creating a Data Manager DSN on UNIX

This section includes the following topics:

Note:

For examples on defining a DSN, see "DSN examples".

Create a user or system 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 system ODBC.INI file, which is located in install_dir/info/sys.odbc.ini.

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

Perform the following to create the DSN:

  1. Specify the DSN 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]
    
  2. Specify the ODBC driver.

    Note:

    JDBC users need to specify the ODBC driver to be used by the JDBC driver, as described in "Connecting using the TimesTen JDBC driver and driver manager".

    To set the TimesTen driver, specify the DRIVER attribute in the ODBC.INI file. The following example provides the TimesTen ODBC driver that this DSN is configured to use:

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

    Note:

    For a list of TimesTen ODBC drivers that you can use, see Table 1-2.
  3. Specify the database directory path and prefix in the ODBC.INI file. The following example defines /users/robin as the database directory path and FixedDs as the prefix for the database files:

    DataStore=/users/robin/FixedDs
    

    The database directory path can use environment variables, as discussed in "Using environment variables in database path names".

  4. Choose a database character set. The following example defines the database character set in the ODBC.INI file as US7ASCII:

    DatabaseCharacterSet=US7ASCII
    

    Note:

    For more information, see "Choosing a database character set".
  5. Set connection attributes in your ODBC.INI file. Attributes that do not appear in the ODBC.INI file assume their default value.

    Note:

    See "Connection Attributes" in Oracle TimesTen In-Memory Database Reference. For examples, see "DSN examples".

Using environment variables in database path names

You can use environment variables in the specification of the database path name and transaction log file path name. For example, you can specify $HOME/AdminDS for the location of the database.

Environment variables can be expressed either as $varname or $(varname). The parentheses are optional. A backslash character (\) in the database path name quotes the next character.

Note:

Environment variable expansion uses the environment of the process connecting to the database. Different processes may have different values for the same environment variables and may therefore expand the database path name differently. Environment variables can only be used in the user ODBC.INI file. They cannot be specified in the system ODBC.INI file.

Defining Client and Server DSNs

For directions on how to define Client or Server DSNs for each platform, see "Defining Server DSNs" and "Creating Client DSNs".

Resolution path for a DSN

When resolving a specific DSN, TimesTen performs the following:

Note:

  • If a user DSN and a system DSN with the same name exist, TimesTen retrieves the user DSN.

  • On UNIX, if there are multiple DSNs with the same name in the same ODBC.INI file, TimesTen retrieves the first one specified in the file.

  1. Searches for a user DSN with the specified name in the following files:

    1. The file referenced by the ODBCINI environment variable, if it is set.

    2. The .odbc.ini file in the user's home directory, if the ODBCINI environment variable is not set.

  2. If no matching user DSN is found, TimesTen looks for a system DSN with the specified name.

    1. The file referenced by the SYSODBCINI environment variable, if it is set.

    2. The sys.odbc.ini file in the daemon home directory, if the SYSODBCINI environment variable is not set.

    3. On UNIX, for a non-root installation, the file is located in install_dir/info/sys.odbc.ini. Or for a root installation, the file is located at /var/TimesTen/InstanceName/sys.odbc.ini or /var/TimesTen/sys.odbc.ini.

DSN examples

This section provides additional examples of how to set up a database:

For each example, the Windows ODBC Data Source Administrator settings are followed by the corresponding ODBC.INI entries for UNIX.

Setting up a temporary database

This example illustrates how to set up a temporary database. For information on temporary databases, see "Database overview".

On Windows, you can use the settings in the TimesTen ODBC Setup dialog to set up a temporary database. See Figure 1-9 and Figure 1-10.

Figure 1-9 Data Store tab

Data Store setup
Description of "Figure 1-9 Data Store tab"

Figure 1-10 First Connection Attributes

First connection attributes
Description of "Figure 1-10 First Connection Attributes"

To set up a temporary database on UNIX, create the following entries in your ODBC.INI file. For a list of drivers for all UNIX platforms, see the table in "Connecting using 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 database
Temporary=1
#create database if it is not found
AutoCreate=1
#log database updates to disk
LogPurge=1
DatabaseCharacterSet=US7ASCII

Specifying PL/SQL connection attributes in a DSN

You can specify values for PL/SQL general connection attributes.

Note:

For a complete list of PL/SQL connection attributes, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.

The following are some 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 by setting PLSQL to "1" and sets the PL/SQL shared memory segment size to 32 MB.

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

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 database

You can create two or more DSNs that refer to the same database 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 database by using AdminDSN. Applications that use multibyte characters can connect to the TTDS database by using GlobalDSN.

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

Figure 1-11 Creating AdminDSN using TTDS database

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

Figure 1-12 Setting the connection character set for AdminDSN

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

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

Figure 1-13 Creating GlobalDSN using TTDS database

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

Figure 1-14 Setting the connection character set for GlobalDSN

Setting the connection character set
Description of "Figure 1-14 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

ODBC.INI file entry descriptions

The following sections describe the entries in the ODBC.INI file:

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]
DSN=driver-description
  • The DSN is required and 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.

The optional Data Sources section, when present in the system DSN file on the TimesTen Server, is used during the setup of Client DSNs. All system DSNs are made available to the Client DSN setup for the ODBC Data Source Administrator on the client, which displays all available DSNs on the TimesTen Server. The user can always add a new system DSN in the ODBC Data Source Administrator. When adding DSNs to the system DSN file, you should only include those DSNs that can be advertised to clients. All system DSNs are potentially accessible through the client/server configuration, even if they are not advertised.

Data Source specification

Each DSN listed in the ODBC Data Sources section has its own DSN specification. The DSN specification for Data Manager DSN has the format shown in Table 1-3.

Table 1-3 Data Source specification format

Component Description

[DSN]

The DSN is required. It is the name of the DSN, as specified in the ODBC Data Sources section of your .odbc.ini file.

Driver=driver-path-name

The TimesTen Data Manager driver that is linked with the data source. This is relevant when using a driver manager or for the server in a client/server scenario.

DataStore=data-store-path-prefix

The directory path and prefix of the database to access. This is required.

Optional attributes

See "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for information about attributes.


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

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

The database specification for TimesTen Client DSN has the format shown in Table 1-4.

Note:

While the syntax for the TimesTen Client DSN is listed here, full directions for setting the Client DSN and Server DSN are located in "Defining Server DSNs" and "Creating Client DSNs".

Table 1-4 Database specification for TimesTen Client configurations

Component Description

[DSN]

The DSN is required. It is the same DSN specified in the ODBC Data Sources section of the .odbc.ini file.

TTC_Server=server-name

The server-name is required. It is the DNS name, host name, IP address or logical server name for the TimesTen Server.

TTC_Server_DSN=server-DSN

The server-DSN is required. It is the name of the data source to access on the TimesTen Server.

TTC_Timeout=value

Client connection timeout value in seconds.


Note:

Most TimesTen Data Manager attributes are ignored for TimesTen Client databases.

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

Connecting to a database using a connection string

TimesTen applications require a DSN or a connection string be specified to connect to a database. For modularity and maintainability, it is better to set attributes in a DSN rather than in a connection string within the application, unless a particular connection requires that specific attribute settings override the settings in the DSN or the default settings.

The syntax for a connection string contains connection attribute definitions, where each attribute is separated by a semicolon.

The following precedence rules are used to determine the settings of DSN attributes:

  1. Attribute settings specified in a connection string have the highest precedence.If an attribute appears more than once in a connection string, the first specification is used.

  2. If an attribute is not specified in the connection string, the attribute settings that are specified in the DSN are used.

  3. Default attribute settings have the lowest precedence.

You can connect to a TimesTen database without a predefined DSN with any ODBC application or the ttIsql utility as long as the connection string contains the Driver, DataStore, and DatabaseCharacterSet attributes, as follows:

The following example shows how you can connect providing the Driver, DataStore, and DatabaseCharacterSet attributes using a connection string in the ttIsql utility:

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 Manager 
11.2.1;DataStore=C:\sales\admin;DatabaseCharacterSet=US7ASCII";

Specifying a RAM policy

TimesTen allows you to specify a RAM policy that determines when databases are loaded and unloaded from main memory. To set the RAM policy, use the ttAdmin utility.

For each database you can have a different RAM policy. The policy options are:

The following sets the RAM policy to always for the database identified by the ttdata DSN:

Note:

The first line shows the RAM residence policy set to always. The rest of the output details other policies you can set with the ttAdmin utility. See "ttAdmin" in the Oracle TimesTen In-Memory Database Reference for more information.
% ttadmin –rampolicy always ttdata
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : False
Cache Agent Policy              : manual
Cache Agent Manually Started    : False

Specifying the size of a database

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

The connection attributes that control the size of the database 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.

Note:

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

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

Managing the database size and unloading the database are described in the following sections:

Estimating and modifying the data partition sizes for the database

Procedures, tables, or rows cannot be created in the database if the permanent or temporary data partition is full. In order to have the correct size for your database, set the appropriate size in the PermSize and TempSize connection attributes.

  • PermSize connection attribute: The permanent data partition can be increased in size, but it cannot be decreased.

  • TempSize connection attribute: The temporary data partition can be either increased or decreased in size for databases that do not participate in replication.

To make size estimates, use the ttSize utility or run the application until you can make a reasonable estimate.

You must make sure that you have a shared memory segment that is large enough to hold the database. In general, the minimum size of this shared memory segment should be:

PermSize + TempSize + LogBufMB + 20MB overhead

Note:

Additional shared segments may be created either for PL/SQL with the PLSQL_MEMORY_SIZE or for Client/Server with the -serverShmSize daemon option.

When you are calculating the amount of PermSize to allocate, take into account that PL/SQL procedures, functions and packages occupy space in the permanent data partition. The amount of permanent data partition required by a stored PL/SQL unit depends on the size and complexity of the unit. Small procedures can take less than 3 KBs, while larger ones can take considerably more. On average, reasonably complex units could be expected to use about 20 KBs of permanent data partition space.

For more details, see "Installation prerequisites" in Oracle TimesTen In-Memory Database Installation Guide and the descriptions of the TempSize and PermSize attributes in Oracle TimesTen In-Memory Database Reference.

Unloading the database from memory

Before you can change the size of either partition, you must first unload a database from memory, close all active connections to the database and set the RAM policy of the database to manual or inUse.

  • To unload the database from memory, use the ttStatus utility to find processes connected to the database and stop them. Once you have made the changes for database size, reload it into memory.

    However, if the database is configured for replication, stop the cache and replication agents, if they are running. Reconfigure the database sizes for all replicas of the database. Once you have made the change in database size, read it into memory and restart the cache and replication agents.

  • To set the RAM policy to manual or inUse, see "Specifying a RAM policy" for information.

Monitoring PermSize and TempSize attributes

The TimesTen table SYS.MONITOR contains several columns that can be used to monitor usage of PermSize and TempSize. These columns include PERM_ALLOCATED_SIZE, TEMP_ALLOCATED_SIZE, PERM_IN_USE_SIZE, PERM_IN_USE_HIGH_WATER, TEMP_IN_USE_SIZE, and TEMP_IN_USE_HIGH_WATER. Each of these columns show in KB units the currently allocated size of the database and the in-use size of the database. The system updates this information each time a connection is made or released and each time a transaction is committed or rolled back.

You can monitor block-level fragmentation in the database by using the ttBlockInfo built-in procedure. For more details, see "ttBlockInfo" in the Oracle TimesTen In-Memory Database Reference.

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.

Manage existing tables in the database

The following utilities enable you to manage certain aspects of existing tables in the database:

Migration, backup, and restoration of the database

The following sections describe how to manage the database:

Copying, migrating and restoring a database

The TimesTen utilities for copying, restoring and migrating a database enable you to perform the following:

  • Migrate a database between releases of TimesTen. Use the ttMigrate utility. This utility saves tables and indexes from a TimesTen database into a binary file. The tables and indexes can then be restored into another TimesTen database. This allows you to migrate data between TimesTen releases.

  • Migrate a database between different hardware platforms. Use the ttBulkCp utility to save and restore the data. The ttBulkCp utility copies data from TimesTen tables into ASCII files and back again, but only for those objects owned by the user executing the utility, and those objects for which the owner has SELECT privileges.

    The ttBulkCp utility enables you to copy a single table between databases, including between databases from different releases of TimesTen or between databases on different hardware platforms.

    The ttSchema utility saves the table definitions. Use ttIsql to re-create the tables from the saved table definitions.

  • Take a snapshot of a database and then restore the database in the exact same state. Use the ttBackup and ttRestore utilities or the ttBackup and ttRestore C functions.

Backing up and restoring a database

The TimesTen backup and restore facility allows you to create a backup of any TimesTen database to restore it at a later time. The primary use for the backup and restore facility is to allow the restoration of a recent state of a database that has been lost.

Note:

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

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

A backup operation is atomic: If it completes successfully, it will produce a backup that can be used to restore a database 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.

TimesTen writes a database 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 database itself if you do not specify a basename.

Note:

Do not manually change the contents of the backup directory. The addition, removal, or modification of any file in the backup directory, except for modifications made by ttBackup and ttRestore themselves, may compromise the integrity of the backup and restoration of the database from the backup may not be possible.

Types of backup provided

TimesTen supports both full and incremental backups. TimesTen also allows stream backups.

  • Stream: A stream backup writes the database backup file to stdout.

  • Full: A full backup saves the entire database. For full backups, you must have enough disk space available to hold both the existing backup and the new backup, until the new backup succeeds.

  • Incremental: An incremental backup augments an existing incremental-enabled backup of the same database. An incremental backup moves the backup point of an existing backup forward in time by augmenting the backup with all of the transaction log records created since its last backup point.

    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.

    Before you can perform an incremental backup, you must first enable your backup to allow for incremental backups by executing the ttBackup utility command with the -fileFullEnable or the -fileIncrOrFull options. In either case, if your backup was not previously enabled for incremental, a full file backup is performed before the backup is enabled for subsequent incremental backups. TimesTen supports the creation of up to eight incremental-enabled backup instances for each database. If you attempt to start a ninth incremental backup, TimesTen returns an error.

    If you restore a database from a backup, regardless of whether the backup was enabled or disabled for incremental, the restored database is disabled for incremental backups. Thus, if you want incremental backups, you need to once again execute the ttBackup utility command with the -fileFullEnable or the -fileIncrOrFull option to enable incremental backups.

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

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.

The total list of backup types supported by TimesTen are as follows:

Backup type File or stream Full or incremental Incremental-enabled Comment
fileFull File Full No Default
fileFullEnable File Full Yes  
fileIncremental File Incremental. Yes Fails if incremental backup not possible.
fileIncrOrFull File Either Yes Performs fileIncremental if possible; fileFullEnable otherwise.
streamFull Stream Full No  
incrementalStop None None No Takes no backup; just disables existing incremental-enabled backup.

Thread programming with TimesTen

TimesTen supports multithreaded application access to databases. When a connection is made to a database, 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 databases.