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.

Tuesday Dec 20, 2011

Developing Applications using TimesTen

As we've discussed before, the TimesTen In-Memory Database is a fully featured relational database.  Because of its in-memory architecture it provides very fast response time, while providing standard APIs and interfaces as any relational database would. Due to these standard interfaces developing applications that run at in-memory speeds is easy with TimesTen, and leverages skills that developers already have.  


Like any relational database, data in TimesTen is stored in a schema consisting of tables.  Tables contain multiple rows of information; each row is made up of named columns.

The basic concepts used in TimesTen - schemas, users, tables, indexes, views, etc. - are compatible with Oracle Database.  

TimesTen is a fully transactional database ... work can be committed or rolled back as you expect.  TimesTen is also multi-user, supporting thousands of simultaneous connections to a single TimesTen database.  

Users are created and identified with passwords in ways that will be familiar to Oracle Database users, or users of other databases...and access to data is controlled by GRANT and REVOKE in standard ways.

The net is that TimesTen is a fully featured relational database, providing the same basic facilities as any other.  


TimesTen's SQL dialect is also very compatible with Oracle Database.  Tables are created with CREATE TABLE; industry-standard types such as CHAR and NCHAR are supported, as are Oracle's VARCHAR2 and NUMBER.  Sequences and views are created in the same way they would be in Oracle Database.  SELECT, INSERT, UPDATE, and DELETE are there just the way you expect them.  


Most users of Oracle Database are familiar with PL/SQL.  PL/SQL is a procedural language designed to be easy to use alongside SQL. TimesTen supports PL/SQL in many of the same ways that Oracle Database does.  TimesTen supports stored procedures, packages and functions written in PL/SQL, as well as anonymous blocks executed from application programs.  

TimesTen's dialect of PL/SQL is quite compatible with Oracle Database - in fact, TimesTen includes the standard PL/SQL compiler and runtime environment from Oracle Database unchanged.  


Application Program Interfaces (APIs) are used by application programs to execute SQL and PL/SQL.  JDBC is the standard API used to talk to databases from Java; ODBC and OCI are similar APIs for "C" and C++ languages.  TimesTen supports all three APIs.  It also supports the Pro*C preprocessor for applications that would prefer to use embedded SQL instead of a call-level interface.   And in Windows environments TimesTen supports ADO.NET via the Oracle Data Provider for .NET (ODP.NET).  

Tools and Environments

Because TimesTen supports standard SQL and standard APIs, it follows that TimesTen also works with a variety of tools built on top of them.  In the Java world, many standard object-relational mapping facilities such as Hibernate and EclipseLink work with TimesTen, as do application server environments such as Oracle Weblogic Server, GlassFish, Websphere, JBoss, and many others.  

SQL Developer is a fantastic tool that lets you explore and manage your tables, indexes and data in Oracle Databases and many others.  Out of the box SQL Developer fully supports TimesTen as well.  

And to manage your TimesTen installations with Oracle Enterprise Manager (EM), TimesTen provides a plugin which lets EM manage TimesTen databases.  


TimesTen supports the most commonly used concepts and facilities from Oracle Database.  Given the amazing capabilities of Oracle Database, not every feature in Oracle Database is supported in TimesTen.  But by providing the most commonly used interfaces from Oracle in a compatible manner, TimesTen is easy to learn and to use. Allowing you to re-use existing concepts, schemas and code can greatly accelerate the development of fast in-memory solutions for your business.  

In short, since the same tools, APIs and interfaces are used when developing for TimesTen as for Oracle Database, developing applications to run at in-memory speed with TimesTen is easy for database developers.  Give it a try!  


Details of TimesTen's SQL are in the TimesTen SQL Reference.

Manuals describing how to use TimesTen from C and Java, and with PL/SQL, are on the main TimesTen documentation page

Information on integrating TimesTen with application servers, SQL Developer, Oracle Enteprise Manager, and other environments is located on the main TimesTen page at OTN.  


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.


« May 2015