Unlike traditional disk-optimized relational databases such as the Oracle Database, DB2, Informix or SQL Server, whose designs must contain algorithms that attempt to minimize disk accesses, timesten stores all data in memory. As we all know memory is much faster than disk and that's the main secret sauce for Exalytics. 

In my last post, I wrote about how to create a datastore in Timesten and get started.Once a datastore has been created, the user faces the task of managing it. In this post I am going to write about some basic timesten commands that every OBIEE developer needs to be aware of to manage the datastore.

ttstatus

TTstatus shows important information about the Timesten instance. Apart from the information that the instance is up and running, TTstatus shows:

• Information about the Timesten daemon
• Information if the Timesten server is running or stopped
• The number of connections to the data store

In the above screenshot, we see that there are 13 connections, but there are only 2 processes that are connected. The rest are subdaemon connections that are spawned from the 2 main processes above. They are not of importance when checking the number of connections.

Rampolicy

Rampolicy is the setting that decides when the datastore is loaded into the memory and when it is unloaded. After creation, the Rampolicy of a datastore is set to InUse by default.

InUse – The datastore is loaded into the memory when the first connection is made to the datastore and is unloaded when the last connection has been disconnected.

Always – Datastore stays in memory always irrespective of the presence/absence of connections.

In a datawarehousing application where the datastore is large, it is practical to set the Rampolicy to Manual to have better control.

Manual – The user explicitly issues commands to load/unload the datastore from memory.

• To view the Rampolicy

- ttadmin TT_AGGR_STORE

• To change the Rampolicy to Manual

- ttadmin –rampolicy manual TT_AGGR_STORE

• To load a datastore into memory

- ttadmin –ramload TT_AGGR_STORE

• To unload a datastore from memory

- ttadmin –ramunload TT_AGGR_STORE

Note. A datastore should be only unloaded if there are no connections to the datastore. The connections can be checked using ttstatus command.

ttdaemonadmin

This command is used to start/stop the Timesten daemon, and the Timesten server.

Stop/Start Timesten Server:

- ttdaemonadmin -stopserver | -startserver

If the Timesten server is stopped, no client application will be able to connect to the Timesten instance. But the Timesten instance will be up and running and users can still work directly on the server machine.

Stop/Start Timesten Daemon:

- ttdaemonadmin -stop | -start | -restart

If the Timesten daemon is stopped, the instance will become unavailable.

Note. Before a Timesten daemon is stopped, it is important to make sure that there are no connections to the datastore and that the datastore is gracefully unloaded from memory (if the Rampolicy is set to Manual).

ttbackup

ttbackup utility is used to create a backup of the datastore for use within the same major release and also on the same platform.

For example,

ttBackup –connstr “dsn=tt_aggr_store” –type streamfull > /home/oracle/ttbk/tt_aggr_store_backup.ttb

The type Stream ensures that the backup is written to the stdout. The above command writes the full backup of the datastore to a single file specified in the command.

There are other backup options also like File backup instead of Stream.

The option of Incremental backup is also there instead of Full. Whereas incremental backup reduces the time taken for creating backups, they may result in more disk usage and longer restoration times. This is because of the need to scan and process the transaction log records created since the last incremental backup.

ttrestore

When required, the backup may be restored to the Timesten instance using the ttrestore utility.

ttmigrate

This utility may be used to migrate a datastore from one major release to another (for example, from 11.2.1.x.x to 11.2.2.y.y). It may also be used to migrate between bit-levels, say from 32-bit to 64-bit machine, both on the same architecture, e.g. both on Linux.

Note. Once a datastore has been created, its Perm size cannot be reduced. It means that once we have allocated a fixed memory size to a datastore, we would not be able to load the datastore into a smaller memory segment. For doing this, we should also use the ttmigrate utility. We should create a backup using ttmigrate, destroy the original datastore, modify the dsn of the datastore to reduce the Perm Size and again use ttmigrate to restore the datastore with a lower size.

ttdestroy

This utility is used to delete a datastore gracefully from the system. Deletion of data files manually is not recommended. The utility does not remove the datastore definition from the sys.odbc.ini file though.

- ttdestroy TT_AGGR_STORE

ttxactadmin

There are different levels of locks in Timesten which are acquired during read/write or ddl/dml operations. The higher the level of locks, the lower is the concurrency in the system.

To identify the locks in the datastore the ttxactadmin utility may be used:

- ttxactadmin TT_AGGR_STORE

If we wish to remove a lock, we will need to rollback the transaction that created the lock. After getting the transaction id from the above command, we can perform the rollback using:

- ttXactAdmin -xactIdRollback <transaction_id>

For example, ttXactAdmin –xactIdRollback 1.21047 tt_aggr_store

Want to know more about doing analysis at the speed of thought.