Connecting to a Database

To be able to connect to a database, every element of the database needs to be created, loaded into memory, added to the distribution map, and opened for user connections. All these operations are covered in the previous topic, Creating a Database.

A connectable defines a name that applications can use to connect to a database. The connectable may have the same name as the database or may have a different name. There are two types of connectables:

Direct connectable: Defines a name by which applications may connect to a database through direct communication.
Client/server connectable: Defines a name by which applications may connect to a database through client/server communication.

TimesTen Scaleout enables you to create multiple connectables with different sets of connection attributes defined for a single database.

Connectables support these types of connection attributes:

General connection attributes are set by each connection and persist for the duration of that connection.
NLS general connection attributes define the connection-specific behavior of the database regarding globalization.
PL/SQL general connection attributes define the connection-wise behavior of the database regarding PL/SQL operations.
Client connection attributes define the connection parameters for client/server connections.

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

Create a Connectable

TimesTen Scaleout creates a direct connectable by default for every database definition added to the grid, this connectable enables applications to create direct connections to the database from any data instance in the distribution map of the database. TimesTen Scaleout uses the name assigned to the database definition to name the connectable. You need to create a client/server connectable to establish client/server connections to a database.

The tasks to create a connectable are:

Creating a Connectable File

A connectable file specifies the attributes to use to connect to a database. The connectable file must use .connect as file name suffix. The file name prefix of the connectable file sets the name of the connectable. For example, a connectable file named database1CS.connect creates a connectable named database1cs.

Note:

Connectable names have the same restrictions as Data Source Names. See Specifying Data Source Names to Identify TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.

Create a client/server connectable file with the connection attributes for the database1 database.

The example shows the contents of a connectable file named database1CS.connect.

Sets AL32UTF8 as the connection character set.
Provides terry as the user ID in UID connection attribute.

Note:

If you do not provide a user ID, TimesTen utilizes the OS user ID of the user that sends the connection request as the UID. In this case, connection requests coming from systems other than the localhost fail since the OS user ID cannot be authenticated.
Provides the user credentials by providing the location of a wallet in the connectable with the PwdWallet connection attribute. Providing credentials in a wallet is more secure than supplying a password on the connection string with the PWD or PWDCrypt connection attributes.

ConnectionCharacterSet=AL32UTF8
UID=terry
PwdWallet=/wallets/dsn1wallet

Before you can use a connectable that provides a wallet, you must first create the wallet for the user credentials. See Providing a User Name and Password in an Oracle Wallet in Oracle TimesTen In-Memory Database Security Guide on how to add user credentials in an Oracle Wallet.

If you do provide a wallet, then the wallet must be located in the same path on every data element from which the user accesses the connectable.

See UID and PWD and PwdWallet in Oracle TimesTen In-Memory Database Reference and Authentication in TimesTen in Oracle TimesTen In-Memory Database Security Guide.

Creating a Connectable Based on the Connectable File

The ttGridAdmin connectableCreate command creates a connectable based on a connectable file.

Create the database1CS connectable based on the database1CS.connect connectable file.

% ttGridAdmin connectableCreate -dbdef database1 -cs /mydir/database1CS.connect
Connectable database1CS created.

Note:

The -cs option enables the connectable for client connections instead of direct connections.
Use the -only option to establish client connections only to the element of the specified data instance.

Apply the creation of the database1CS connectable to the current version of the model to make the connectable available for use.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

For more information on the ttGridAdmin connectableCreate or ttGridAdmin modelApply command, see Create a Connectable (connectableCreate) in Oracle TimesTen In-Memory Database Reference or Applying the Changes Made to the Model, respectively.

Connect to a Database Using ODBC and JDBC Drivers

Applications can use the ODBC direct driver, the ODBC client driver, or an ODBC driver manager to connect to a database. See Connecting to TimesTen with ODBC and JDBC Drivers in Oracle TimesTen In-Memory Database Operations Guide.

The following topics discuss how to use those DSNs to establish direct and client connections to a database:

Establishing Direct Connections from a Data Instance

TimesTen Scaleout automatically creates a direct connectable that includes any general connection attribute included in the database definition file. TimesTen Scaleout uses the name of the database definition to name the connectable. When the connectable is applied to the current version of the model, TimesTen Scaleout defines a DSN in every data instance with the same name as the connectable. This allows ODBC and JDBC applications to connect to the database associated with the connectable.

You may use the ttIsql utility from a data instance to establish direct connections to a database.

From the host3.instance1 data instance, connect to the database1 database using the database1 connectable.

% ttIsql -connStr "DSN=database1"

Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1";
Connection successful: DSN=database1;UID=instanceadmin;DataStore=/disk1/databases/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogDir=/disk2/logs;
PermSize=32768;TempSize=4096;TypeMode=0;
(Default setting AutoCommit=1)
Command>

Note:

The example connects to the database as the instance administrator, which is defined for all instances (data and management) of the grid1 grid. For more information on database users, see Overview of TimesTen Users in Oracle TimesTen In-Memory Database Security Guide.

For more information on the ttIsql utility, see Using the ttIsql Utility in Oracle TimesTen In-Memory Database Operations Guide.

Establishing Client Connections from a TimesTen Client

A client/server connectable enables all data instances to accept connections from a TimesTen client instance or applications using the TimesTen client driver. However, to establish a client connection from a TimesTen client that is not part of the grid, you have to create a client DSN in the system or user odbc.ini file of the TimesTen client.

The ttGridAdmin gridClientExport command exports every client/server connectable available for the grid into a file that is formatted to replace the system or user odbc.ini file used by the TimesTen client.

Export the client/server connectables of the grid1 grid into a file.

% ttGridAdmin gridClientExport /mydir/sys.odbc.ini

The following example shows the contents of the resulting file.

[ODBC Data Sources]
database1CS=TimesTen 22.1 Client Driver
 
[database1CS]
TTC_SERVER_DSN=DATABASE1
# External address/port info for host3.instance1
TTC_SERVER1=host3.example.com/6625
# External address/port info for host4.instance1
TTC_SERVER2=host4.example.com/6625
# External address/port info for host5.instance1
TTC_SERVER3=host5.example.com/6625
# External address/port info for host6.instance1
TTC_SERVER4=host6.example.com/6625
# External address/port info for host7.instance1
TTC_SERVER5=host7.example.com/6625
# External address/port info for host8.instance1
TTC_SERVER6=host8.example.com/6625
ConnectionCharacterSet=AL32UTF8
UID=terry

For more information on the ttGridAdmin gridClientExport command, see Export sys.odbc.ini for Client/Server Connections Outside Grid (gridClientExport) in Oracle TimesTen In-Memory Database Reference.

Adding a Client DSN to a TimesTen Client on Linux or UNIX

To add a client DSN to a TimesTen client on Linux or UNIX, either replace the system or user odbc.ini file of the TimesTen client with the file you just created, or copy the contents of the file into the system or user odbc.ini file. Then, from the TimesTen client, connect to the database1 database using the database1CS DSN with the ttIsqlCS utility.

% ttIsqlCS -connStr "DSN=database1CS"

Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1CS;UID=terry;
Enter password for 'terry': 
Connection successful: DSN=database1CS;TTC_SERVER=host3.example.com;TTC_SERVER_DSN=DATABASE1;
UID=terry;DATASTORE=/disk1/databases/database1;DATABASECHARACTERSET=AL32UTF8;CONNECTIONCHARACTERSET=AL32UTF8;
PERMSIZE=32768;TEMPSIZE=4096;TYPEMODE=0;
(Default setting AutoCommit=1)
Command>

Note:

The example connects to the database with the terry user, which has at least CREATE SESSION privileges on the database. See Creating or Identifying a Database User in Oracle TimesTen In-Memory Database Security Guide.

For more information on the ttIsqlCS utility and the TimesTen client, see Working with the TimesTen Client and Server in Oracle TimesTen In-Memory Database Operations Guide.

Adding a Client DSN to a TimesTen Client on Windows

You can add a client DSN to a TimesTen client on Windows by using the ttInstallDSN utility included in the TimesTen client Release 22.1. The ttInstallDSN utility creates a system DSN based on the contents of the output file of the ttGridAdmin gridClientExport command. You will need to make the file or its contents available to the Windows system where the TimesTen client is installed.

C:\>ttInstallDSN -f C:\Users\terry\Downloads\sys.odbc.ini
 
Found the following DSNs in available 'C:\Users\terry\Downloads\sys.odbc.ini'.
0 : database1CS
[ Please select the DSN to be imported: ]
0
Adding DSN 'database1CS'.

Note:

You must run the ttInstallDSN utility as administrator of Windows with the environment variables for the TimesTen client set. See Setting Environment Variables for TimesTen in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

From the TimesTen client system, you can now connect to the database1 database using the database1CS DSN with the ttIsql utility.

C:\>ttIsql -connStr "DSN=database1CS"
 
Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1CS;UID=terry;
Enter password for 'terry': 
Connection successful: DSN=database1CS;TTC_SERVER=host3.example.com;TTC_SERVER_DSN=DATABASE1;
UID=terry;DATASTORE=/disk1/databases/database1;DATABASECHARACTERSET=AL32UTF8;CONNECTIONCHARACTERSET=AL32UTF8;
PERMSIZE=256;TEMPSIZE=128;TYPEMODE=0;
(Default setting AutoCommit=1)
Command>

Note:

For more information on the ttInstallDSN utility, see ttInstallDSN in Oracle TimesTen In-Memory Database Reference.

For more information on the ttIsql utility and the TimesTen client, see Working with the TimesTen Client and Server in Oracle TimesTen In-Memory Database Operations Guide.

Using a Connection String to Establish a Client Connection

Alternatively, you can connect to a specific element by defining 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. (The client TCP/IP port is only necessary if the instance is not running with the default port.)

% ttIsqlCS -connStr "TTC_SERVER=host3.example.com;TTC_SERVER_DSN=database1;TCP_Port=6625;UID=terry"

Establishing Encrypted Client Connections from a TimesTen Client

A client/server connectable can enable all data instances to accept or require encrypted connections from a TimesTen client instance or applications using the TimesTen client driver.

However, to establish an encrypted client connection from a TimesTen client instance that is not part of the grid, you have to import the following into the TimesTen client instance:

The Certificate Authority (CA) public key
The client certificate
The sys.odbc.ini file with every client/server connectable available in the grid

The ttGridAdmin gridClientExportAll command exports a sys.odbc.ini file containing all client/server connectables and an Oracle Wallet with the necessary certificates into a .zip file.

The ttClientImport utility uses the resulting .zip file to import the Wallet and client/server connectables into a TimesTen client in UNIX or Windows.

To establish a encrypted client connection from a TimesTen client, do the following:

Note:

Both the client and server need to be set to either accept, request, or require encrypted connections and support matching cipher suites, as shown in Configuration for TLS for Client/Server in Oracle TimesTen In-Memory Database Security Guide.

On the active management instance, export all the necessary files for encrypted client connections to databases in the grid into a file.

% ttGridAdmin gridClientExportAll /mydir/myfile.zip
Definitions exported to /mydir/myfile.zip

Next are the contents of some of the resulting files.

% zip -sf /mydir/myfile.zip 
Archive contains:
  gridWallet/
  gridWallet/cwallet.sso
  sys.odbc.ini
  exportinfo.json
Total 4 entries (2924 bytes)

% unzip -p /mydir/myfile.zip sys.odbc.ini
[ODBC Data Sources]
database1CS=TimesTen 22.1 Client Driver
 
[database1CS]
TTC_SERVER_DSN=DATABASE1
# External address/port info for host3.instance1
TTC_SERVER1=host3.example.com/6625
# External address/port info for host4.instance1
TTC_SERVER2=host4.example.com/6625
# External address/port info for host5.instance1
TTC_SERVER3=host5.example.com/6625
# External address/port info for host6.instance1
TTC_SERVER4=host6.example.com/6625
# External address/port info for host7.instance1
TTC_SERVER5=host7.example.com/6625
# External address/port info for host8.instance1
TTC_SERVER6=host8.example.com/6625
CipherSuites=SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
ConnectionCharacterSet=AL32UTF8
Encryption=requested
UID=terry
Wallet=!!TIMESTEN_HOME!!/conf/wallets/clientWallet

% unzip -p /mydir/myfile.zip exportfinfo.json
{
   "modelVersion" : null,
   "exportTime" : "2021-08-03T21:36:06.000Z",
   "gridName" : "grid1",
   "modelTime" : null,
   "gridGuid" : "A6BED5B5-0B03-4FB7-A5B8-728B270BCECB",
   "serverEncr" : "requested",
   "TimesTenVersion" : "22.1.1.18.0",
   "serverCiphers" : "SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384"
}

Import the client/server connectables and Wallet to a TimesTen client instance on UNIX or Windows. You will need to make the file available to the UNIX or Windows system where the TimesTen client is installed.
- On the TimesTen client instance (UNIX):
```
% ttClientImport /home/terry/Downloads/myfile.zip
Client definitions imported.
```
- On the TimesTen client instance (Windows):
```
C:\> ttClientImport C:\Users\terry\Downloads\myfile.zip
Client definitions imported.
```

Connect to the database using the client DSN with the ttIsqlCS (UNIX) or ttIsql (Windows) utility.

On the TimesTen client instance (UNIX):

% ttIsqlCS -connStr "DSN=database1CS"

Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=database1CS";
Enter password for 'terry': 
Connection successful: DSN=database1cs;TTC_SERVER=host3.example.com;TTC_SERVER_DSN=DATABASE1;
UID=terry;DURABILITY=0;DATASTORE=/disk1/databases/database1;DATABASECHARACTERSET=AL32UTF8;
CONNECTIONCHARACTERSET=AL32UTF8;WAITFORCONNECT=0;LOGDIR=/disk2/logs;PERMSIZE=32768;TEMPSIZE=4096;
CONNECTIONS=100;Encryption=Requested;Wallet=/grid/instance1/conf/wallets/gridWallet;
CipherSuites=SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384;
(Default setting AutoCommit=1)
Command>

On the TimesTen client instance (Windows):

C:\> ttIsql -connStr "DSN=database1CS"

Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=database1CS";
Enter password for 'terry': 
Connection successful: DSN=database1cs;TTC_SERVER=host3.example.com;TTC_SERVER_DSN=DATABASE1;
UID=terry;DURABILITY=0;DATASTORE=/disk1/databases/database1;DATABASECHARACTERSET=AL32UTF8;
CONNECTIONCHARACTERSET=AL32UTF8;WAITFORCONNECT=0;LOGDIR=/disk2/logs;PERMSIZE=32768;TEMPSIZE=4096;
CONNECTIONS=100;Encryption=Requested;Wallet=/grid/instance1/conf/wallets/gridWallet;
CipherSuites=SSL_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384;
(Default setting AutoCommit=1)
Command>

Note:

To revoke encrypted access to a TimesTen client instance already set for encrypted connections, you need to regenerate the certificates on the grid. See Regenerating Certificates in TimesTen Scaleout in Oracle TimesTen In-Memory Database Security Guide.

For more information on the ttGridAdmin gridClientExportAll command, see Export sys.odbc.ini and Certificates for Encrypted Client/Server Connections (gridClientExportAll) in Oracle TimesTen In-Memory Database Reference.

Redirecting Client Connections

When an application connects to a client/server connectable a TCP/IP connection is established to one of the data instances in the grid. However, if the instance is busy then the instance can automatically redirect the client connection to another instance in the grid.

By default, a client connection can be automatically redirected to any available instance within the grid. However, you can limit or change this behavior with:

The TTC_Redirect connection attribute, which defines how a client is redirected.
- Automatic redirection: By default, this connection attribute is set to 1 so that a client connection is automatically redirected to any available instance within the grid if the current instance is busy or unavailable. The connection is redirected to the instance with the fewest number of client connections.
- Elements within a single replica set: If you want the client to connect to instances with elements within a single replica set (because the data you are interested in is contained within this replica set), then set the TTC_Redirect attribute to 0. Then, the client connects only to the instances with elements in the same replica set. If the connection is rejected, then a connection error is returned.
The TTC_Redirect_Limit connection attribute, which limits how many times the client is redirected. The number of instances in your grid may be of a size that you want to limit the number of redirected client connection attempts for performance reasons. You can set the TTC_Redirect_Limit attribute to the number of connection redirection attempts. For example, setting TTC_Redirect_Limit limits the number of client connection redirection attempts to other instances to 10 attempts. If the client does not connect within this number of attempts, a connection error is returned.

If the client connection cannot be redirected to a suitable instance, then the client connection fails. See Client Connection Failover for more information on the client failover process.

For more information on the TTC_Redirect or TTC_Redirect_Limit connection attributes, see TTC_REDIRECT or TTC_Redirect_Limit, respectively, in Oracle TimesTen In-Memory Database Reference.

See Modify the Connection Attributes in a Connectable for information on how to modify client connection attributes.

Verify If Your Database Is a Distributed Database

If you want to verify that the database you are connected to is indeed a distributed database (TimesTen Scaleout) and not a single-instance database (TimesTen Classic), call for the value of the ttGridEnable attribute with the ttConfiguration built-in procedure. The built-in procedure returns ttGridEnable=1 for databases in a grid.

Command> CALL ttConfiguration('ttGridEnable');
< TTGridEnable, 1 >
1 row found.

For more information on the ttConfiguration built-in procedure, see ttConfiguration in Oracle TimesTen In-Memory Database Reference.