TimesTen Application Layer Database Cache learning: 19. Understand the three modes of the AWT cache group

Overview

This paper describes the concept and difference of AWT three cache groups, and gives the complete process of 3 kinds of cache groups from establishment to destruction.

The AWT cache group has 3 types:
1. AWT default (manually load)
2. AWT Dynamic
3. AWT Dynamic globle (Cache Grid)

Various AWT types differ by AWT default (manually load)

• inserted/updated/deleted data in TimesTen to Oracle
• New data in Oracle is synced to TimesTen via "LOAD CACHE GROUP"
• If a table is cached to two AWT default cache group, there is no mutual knowledge between cache groups, so a single cache instance can exist in two cache groups simultaneously

Grammar:
Create asynchronous Writethrough Cache group T1_awt_reg
from T1 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));

AWT Dynamic

• inserted/updated/deleted data in TimesTen to Oracle
• New data in Oracle is synced to TimesTen via "LOAD CACHE GROUP"
• New data in Oracle can also be dynamically loaded with Select, UPDATE, and DELETE statements
• If a table is cached to two AWT default cache group, there is no mutual knowledge between cache groups, so a single cache instance can exist in two cache groups simultaneously

Grammar:
Create dynamic asynchronous Writethrough cache group T2_awt_dyn
from T1 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));

AWT Dynamic globle (Cache Grid)

• inserted/updated/deleted data in TimesTen to Oracle
• New data in Oracle is synced to TimesTen via "LOAD CACHE GROUP"
• New data in Oracle can also be dynamically loaded with Select, UPDATE, and DELETE statements
• If a table is cached to two AWT Dynamic globle cache group, a cache instance can only exist in one cache group due to the mutual communication between buffer groups

Grammar:
Create dynamic asynchronous Writethrough Global cache group T3_awt_dyn_gbl
from T1 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));

Experiment section Creating tables in Oracle

$ sqlplus Tthr/[email protected]
CREATE TABLE T1 (c1 number) NOT NULL primary key, C2 date, C3 varchar (40));
CREATE TABLE T2 (c1 number) NOT NULL primary key, C2 date, C3 varchar (40));
CREATE TABLE t3 (c1 number) NOT NULL primary key, C2 date, C3 varchar (40));

Create DSN

[CACHEDB1]
Driver=/home/oracle/timesten/tt1122/lib/libtten.so
Datastore=/home/oracle/timesten/tt1122/info/demodatastore/cachedb1
Permsize=32
Tempsize=64
Logfilesize=32
Logbufmb=32
Databasecharacterset=al32utf8
Oraclenetservicename=ttorcl

[CACHEDB2]
Driver=/home/oracle/timesten/tt1122/lib/libtten.so
Datastore=/home/oracle/timesten/tt1122/info/demodatastore/cachedb2
Permsize=32
Tempsize=64
Logfilesize=32
Logbufmb=32
Databasecharacterset=al32utf8
Oraclenetservicename=ttorcl

Create user

Also executed in CACHEDB1 and CACHEDB2:
Create user tthr identified by TimesTen;
Grant Admin, create session, Cache_manager, create any table to Tthr;

Create cache group, cache grid, and associate to Grid

Also executed in CACHEDB1 and CACHEDB2:
TTIsq L?v1?e"seTPRomPT'CaChed b1>'""d sN=CaChed b1;uId =TThR;PWd =TImesTeN;oRaCLePWd =oRaCLe" Ttisql-v1-e "set prompt ' Cachedb2> '" "Dsn=cachedb2;uid=tthr;pwd=timesten;oraclepwd=oracle"

Call Ttcacheuidpwdset (' Cacheadm ', ' Oracle ');
Call Ttcachestart;
Call Ttgriddestroy (' Samplegrid ', 1); <-This command is very useful
Call Ttgridnodestatus (' Samplegrid ');
Call Ttgridcreate (' Samplegrid '); <-can be executed once in any TimesTen database
Call Ttgridinfo (' Samplegrid ');
Call Ttgridnameset (' Samplegrid ');
Call Ttgridinfo (' Samplegrid ');
Call Ttgridnodestatus (' Samplegrid ');

Three table distributions correspond to regular, dynamic, dynamic global cache group
Create asynchronous Writethrough Cache group T1_AWT
from T1 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));
Create dynamic asynchronous Writethrough cache group T2_awt_dyn
From T2 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));
Create dynamic asynchronous Writethrough Global cache group T3_awt_dyn_gbl
from T3 (C1 number (a) Not null primary key, C2 date, C3 varchar (40));

Cachedb1>
Call Ttgridattach (1, ' member1 ', ' 127.0.0.1 ', 5001);
Call Ttgridnodestatus (' Samplegrid ');
Call Ttrepstart;

Cachedb2>
Call Ttgridattach (1, ' member2 ', ' 127.0.0.1 ', 5002); <-uses a different port because two TimesTen databases are on the same host
Call Ttgridnodestatus (' Samplegrid ');
Call Ttrepstart;

In two databases, insert data for each cache group

Cachedb1>
INSERT into T1 values (1, sysdate, ' T1 data ');
INSERT into T2 values (1, sysdate, ' T2 data ');
INSERT into T3 values (1, sysdate, ' t3 data ');
Unload Cache group T1_AWT;
Unload Cache group T2_awt_dyn;
Unload Cache group T3_awt_dyn_gbl; <-unload cannot see cached data in TimesTen

Cachedb2>
INSERT into T1 values (2, sysdate, ' T1 data ');
INSERT into T2 values (2, sysdate, ' T2 data ');
INSERT into T3 values (2, sysdate, ' t3 data ');
Unload Cache group T1_AWT;
Unload Cache group T2_awt_dyn;
Unload Cache group T3_awt_dyn_gbl; <-unload cannot see cached data in TimesTen

Manually load data from Oracle

Cachedb1>
Load Cache group T1_AWT where C1 = 1 commit every rows parallel 10;
Load Cache group T2_awt_dyn where C1 = 1 commit every rows parallel 10;
Load Cache group T3_AWT_DYN_GBL where C1 = 1 commit every rows parallel 10;
SELECT * from T1;
select * from T2;
select * from T3;
Unload Cache group T1_AWT;
Unload Cache group T2_awt_dyn;
Unload Cache group T3_awt_dyn_gbl;

Cachedb2>
Load Cache group T1_AWT where C1 = 2 commit every rows parallel 10;
Load Cache group T2_awt_dyn where C1 = 2 commit every rows parallel 10;
Load Cache group T3_AWT_DYN_GBL where C1 = 2 commit every rows parallel 10;
SELECT * from T1;
select * from T2;
select * from T3;
Unload Cache group T1_AWT;
Unload Cache group T2_awt_dyn;
Unload Cache group T3_awt_dyn_gbl;

Dynamically load data from Oracle via SQL

Cachedb1>
SELECT * from t1 where c1 = 1;
SELECT * FROM t2 where c1 = 1;
SELECT * from t3 where c1 = 1;
SELECT * from T1;
select * from T2;
select * from T3;

Output:
Cachedb1> select * from T1; <-no output, because manual load is required
Cachedb1> select * from T2; <-has output because the dynamic load condition is satisfied
< 1, 2016-06-19 22:41:56, t2 data >
Cachedb1> select * from T3; <-has output because the dynamic load condition is satisfied
< 1, 2016-06-19 22:41:57, T3 data >

Cachedb2>
SELECT * from t1 where c1 = 2;
SELECT * FROM t2 where c1 = 2;
SELECT * from t3 where c1 = 2;
SELECT * from T1;
select * from T2;
select * from T3;

Output:
Cachedb2> select * from T1; <-no output, because manual load is required
Cachedb2> select * from T2; <-has output because the dynamic load condition is satisfied
< 2, 2016-06-19 22:45:12, t2 data >
Cachedb2> select * from T3; <-has output because the dynamic load condition is satisfied
< 2, 2016-06-19 22:46:07, T3 data >

Dynamically load data from Oracle or cache grid via SQL

Cachedb1>
SELECT * from t1 where c1 = 2;
SELECT * FROM t2 where c1 = 2;
SELECT * from t3 where c1 = 2;
SELECT * from T1;
select * from T2;
select * from T3;

Output:
Cachedb1> select * from T1;
Cachedb1> select * from T2;
< 1, 2016-06-19 22:41:56, t2 data >
< 2, 2016-06-19 22:45:12, t2 data > <-This is from dynamic load from Oracle
Cachedb1> select * from T3;
< 1, 2016-06-19 22:41:57, T3 data >
< 2, 2016-06-19 22:46:07, T3 data > <-This is load from another member:cachedb2 in the cache grid

Cachedb2>
SELECT * from t1 where c1 = 1;
SELECT * FROM t2 where c1 = 1;
SELECT * from t3 where c1 = 1;
SELECT * from T1;
select * from T2;
select * from T3;

Output:
Cachedb2> select * from T1;
Cachedb2> select * from T2;
< 1, 2016-06-19 22:41:56, t2 data > <-for ordinary dynamic AWT, because of non-knowledge, so these two data in two TimesTen database exist
< 2, 2016-06-19 22:45:12, t2 data > <-This is from dynamic load from Oracle
Cachedb2> select * from T3;
< 1, 2016-06-19 22:41:57, T3 data > <-This is load from another member:cachedb1 in the cache grid
Cachedb1> select * from T3;
< 2, 2016-06-19 22:46:07, T3 data > <-for Global AWT, the cache instance will only appear in one TimesTen

Delete a cache group and detach from a grid

Cachedb1>
Call Ttrepstop;
Call Ttgriddetach;
Drop Cache group T1_AWT;
Drop Cache group T2_awt_dyn;
Drop Cache group T3_awt_dyn_gbl;
Call Ttcachestop;

Cachedb2>
Call Ttrepstop;
Call Ttgriddetach;
Drop Cache group T1_AWT;
Drop Cache group T2_awt_dyn;
Drop Cache group T3_awt_dyn_gbl;
Call Ttcachestop;
Call Ttgriddestroy (' Samplegrid ', 1);

Reference

Howto:understand the three fundamental Types of TimesTen asynchronous (AWT) Cache Groups (Doc ID 1471954.1)

TimesTen Application Layer Database Cache learning: 19. Understand the three modes of the AWT cache group