Tuesday, March 10, 2009

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!!!


10 Comments:

adeel said...

HEY GREAT EFFORT BUT I HAVE A question to you if our table contain 92 col mean above than 50 it does not sshow data?? i have face a problem just like this i have a table with 92 col but i select some of colum 5 or 10 create cache group succefully. all col shows under table but no record i have found. by using same DNS just changing the name of user HR create cache group all data show. i think its is just becoz or col or my table also partition i dont know time ten support partition or not plz help reply me ASAP

Apun Hiran said...

Hello Adeel,
There are a few more steps that need to be done after creating the cache groups, Like starting the refresh activity. Timesten supports partitioned tables.
Check the post:
http://apunhiran.blogspot.com/2009/03/how-to-creation-fo-cache-group-using.html
On this page start looking from the line "Lets check the cache group we created using the cache administrator." There you would see steps to enable refresh of data in the newly created refresh groups.
Regards
Apun

Alma said...

How did you solve the TNS_ADMIN variable problem?!
I'm trying to figure that one out for ages... I'm trying to configure TT cache for Oracle 10g on WinXP, and I'm so stuck at this ora-12154 thing :(
Hoping to see your answer soon...
Thanks

Apun Hiran said...

Hello Alma,
Have you set the TNS_ADMIN global variable in your windows XP machine. Set the value to the path of your tnsnames.ora file and check if that works.
Regards
Apun

adeel said...

i tried times ten after 6 month again i have found same problem how to display more 50 columns table in time ten

Apun Hiran said...

Hello Adeel,
I am not able to reproduce this problem. According to the documentation Timesten supports a lot of columns. I think in the range of 1000. Could you please check the data type of the columns you are trying to cache. Please let me know if there is any specific error message that you are getting. Also you may try to raise a Service Request on metalink.
Regards
Apun

Alma said...

Hello AH,

I solved it! I'm kind of ashamed to say what my problem was.
I was making a mistake in my Oracle ID (I think it was xe, and I used orcl), but figured that out when I tried to install oracle 10g again, after I uninstalled it...

Thanks for your quick reply! I found your blog to be very useful.
Best regards

Apun Hiran said...

Hello Alma,
Nice to hear that your problem has resolved. Well yes the default database created by Oracle Express ediction is XE and not the popluar ORCL. :-)
Thanks
Apun

Sriram said...

Hi,

I'm using TimesTen 7.0 for cacheconnect where it is very mandatory to set TypeMode=0. Even if i do not set TypeMode=0, by default, IMHO, TypeMode=0. Hence it is possible to cache the Oracle table data to TimesTen.

However, the application which I use requires TypeMode to be set to 1.

The problem which I face is that, I cannot set TypeMode=0 for Cache connect and TypeMode=1 for my application. How is it possible to rectify this issue?

Do I need to do work around, by setting TypeMode=0 for cache connect and write my own solution, my solution being something similar to what TypeMode=1 does?

P.S: The application only queries a table (cached using cache connect) and returns a value if there is a match in the table. If I don't use TypeMode=1, my application throws me an error, "Unsupported Field Type". If I don't use TypeMode=0, Cache connect won't work in TimesTen 7. What could be the issue and is there any possible solution or help which can be offered for this problem?

Thanks in advance.

Sriram said...

Hi,

I'm using TimesTen 7.0 for cacheconnect where it is very mandatory to set TypeMode=0. Even if i do not set TypeMode=0, by default, IMHO, TypeMode=0. Hence it is possible to cache the Oracle table data to TimesTen.

However, the application which I use requires TypeMode to be set to 1.

The problem which I face is that, I cannot set TypeMode=0 for Cache connect and TypeMode=1 for my application. How is it possible to rectify this issue?

Do I need to do work around, by setting TypeMode=0 for cache connect and write my own solution, my solution being something similar to what TypeMode=1 does?

P.S: The application only queries a table (cached using cache connect) and returns a value if there is a match in the table. If I don't use TypeMode=1, my application throws me an error, "Unsupported Field Type". If I don't use TypeMode=0, Cache connect won't work in TimesTen 7. What could be the issue and is there any possible solution or help which can be offered for this problem?

Thanks in advance.