How To Create Cache Group in TimesTen

Cache groups are used to cache oracle database data in TimesTen in-memory system.
You can either cache the whole table, or part of it by defining the columns.
You cannot cache LOB datatypes as of now in TimesTen, the biggest available chumk is LONG RAW.
You can create a cache group of multiple tables or single table as per your requirement.
If you have multiple tables in a cache group you can define parent child relations.
Though the tables in a cache group must be joined by foreign key constraints in TimesTen,
the tables do not necessarily need to be joined in the Oracle instance .
There can be different types of cache group configurations, depending upon the requirements.

Types of cache groups:

-->READONLY - enforces a caching behavior in which updates on Oracle tables are applied to TimesTen through the AUTOREFRESH mechanism.
-->SYNCHRONOUS WRITETHROUGH - enforces a caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to a SWT cache group are committed synchronously.
-->ASYNCHRONOUS WRITETHROUGH (AWT) cache groups - enforces caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to an AWT cache group are committed asynchronously.
-->USERMANAGED - Customizable/user defined cache group.

Setting up Oracle:
Create the schema to be used for caching. I am using the oracle provide "HR" sample schema on the oracle side.

Setting up TimesTen:
1.) Create user on TimeTen:
oracle@oracle:~/TimesTen/timesten/bin$ ./ttIsql tt_timesten

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=tt_timesten";
Connection successful: DSN=TT_timesten;UID=oracle;DataStore=/home/oracle/TimesTen/timesten/info/TT_timesten;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)

Command> create user hr identified by 'hr';
Command> grant admin,ddl to hr;
Command> exit
Disconnecting...
Done.

2.) Create TimesTen DSN:
Need to add the entry of the dsn in the sys.odbc.ini file on the TimesTen server and sys.ttconnect.ini on the TimesTen client.
The file is located at $TIMESTEN_HOME/info
Parameters to be defined:
[myDSN]
Driver=/TimesTen/timesten/lib/libtten.so ##See the default one in the file and use the same path.
Datastore=/TimesTen/data ##Defined the place where TimesTen can store the cache data.
PermSize=16 ##Defines permanent size of the data store.
TempSize=16 ##Temporary size
UID=hr ##TimesTen user you created.
OracleId=hr ##DB name/tnsnames.ora entry.
OraclePwd=hr ##Oracle password
DatabaseCharacterSet=AL32UTF8 ##Should be same as the oracle database

oracle@oracle:~/TimesTen$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Mar 10 14:18:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8

3.) Create oracle tables:
As we are using the HR schema, we will use the following tables:
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

4.) Create the cache group:

VERY IMPORTANT THING TO REMEMBER:
Make sure that your environment has the correct location of the TNS_ADMIN variable. That means it points to the correct tnsnames.ora.
In my case I had both 10g and 11g installed and had a problem with the location and was receiving the following error(ORA-12154):
5220: Permanent Oracle connection failure error in OCIServerAttach(): ORA-12154: TNS:could not resolve the connect identifier specified rc = -1
5935: Could not validate Oracle login: uid = hr, pwd = HIDDEN, oracleId = hr

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> call ttCacheUidPwdSet('hr','hr');

Command> call ttCacheStart;

Command> CREATE READONLY CACHE GROUP EMP_IN_MEM
> AUTOREFRESH INTERVAL 5 SECONDS
> FROM EMPLOYEES
> (EMPLOYEE_ID NUMBER(6) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR2(20),LAST_NAME VARCHAR2(25) NOT NULL,EMAIL VARCHAR2(25) NOT NULL);

Command> LOAD CACHE GROUP EMP_IN_MEM COMMIT EVERY 256 ROWS;
107 cache instances affected.

Command> cachegroups;

Cache Group HR.EMP_IN_MEM:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: HR.EMPLOYEES
Table Type: Read Only

1 cache group found.

Command> select count(*) from EMPLOYEES;
107
1 row found.
Command> select * from EMPLOYEES;
100, Steven, King, SKING
101, Neena, Kochhar, NKOCHHAR
102, Lex, De Haan, LDEHAAN
103, Alexander, Hunold, AHUNOLD
104, Bruce, Ernst, BERNST
...

So we were successfully able to create a read only cache group.

To test further, lets add data in the oracle database, as of now the table has 107 rows.

SQL> insert into employees values (500,'Apun','Hiran','me@me.com','1111111111','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> insert into employees values (501,'Rick','Smith','ricksmith@me.com','1234567891','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> commit;
Commit complete.

We added 2 rows. Lets see if the record count in TimesTen has changed.

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> select count(*) from EMPLOYEES;
109
1 row found.

Command> select * from EMPLOYEES where EMPLOYEE_ID in (500,501);
500, Apun, Hiran, me@me.com
501, Rick, Smith, ricksmith@me.com

2 rows found.
Command>

This completed the testing for our cache group!!!