Introduction

Create XE DB

Once TimesTen XE has been installed and an instance created, you probably want to create a database.

This blog shows you how to create a small database with the minimum configuration.

A follow up blog will cover more advanced database configurations and performance tuning.

 

 

Set the TimesTen Environment

This blog assumes that TimesTen XE has been installed in the users home directory, eg /home/opc/xe

You need to set the TimesTen XE environment before you can use the TimesTen XE utilities.

cd ~/xe/bin
. ttenv.sh

eg

ttenv.sh

 

 

Critical sys.odbc.ini attributes

TimesTen XE is an ODBC database.  This means that the lowest level SQL API is ODBC and ODBC concepts like a DataStore and Data Source Names need to be used to work with TimesTen databases. 

A file called sys.odbc.ini contains a set of attributes which defines the behavior of a TimesTen XE database.  There are over 100 possible attributes.

The following are the most important attributes needed to create a simple TimesTen XE database:

  • Data Source Name
    • The DSN is the name of the database, eg sampleDB, sales or marketing
  • Driver
    • The driver is the Linux path to the TimesTen shared library which is the interface to the TimesTen database
  • DataStore
    • The DataStore is the Linux path to the TimesTen checkpoint files
    • These are the database datafiles
    • There are two checkpoint files with filename extensions of .ds0 and .ds1
  • LogDir
    • The LogDir is the Linux path to the transaction log files
    • TimesTen combines the redo and undo into a single file called a transaction log file
    • There will be a set of transaction log files based on the workload
    • Checkpointing will delete the oldest transaction logs files which are no longer needed
  • PermSize
    • The PermSize is the size of RAM used to store table data and indexes
    • The PermSize unit is MB.  Given sufficient RAM, the effective PermSize can by many TB
    • All of the data needs to be in RAM all of the time. eg 500 GB of data will require a PermSize of at least 500 GB
  • TempSize
    • The TempSize is a transient area of memory used for things like ORDER BY or GROUP BY
    • The TempSize unit is MB.  The workload will determine how large that TempSize needs to be, eg 100MB or 5 GB
  • LogBufMB
    • The LogBufMB is a memory buffer to accelerate log file writes
    • The LogBufMB unit is MB
    • Optimal values will tend to be in the range of 1024 to 8192 MB
  • LogFileSize
    • The LogFileSize is the size of each transaction log file
    • The LogFileSize unit is MB
    • The LogFileSize and LogBufMB should be the same size
  • DatabaseCharacterSet
    • The DatabaseCharacterSet is the character set used to store the table data
    • TimesTen XE supports the same NLS character sets as the Oracle Database 19c
    • Why the database character set matters
  • ConnectionCharacterSet
    • The character set of the connection’s client machine
    • There may be many client machines, each with potentially difference connection character sets
    • If the ConnectionCharacterSet = DatabaseCharacterSet then no NLS character set conversions are needed at runtime
  • OracleNetServiceName
    • The OracleNetServiceName is the SQLNet name of the Oracle database if TimesTen is being used as a cache database

 

 

Edit SYS.ODBC.INI config file

The sys.odbc.ini file is in the $TIMESTEN_HOME/conf directory. eg /home/opc/xe/conf/sys.odbc.ini

edit sys.odbc.ini

Edit the sys.odbc.ini file to use the following values

  • Data Source Name sampledb
  • DataStore /home/opc/databases/sampledb [make this the same as the Data Source Name]
  • Driver /home/opc/xe/install/lib/libtten.so [This path is generated by ttInstanceCreate]
  • PermSize 512      [This is arbitarily small so that not much RAM is need for your initial TimesTen XE database]
  • TempSize 128      [This is a small value. It could be many GB for large aggregations with many users]
  • LogBufMB 1024   [This is a good default value]
  • LogFileSize 1024 [Make it the same size as LogBufMB]
  • DatabaseCharacterSet AL32UTF8   [Use the same value as your Oracle database for cache, else use AL32UTF8]
  • ConnectionCharacterSet AL32UTF8 [Use this value unless you have a good reason not to]
  • OracleNetServiceName myTNSServiceName

 

This trivial example will require about 2 GB of RAM [512 MB + 1024 MB + 128 MB + extras] for a tiny TimesTen sampledb database.

Larger databases, ie larger values for PermSize will require more RAM.  Unlike most databases, TimesTen does not use a buffer cache to get data blocks from disk as needed.  All of the data needs to be in memory all of the time.  This is a space-time trade off. TimesTen XE will tend to be about 10x faster than any traditional database, but will tend to need more RAM.

sys.odbc.ini

The data source name [DSN] is sampledb. 

The DSN is the parent object and the set of attributes [eg Driver, DataStore etc] below that all belong to the DSN.

 

 

Create the directories for the data files and redo logs

mkdir database logs

The directories for the DataStore and LogDir attrobutes in the sys.odbc.ini file need to exist. eg

mkdir -p ~/databases
mkdir -p ~/logs
  • The DataStore directory needs to have the capacity for at least 2x PermSize
    • The checkpoint file is an exact copy of the shared memory segment
    • There are two checkpoint files, so the disk space needs to be greater than 2x the PermSize value
  • The LogDir needs to have the capacity of about 1x PermSize
  • The file system for LogDir needs to be fast enough for the TimesTen [redo] transaction log files
  • As IO from both redo and checkpointing can occur at the same time, care must be taken to not slow down the TimesTen XE database
    • Either the LogDir and DataStore should be on separate SSDs
    • or the both LogDir and DataStore can be on the same [high bandwidth] NVMe storage device

 

 

Create the database

The Linux OS user [eg opc] who installed TimesTen is called the Instance Administrator.  The Instance Administrator has extra database privileges [like SYSDBA for Oracle].

The ttIsql utility is a SQL shell like Oracle sqlplus.

Create the sampledb database via ttIsql as the Instance Administrator Linux OS user. eg

ttisql sampledb

 

create the database

  • You connection to a TimesTen XE database by using the Data Source Name, eg sampledb
  • As sampledb does not yet exist, the database will be created as a side effect the first time that you connection to it
  • Connecting directly to the DSN is like connecting to an Oracle database as SYSDBA
  • You want to create a user [eg scott] with minimal priviledges and create tables as that user
  • The default SQL Profile was used

 

 

Use the database

Connect as user scott

This example did the following:

  • Connected as user scott with password tiger to database sampledb
    • ttisql “uid=scott;pwd=tiger;dsn=sampledb”
    • This ODBC syntax with UID, PWD and DSN is equivalent to sqlplus. eg
      • sqlplus scott/tiger@sampledb
  • A trivial table was created
  • The table was described
  • Some data was inserted
  • The table was queried

 

You are now ready to do some more SQL with TimesTen XE.

For more infomation, the TimesTen SQL reference guide is here.

 

 

Learn more about TimesTen XE:

 

More TimesTen XE Blogs

 

 

 

Summary

  • You need a TimesTen XE instance to create a database
  • You need the TimesTen XE environment to been set via ttenv.sh
  • You need to define the critical attributes in sys.odbc.ini
  • You need to connect to the database as the instance admin user to create the database
  • You use ttIsql to connect to a database as a user with a password
  • TimesTen XE uses SQL to grant and revoke priviledges

 

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.