Oracle TimesTen and other Oracle Technologies: New type of data loading into TimesTen

Let's have a look on new feature which was introduced in 11.2.2.4 TimesTen version. I mean the opportunity to load data into TimesTen table based on the result of a query executed into Oracle database. If you don't want to use the cache group and cache grid features, if you don't want to create special users and etc., but at the same time, you would like to cache some information into TimesTen, please use this new feature.

Before using this feature you should make some preparation:

You should create user (or use the existing user) on Oracle DB site (plus grants).
DSN should have the same national database character set.
specify the DSN attributes which are needed for connection to Oracle DB.

For example, let's imagine that I've got one user in my Oracle DB (oratt user) and I would like to cache some sql query. I will use oratt user for connection and for execution the SQL query into Oracle DB. It means that the oratt user should have the grants for all objects which you would like to use in your query.

[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 7 18:55:28 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');

USERNAME
------------------------------
SYS
SYSTEM
ORATT
...

31 rows selected.

SQL> select d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');

no rows selected

SQL>

As you can see, there are no any specific TimesTen users as well. In TimesTen, we need to specify some DSN attributes (OracleNetServiceName,DatabaseCharacterSet,OraclePWD). In this case I use the following DSN:

[ds_test]
Driver=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so
DataStore=/u01/app/oracle/product/datastore/ds_test
LogDir=/u01/app/oracle/product/datastore/ds_test/log
PermSize=1024
TempSize=128
PLSQL=1
NLS_LENGTH_SEMANTICS=BYTE
DatabaseCharacterSet=WE8MSWIN1252
PLSQL_TIMEOUT=1000
OracleNetServiceName=orcl
OraclePWD=oracle

I should create the oratt user as well.

Command> CREATE USER oratt IDENTIFIED BY oracle;

User created.

Command> grant create session to oratt;

That is all. I don't need to do anything else for loading data. Now I can load data. There are two methods of loading information into TimesTen:

using createandloadfromoraquery command
using ttTableSchemaFromOraQueryGet and ttLoadFromOracle procedures

I will describe both of them. Let's start with the first one. Documentation says:
"The ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, will automatically create the TimesTen table, execute the SELECT statement on Oracle, and load the result set into the TimesTen table."
For example:

[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=oratt;PWD=oracle"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=ds_test;UID=oratt;PWD=oracle";
Connection successful: DSN=ds_test;UID=oratt;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> tables;
0 tables found.
Command>
Command> createandloadfromoraquery t_sql_load select level id,
       >                                             level val_1,
       >                                             level val_2,
       >                                             level val_3
       >                                        from dual
       >                                     connect by level <= 100;
Mapping query to this table:
    CREATE TABLE "ORATT"."T_SQL_LOAD" (
    "ID" number,
    "VAL_1" number,
    "VAL_2" number,
    "VAL_3" number
     )

Table t_sql_load created
100 rows loaded from oracle.
Command> tables;
  ORATT.T_SQL_LOAD
1 table found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>

As you can see, I loaded the result of the sql query into t_sql_load table. In this case TimesTen created the t_sql_load table, but I can also load the data into existing table.

Command> truncate table t_sql_load;
Command> select count(*) from t_sql_load;
< 0 >
1 row found.
Command> createandloadfromoraquery t_sql_load select level id,
       >                                             level val_1,
       >                                             level val_2,
       >                                             level val_3
       >                                        from dual
       >                                     connect by level <= 100;
Warning  2207: Table ORATT.T_SQL_LOAD already exists
100 rows loaded from oracle.
Command> select count(*) from t_sql_load;                                                                                                    < 100 >
1 row found.
Command>

In this case I've received the 2207 warning. It is very simple :) let's have a look on the second option. Documentation says: "The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on Oracle and loads the result set into the TimesTen table." For example;

Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command>
Command> call ttTableSchemaFromOraQueryGet('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< CREATE TABLE "ORATT"."T_SQL_LOAD" (
"ID" number,
"VAL_1" number,
"VAL_2" number,
"VAL_3" number
 ) >
1 row found.
Command>

ttTableSchemaFromOraQueryGet procedure is telling us which create table statement we should manually execute. Ok, execute the following statement.

Command> CREATE TABLE "ORATT"."T_SQL_LOAD" (
       > "ID" number,
       > "VAL_1" number,
       > "VAL_2" number,
       > "VAL_3" number
       >  );
Command> tables;
  ORATT.T_SQL_LOAD
1 table found.
Command> call ttloadfromoracle ('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< 100 >
1 row found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>

Now, we loaded all necessary information into TimesTen. It is also very easy. The second method is more flexible I think. So, now you can see how easy to load data from Oracle DB into TimesTen using this sql query feature. One more thing. As you now, cache groups in TimesTen have some naming restrictions. For example, you can't create a cache group on a table in Oracle DB which contains symbol "#". This feature helps in this situation as well. For example:

SQL> show user
USER is "ORATT"
SQL> create table tab# as
  2  select level id,
  3         level val_1,
  4         level val_2,
  5         level val_3
  6    from dual
  7  connect by level <= 100;

Table created.

SQL>

In TimesTen:

Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command> createandloadfromoraquery "oratt.tab#" select * from tab#;
Mapping query to this table:
    CREATE TABLE "ORATT"."TAB#" (
    "ID" number,
    "VAL_1" number,
    "VAL_2" number,
    "VAL_3" number
     )

Table tab# created
100 rows loaded from oracle.
Command> tables;
  ORATT.TAB#
1 table found.
Command> select count(*) from tab#;
< 100 >
1 row found.
Command>