Back to Basics - Multiple Connections in ttIsql
By SusanCheung-Oracle on Apr 28, 2015
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:
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";
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
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.