Connecting to 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 inUID
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 thePWD
orPWDCrypt
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:
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 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.
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 Task 1: Generate Certificates and Set TLS Attributes with ttGridAdmin gridCreate and instanceCreate 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 to0
. 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 theTTC_Redirect_Limit
attribute to the number of connection redirection attempts. For example, settingTTC_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.