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.

Thursday Mar 12, 2015

Product News on SUSE 12 and HP-UX Itanium

Thursday – March 12, 2015

Recently, we added support for two new platforms with the latest release of Oracle TimesTen In-Memory Database:

  • TimesTen is certified for use with SUSE Linux Enterprise Server 12 (download the same binary for Linux X86-64 bit)
  • TimesTen is certified for the HP-UX Itanium platform (v11.31)
The software is available for download from the Oracle Support site http://support.oracle.com

Monday Dec 12, 2011

TimesTen and In-Memory Database Cache

Several recent posts I’ve written so far are intended to help explain the basics of what the Oracle TimesTen In-Memory Database is. If you’ve been reading along, you know that TimesTen is a very fast relational database that provides standard interfaces like SQL, PL/SQL, JDBC, ODBC and OCI to access data. TimesTen provides this very fast access to data in large part due to its “in-memory” architecture. TimesTen, unlike most databases, stores all data in RAM. (A copy is also maintained on disk.)

Because TimesTen stores data in RAM, the size of a TimesTen database is limited by the amount of physical RAM on a machine. On modern 64-bit systems this isn’t much of a limitation! Today (late 2011) systems can be purchased which have several Terabytes of RAM, and future hardware will naturally support even more.

But even though I really like TimesTen, I have to admit … some databases are bigger than that. J Traditional database systems, like Oracle Database itself, store data primarily on disk. Oracle Database is obviously a fantastic product, and one of the benefits it brings is the ability to support very large databases easily … ones larger than could be cost-effectively handled in memory in many cases.

So TimesTen is a fully featured database, and it can be used on its own to provide data storage for many applications. But in many cases what we really want is the best of both worlds … the fast memory-centric performance of TimesTen, combined with the large capacity and familiarity of the Oracle Database. If only you could have both!

Database Caching

Enter the “In-Memory Database Cache”. IMDB Cache is TimesTen … with a few extra tricks. It lets one or more TimesTen databases be used “in front” of a back-end Oracle Database to cache “hot” data.

There are a number of different ways that IMDB Cache can be used. Just to get across the basic concept, here’s one particular scenario. Suppose you run a “shopping” website, where customers log on, browse your catalog of goods, and place orders. All that data is stored in an Oracle Database. Your application runs in Java application servers in the mid-tier. Alongside the application server of your choice, you run TimesTen on the application server. TimesTen is configured to cache tables from the Oracle Database.

Let’s say user “Scott” logs on to your website. His first click on the site accesses his customer profile from the local TimeTen database. But Scott hasn’t been to your site for some time, so his data isn’t present in TimesTen. So under the covers, TimesTen automatically fetches Scott’s data from the Oracle Database, storing it in TimesTen for future reference. This initial load operation can load a number of items from a number of different tables. In this case, we’ll cache Scott’s preferences (such how he likes his web pages to be formatted) as well as his shopping cart contents and recent order history.

So once Scott has logged in to the site, everything about Scott has been loaded into TimesTen. His subsequent clicks on the site are all handled by TimesTen. Queries are handled by TimesTen exclusively; when items are changed – for example, if Scott adds an item to his shopping cart – those changes are made by the application in TimesTen, which automatically reflects them in Oracle Database as well.

When Scott logs off from the site, the copy of his data stored in the local TimesTen database can be removed, freeing up RAM to be used for other active users. And if Scott simply goes to lunch, when RAM is needed for other users then Scott’s data can be automatically reclaimed.

In this scenario, the bulk of database workload of your site is handled in RAM, by the TimesTen database. The TimesTen database automatically interacts with Oracle Database to cache information for active users of the site. Users to your site see the fastest possible response time, and workload on your Oracle Database is reduced.

Since TimesTen’s APIs and interfaces are very compatible with Oracle Database, the best part is that your application can manage data in a single relational schema, with a single set of tools and APIs (SQL, PL/SQL, JDBC, etc.). You can re-use code and schema used to manipulate data in the Oracle Database to implement a high performance in-memory cache … without needing to invent a new data model or data access library. The cache is itself a fully featured relational database … TimesTen.

As I said, there are many different examples of how TimesTen (er, the IMDB Cache) can be used to work with Oracle Database to provide scalable and high performance solutions. We’ll discuss this in more detail in future articles, I’m sure!


Oracle In-Memory Database Cache User’s Guide … Cache Concepts

White Paper: Using In-Memory Database Cache to Accelerate the Oracle Database:


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