1 Connecting to a TimesTen Database

Connecting using TimesTen ODBC drivers

TimesTen includes the following TimesTen 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 TimesTen Data Manager driver (direct 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 Linux and UNIX, the TimesTen debug libraries are compiled with the -g option to display additional debug information.

For TimesTen Classic support on Windows, you can install the "TimesTen Client 18.1" driver after choosing either the Typical or Custom setup.

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

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

Connecting using the TimesTen JDBC driver and driver manager

The TimesTen JDBC driver 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.

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 accesses it on behalf of JDBC.

The TimesTen JDBC API is implemented using native methods to bridge to the TimesTen native API. Java provides a driver manager that can support multiple drivers connecting to separate databases. The Java 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, an application can load both a TimesTen Client JDBC driver and an Oracle Database JDBC driver. Then, Java applications can access either the TimesTen or Oracle databases.

For a list of the Java functions supported by TimesTen, see "Support for interfaces in the java.sql package" in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Overview of user and system DSNs

DSNs are resolved using a two-tiered naming system, where TimesTen first tries to resolve the DSN within the defined user DSNs and secondly within the defined 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.

  • For TimesTen Classic on Linux and UNIX, define user DSNs in the user odbc.ini file. TimesTen locates this file by first finding if a file is specified by the ODBCINI environment variable. If not, TimesTen locates the $HOME/.odbc.ini file.

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

  • For TimesTen Scaleout, define user DSNs within the appropriate database definitions and connectables. See "Managing a database" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for full details.

  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.

• A system DSN can be used by any user on the system 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.

  • For TimesTen Classic on Linux and UNIX, system DSNs are defined in the sys.odbc.ini file, which is referred to as the system odbc.ini file.

    TimesTen locates the system DSN file in the following order:

    • The file is located if it is specified by the SYSODBCINI environment variable.

    • In an installation, the file is located in timesten_home/conf/sys.odbc.ini.

    • If not found in any of these locations, TimesTen looks on the system for the /etc/odbc.ini file.

  • For TimesTen Scaleout, create system DSNs within the appropriate database definitions and connectables. See "Managing a database" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for full details.

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 on a Linux or UNIX host. You can use either the production version or debug version of the TimesTen Data Manager driver.

  A Data Manager DSN refers to a database using a path name and a file name prefix. The database path name specifies the directory location of the database and the prefix for the database, such as /disk1/databases/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 /disk1/databases/AdminDS.ds0.

  A Data Manager DSN that refers to a given TimesTen database must be defined on the same system on which the database resides. 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.

• 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 system 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 can be accessed by client applications. The format and attributes of a Server DSN are very similar to those of a Data Manager DSN.

On Linux and 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.

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

On Linux and UNIX, you specify connection attributes in the odbc.ini file. Connection attributes that do not appear in the odbc.ini file assume their default value.

Common types of connection attributes:

• 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 the most commonly used data store attributes:

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

  • LogDir: Directory name of the database transaction log files. Placing the transaction log files and checkpoint files on different file systems can improve system performance.

  • 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 the most commonly used first connection attributes:

  • PermSize: Configures the allocated size of the database's permanent memory region. The permanent memory region contains persistent database objects. TimesTen only writes the permanent memory region to the file system during a checkpoint operation.

  • TempSize: Configures the allocated size of the database's temporary memory region. The temporary memory region 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 region sizes, see "Specifying the memory region sizes 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.

  The following are the most commonly used general connection attributes:

  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.

  • UID: Specifies the user name to be used for the connection to the database, whether using a direct or client/server connection. To connect as the instance administrator or as an external user, you do not need to specify a user name. When you do not specify a user name, TimesTen assumes that the UID is the user name identified by the operating system.

  • PWD: Specifies the password that corresponds with the specified UID. For internal users, if you do not set the PWD attribute in the odbc.ini file for the specified DSN or in the connection string, TimesTen prompts for the password. For external users, you do not provide the password as it is verified by the operating system.

    When you initiate a client/server connection, the password sent for the connection is encrypted by the client/server protocol.

  • PWDCrypt: Specifies the encrypted password that corresponds with the specified UID.

    Note:

    For more information on the UID, PWD and PWDCrypt general connection attributes, see "UID and PWD" in the Oracle TimesTen In-Memory Database Reference. See the Oracle TimesTen In-Memory Database Security Guide for full details on access control.

• TimesTen Cache attributes enable you to enter the Oracle Service Identifier for the Oracle database instance from which data is loaded into TimesTen.

Create a user or system odbc.ini file

On Linux and 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 timesten_home/conf/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 system. 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 TimesTen ODBC driver.

   Note:

   JDBC users need to specify the TimesTen 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 ODBC 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, where /disk1/timesten is the timesten_home:

   [AdminDS]
   Driver=/disk1/timesten/lib/libtten.so
   

   Note:

   For a list of TimesTen ODBC drivers that you can use, see Table 1-1.

3. Specify the database directory path and prefix in the odbc.ini file. The following example defines /disk1/databases as the database directory path and FixedDs as the prefix for the database files:

   DataStore=/disk1/databases/FixedDs
   

   Note:

   For more information, see "Specifying Data Source Names to identify TimesTen databases".

   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 AL32UTF8:

   DatabaseCharacterSet=AL32UTF8
   

   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 for TimesTen Classic".

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.

Creating multiple DSNs to a single TimesTen Classic database

You can create two or more DSNs that refer to the same TimesTen Classic 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.

The text in square brackets is the data source name.

AdminDSN is created with the AL32UTF8 database character set and US7ASCII as the connection character set. GlobalDSN is created with the AL32UTF8 database character and AL32UTF8 as the connection character set. Note that /disk1/timesten is the timesten_home.

[AdminDSN]
Driver=/disk1/timesten/lib/libtten.so
Datastore=/disk1/databases/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=US7ASCII

[GlobalDSN]
Driver=/disk1/timesten/lib/libtten.so
DataStore=/disk1/databases/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

Specifying PL/SQL connection attributes in a DSN

For TimesTen Classic, PL/SQL connection attributes exist both as first and general connection attributes.

• PLSQL_MEMORY_ADDRESS - A first connection attribute that 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 - A first connection attribute that determines the size, in megabytes, of the PL/SQL shared memory segment.

• PLSCOPE_SETTINGS - A general connection attribute that controls whether the PL/SQL compiler generates cross-reference information.

• PLSQL_OPTIMIZE_LEVEL - A general connection attribute that sets the optimization level that is used to compile PL/SQL library units.

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

[PLdsn]
Datastore=/disk1/databases/PLdsn
PermSize=128
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
PLSQL_MEMORY_SIZE=32

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

Setting up a default DSN in TimesTen Classic

Optionally, you can add a default data source definition in TimesTen Classic. At connect time, if an application specifies a DSN that is not in the odbc.ini file or if the application does not specify a DSN, the default DSN is used to configure the connection to the TimesTen Classic database.

The default data source must be named default when defined. The default DSN can be defined with the same attributes as any other data source, which are described in "DSN specification".

When connecting, TimesTen Classic uses the default DSN in any of the following scenarios:

• When you specify the DSN=default keyword-value pair in the connection string.

• When you specify an undefined value for the DSN connection attribute in the connection string.

• When you do not specify any value for the DSN connection attribute in the connection string.

However, in general, it is best to connect with a specific data source.

When using a default DSN, provide default as the DSN name when performing TimesTen utility operations that require a DSN name, such as destroying the database with the ttDestroy utility.

The following example shows the user invoking ttIsql to connect using an undefined DSN. Since there is no definition for doesNotExist in the odbc.ini file, TimesTen Classic uses the default DSN to create the database and initiate a connection to it. It also demonstrates the user invoking both the ttStatus and ttDestroy utilities with default specified as the DSN. In addition, it shows the error thrown if the user provides doesNotExist as the DSN, instead of default.

% ttIsql doesNotExist;
 
Copyright (c) 1996, 2020 Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=doesNotExist";
Connection successful:
Command> exit
Disconnecting...
Done.
% ttStatus default
TimesTen status report as of Mon Oct 22 12:27:52 2012
 
Daemon pid 13623 port 16138 instance myhost
TimesTen Server pid 13632 started on port 16140
------------------------------------------------------------------------
Data store /timesten/install/sample_db/default
There are no connections to the data store
Replication policy  : Manual
Cache Agent policy  : Manual
PL/SQL enabled.
------------------------------------------------------------------------
Accessible by group xyz
End of report
% ttDestroy doesNotExist;
Failed to destroy data store: Specified DSN is not found in user and system
odbc.ini files (or registry)
% ttDestroy default; 

The following example shows how to configure connection attributes for a default DSN. While not necessary, you can configure connection attributes for a default DSN as you would configure any other DSN. Notice that it is not specified in the ODBC Data Sources section. Note that /disk1/timesten is the timesten_home.

[ODBC Data Sources]
database1=TimesTen 18.1 Driver
...
 
[default]
Driver=/disk1/timesten/lib/libtten.so
DataStore=/disk1/timesten/sample_db/DemoDataStore/default
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8

[database1]
Driver=/disk1/timesten/lib/libtten.so
DataStore=/disk1/timesten/sample_db/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8

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.

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

For example, the database1 DSN could have a specification that includes the following. Note that /disk1/timesten is the timesten_home.

[database1]
Driver=/disk1/timesten/lib/libtten.so
DataStore=/disk1/timesten/sample_db/DemoDataStore/database1
...

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

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

[database1CS]
TTC_Server=ttserver
TTC_SERVER_DSN=database1
TTC_Timeout=30

odbc.ini file example

The following example shows portions of a Linux or UNIX .odbc.ini file. Note that /disk1/timesten is the timesten_home.

...
[ODBC Data Sources]
database1=TimesTen 18.1 Driver
...
 
[database1]
Driver=/disk1/timesten/lib/libtten.so
DataStore=/disk1/timesten/sample_db/DemoDataStore/database1
PermSize=128
TempSize=64
DatabaseCharacterSet=AL32UTF8
...
 
########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 18.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]
database1CS=TimesTen 18.1 Client Driver
...
 
[database1CS]
TTC_SERVER=localhost
TTC_SERVER_DSN=database1
...

Using a connection string in TimesTen Scaleout

In TimesTen Scaleout, you can connect to a specific element by specifying in the connection string the address of the host associated with that element, the database name, and a database user with at least CREATE SESSION privileges. See "Using a connection string to establish a client connection" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for more details.

Using a connection string in TimesTen Classic

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

• The name or path name of the ODBC driver using the Driver attribute.

  • On Windows, the value of the Driver attribute should be the name of the TimesTen Client Driver.

  • On UNIX systems, the value of the Driver attribute should be the pathname of the TimesTen ODBC Driver shared library file (as described in Table 1-1). The file resides in the timesten_home/lib directory.

• The database path and file name prefix using the DataStore attribute.

• The character set for the database using the DatabaseCharacterSet attribute.

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

Providing the connection attributes on the connect string from a Linux/UNIX client. Note that /disk1/timesten is the timesten_home.

% ttIsql 

Command> connect "Driver=/disk1/timesten/lib/libtten.so;
DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8;

Providing the connection attributes on the connect string from a Windows client:

C:\ ttIsql
Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
Command> connect "Driver=TimesTen Client 18.1;
DataStore=/disk1/databases/database1;DatabaseCharacterSet=AL32UTF8";