Tuesday Apr 28, 2015

Back to Basics - Multiple Connections in ttIsql

A user recently asked if ttIsql can support multiple concurrent connections to a TimesTen database. We thought the answer might be of interest to other users as well, and decided to include it in this blog.

ttIsql is an interactive SQL command line utility for working with TimesTen databases. In addition to supporting SQL command execution, ttIsql provides a rich set of functionality to enable the users to connect to the databases, execute built-in procedures and utilities, support flexible command editing, and the ability to invoke host OS commands, all within the same ttIsql session.

One of the scenarios where it’s convenient to have multiple connections within the same ttIsql session is when you want to execute commands that require different user credentials.  For example, there are database operations that require the Admin privilege, and other operations that only require table owner or general database access privileges. 

If you plan to use multiple connections, it’s useful to name the connections so that ttIsql can help you clearly identify which connection you are executing. 

In the example below, we will create two connections to the sample database, sampledb – first connection as the application user (appuser) and the second connection as the Admin user (adm). We will use the CONNECT command and specify a connection name for each:

$ ttIsq

Command> CONNECT “DSN=sampledb;uid=appuser;pwd=appuser;ConnectionName=app_conn”;

Command> SELECT count (*) from mytable;

< 1000000 >
1 row found.

Command> CONNECT "DSN=sampledb; uid=adm; pwd=adm; ConnectionName=adm_conn";

adm_conn: Command>

In ttIsql, the last connection made is the current active connection, and the ttIsql prompt displays the connection name currently in use. As shown above, adm_conn is the connection currently in use.

adm_conn: Command> CALL ttRamPolicySet (‘manual’);

The ttRamPolicySet built-in procedure requires the ADMIN privilege; instead of exiting the current ttIsql session or starting another session, we conveniently use the multiple connection functionality here. As shown above, we executed a command to set the RAM policy to MANUAL using the Admin connection.

To switch to a different connection, you simply execute the USE command:

adm_conn: Command> USE app_conn;

app_conn: Command> CALL ttVersion();

< 11, 2, 2, 8, 0 >
1 row found.

Notice that after the USE command, the ttisql prompt changed to the app_conn to indicate the current connection context. ttVersion is a built-in procedure that returns the current TimesTen version, and it can be called by any user.

To disconnect a connection, use the DISCONNECT command:

app_conn: Command> DISCONNECT adm_conn;

Another useful feature in ttIsql is the ability to execute host OS commands. You can execute any OS commands that you are entitled to. As a simple example below, we executed the MORE command to view the content in myconfig.ini file:


app_conn: Command> HOST more /home/appuser/myconfig.ini

This is myconfig.ini file
Line 1
Line 2
Line 3

app_conn: Command> exit

$

There are many other features in ttIsql you can use to help automate your day-to-day operations. Refer to the TimesTen Database Reference product documentation for more details.

About

This blog covers the Oracle TimesTen In-Memory Database and the TimesTen Application-Tier Database Cache. The blog is maintained by Sam Drake. He has worked on the TimesTen product for more than 15 years, and is presently an Architect working in TimesTen development at Oracle.

Search

Categories
Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today