TimesTen -- cache group （zz）

1. create dsn

[testdsn]
Driver=/home/tt/TimesTen/abm/lib/libtten.a
DataStore=/home/tt/ttdata/testdsn
LogDir=/home/tt/ttlog
ConnectionCharacterSet=ZHS16GBK
DatabaseCharacterSet=ZHS16GBK
PermSize=128
TempSize=20
LogBuffSize=131072
LogFileSize=128
LogBuffSize=131072
LogFileSize=128
PrivateCommands=1
CkptLogVolume=0
CkptFrequency=300
RecoveryThreads=16
OracleID=GCDB
PassThrough=0
WaitForConnect=0
Connections=100
Authenticate=0
DurableCommits=0
UID=abm2
PWD=abm2
oraclepwd=abm2

2.on oracle create user

create user abm2 identified by abm2;
grant dba to abm2;

3.on oracle create table and insert data

CREATE TABLE readTable (ID NUMBER(5) PRIMARY KEY, NAME VARCHAR2(30));
insert into readTable values(1,'AAA');
commit;

4. on tt

call ttcacheuidpwdset('abm2','abm2');
call ttcachestart;
call ttrepstart;

--create readonly cache group
CREATE READONLY CACHE GROUP cg_readTable
AUTOREFRESH INTERVAL 1 SECONDS
FROM readTable (ID INT PRIMARY KEY, NAME VARCHAR(30));

load cache group cg_readtable commit every 10 rows;
Command> select * from readtable;
< 1, AAA >

5.on oracle insert record

insert into readtable values(2,'AAA');

6.verify the data on tt

Command> select * from readtable;
< 1, AAA >
< 2, AAA >

 

=============带条件的readonly cache group================

1.create table on oracle

create table readTable1 (ID INT PRIMARY KEY, NAME VARCHAR(30));
insert into readTable1 values (1,'aaaa');
insert into readTable1 values (2,'aaaa');
insert into readTable1 values (11,'aaaa');
insert into readTable1 values (12,'aaaa');
commit;

 

2.create readonly cachegroup on tt

Command> CREATE READONLY CACHE GROUP cg_readTable1
>   AUTOREFRESH INTERVAL 1 SECONDS
> FROM readTable1 (ID INT PRIMARY KEY, NAME VARCHAR(30)) where id < 10;
Command>
Command>
Command> select * from readTable1;
0 rows found.
Command>
Command>
Command> load cache group cg_readTable1 commit every 10 rows;
2 cache instances affected.
Command>
Command> commit;
Command> select * from readTable1;
< 1, aaaa >
< 2, aaaa >
2 rows found.

===============给视图建立cache group =========================
1.create view on oracle

create view readtables as
select a.id,b.name from readTable1 a,readTable b where a.id = b.id;

2.create cache group on tt

Command> CREATE READONLY CACHE GROUP cg_readTable2
>   AUTOREFRESH INTERVAL 1 SECONDS
> FROM readtables (ID INT PRIMARY KEY, NAME VARCHAR(30));
5141: Only tables and synonyms to local tables or materialized views can be cached.
The command failed.

------>不对对视图进行cache

 

===========================truacate table 对readonly cache group 影响================

1.truncate on oracle

SQL> truncate table READTABLE;

Table truncated

SQL> select * from READTABLE;

    ID NAME
------ ------------------------------

2.query on tt

Command> select * from READTABLE;
< 1, AAA >
< 2, AAA >
2 rows found.

3.insert record on oracle

Command> select * from READTABLE;
< 1, AAA >
< 2, AAA >
2 rows found.

4.query on tt

Command> select * from READTABLE;
< 1, eee >
< 2, AAA >
2 rows found.

5.insert record on oracle

SQL> insert into readtable values(3,'eee');

1 row inserted

SQL> commit;

6.query on tt

Command> select * from READTABLE;
< 1, eee >
< 2, AAA >
< 3, eee >
3 rows found.

--->在oracle端进行truncate操作不能自动同步到tt，在tt里面进行同样ID的插入还是可以成功，感觉很有问题，对实际的应用会产生不可预计的后果。

 

======================================delete操作对readonly cache group 影响===========

1.delete on oracle
SQL> delete from readtable where id =1;

1 row deleted

SQL> commit;

Commit complete

2.verify on tt

Command> select * from readtable;
< 2, AAA >
< 3, eee >
< 5, eee >
3 rows found.

--->delete 是可以进行同步的。

 

=============对readonly cache group 建立索引============================

1.create index on tt

Command> create index idx_readtable_id on readtable(name);

2.verify index on tt

Command> indexes abm2.%;

Indexes on table ABM2.READTAB:
READTAB: unique T-tree index on columns:
    A
1 index found.

Indexes on table ABM2.READTABLE:
READTABLE: unique T-tree index on columns:
    ID
IDX_READTABLE_ID: non-unique T-tree index on columns:
    NAME
2 indexes found.

--->可以对readonly cache group建立索引

 

===========================给同义词建立cache group======================

1.create synonym on oracle

create or replace synonym readtables for abm2.readtable;
select * from readtable;

2.create cache group on tt

Command> CREATE READONLY CACHE GROUP cg_readTable2
>   AUTOREFRESH INTERVAL 1 SECONDS
> FROM readtables (ID INT PRIMARY KEY, NAME VARCHAR(30));
5142: Autorefresh is not allowed on cache groups with Oracle synonyms
The command failed.

------->不能在同义词上建立自动刷新的cache

 

 

==========================Synchronous Writethrough cache group =====================

1.create table on oracle

create table swttable (id int primary key,name varchar2(30));

2.create cache group on tt

Command> create SYNCHRONOUS WRITETHROUGH cache group cg_swt  
>      from swttable (id int primary key,name varchar2(30));

Cache Group ABM2.CG_SWT:

Cache Group Type: Synchronous Writethrough
Autorefresh: No

Root Table: ABM2.SWTTABLE
Table Type: Propagate

Command> load cache group ABM2.CG_SWT commit every 10 rows;
4 cache instances affected.
Command> commit;
Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
4 rows found.

Command> insert into swttable values(5,'cccc');
1 row inserted.
Command> commit;
Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
< 5, cccc >
5 rows found.

3.verify record on oracle

SQL> select * from swttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      1 BBBB
                                      4 BBBB
                                      2 BBB
                                      3 BBB
                                      5 cccc
                                     

4.insert into record on oracle

SQL> insert into swttable values(6,'ddddd');

1 row inserted

SQL> commit;

Commit complete

5.verify record on tt

Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
< 5, cccc >
5 rows found.

Command> load cache group cg_swt where id =6 commit every 10 rows;
1 cache instance affected.
Command> commit;
Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
< 5, cccc >
< 6, ddddd >
6 rows found.

-------------------->同步CACHE GROUP能够同时对oracle、tt进行操作，在tt端进行的操作能够自动传播到oracle，在oracle进行的操作不能自动刷新到tt，需要手动load。

 

 

 

===========================truacate table 对Synchronous Writethrough cache group 影响================

1.truncate table on oracle

SQL> select * from swttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      1 BBBB
                                      4 BBBB
                                      2 BBB
                                      3 BBB
                                      5 cccc
                                      6 ddddd

6 rows selected

SQL>
SQL> truncate table swttable;

Table truncated

2.verify record on tt

Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
< 5, cccc >
< 6, ddddd >
6 rows found.
Command>
Command> load cache group cg_swt commit every 10 rows;
0 cache instances affected.

------->在oracle端truncate table 不会同步到tt，即使进行load也行。

3.insert record on tt

SQL> insert into swttable values(1,'bbbbb');

1 row inserted

SQL> commit;

4.verify record on tt

Command> load cache group cg_swt where id=1 commit every 10 rows;
0 cache instances affected.
Command> commit;

--->在oracle里面插入的数据跟tt里面一致，load也不会进行同步。

5.insert record on oracle

SQL> insert into swttable values(7,'bbbbb');

1 row inserted

SQL> commit;

Commit complete

6.verify record on tt

Command> load cache group cg_swt commit every 10 rows;
1 cache instance affected.

Command> select * from swttable;
< 1, BBBB >
< 2, BBB >
< 3, BBB >
< 4, BBBB >
< 5, cccc >
< 6, ddddd >
< 7, bbbbb >
7 rows found.

--->如果插入的数据跟tt里面的数据不一致，load是会进行同步的。

 

=========================对Synchronous Writethrough cache group建索引====================

Command> create index idx_swttable_id on swttable(name);

Command> indexes abm2.%;
Indexes on table ABM2.SWTTABLE:
SWTTABLE: unique T-tree index on columns:
    ID
IDX_SWTTABLE_ID: non-unique T-tree index on columns:
    NAME
2 indexes found.

Indexes on table ABM2.T1:
T1: unique T-tree index on columns:
    A
1 index found.

 

 

 

 

 

============================================ASYNCHRONOUS WRITETHROUGH CACHE GROUP====================

1.create table on oracle

create table awttable (id int primary key,name varchar2(30));
insert into awttable values(1,'AAAA');
commit;

2.create awt cache group on tt

create asynchronous writethrough cache group cg_awt
from awttable (id int primary key,name varchar2(30));

load cache group cg_awt commit every 10 rows;
commit;

3.insert record on tt

Command> insert into awttable values(4,'DDD');
1 row inserted.
Command> commit;

4.verify record on oracle

SQL> select * from awttable ;

                                     ID NAME
--------------------------------------- ------------------------------
                                      1 AAAA
                                      2 bbbb
                                      3 cccc
                                      4 DDD
                                     
5.insert record on oracle

SQL> insert into awttable values(5,'dddddd');

1 row inserted

SQL> commit;

Commit complete

6.verify record on tt

Command> select * from awttable;
< 1, AAAA >
< 2, bbbb >
< 3, cccc >
< 4, DDD >
4 rows found.
Command>
Command>
Command>
Command> load cache group cg_awt where id= 5 commit every 10 rows;
1 cache instance affected.
Command> commit;
Command> select * from awttable;
< 1, AAAA >
< 2, bbbb >
< 3, cccc >
< 4, DDD >
< 5, dddddd >
5 rows found.

--------->awt能够在tt、oracle端进行操作，tt端到oracle端的传播是自动的，但是oracle端到tt端的刷新是需要手工进行的
AWT不仅需要启动CACHE AGENT，还需要启动REPLICATION AGENT才能将修改传播到Oracle。

 

================awt 工作原理验证=========================

1.insert record on oracle

SQL> insert into awttable values(7,'rrrrrrrrrr');

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> select * from awttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      5 dddddd
                                      6 dddddd
                                      7 rrrrrrrrrr
                                      1 AAAA
                                      2 bbbb
                                      3 cccc
                                      4 DDD

7 rows selected

2.insert record on tt

Command> insert into awttable values(7,'dddddd');
1 row inserted.
Command> commit;
Command>
Command> select * from awttable;
< 1, AAAA >
< 2, bbbb >
< 3, cccc >
< 4, DDD >
< 5, dddddd >
< 6, dddddd >
< 7, dddddd >
7 rows found.

----------->从以上可以看出awt模式下，可以在tt、oracle里面同时进行操作，而且都可以成功提交。但是在tt、oracle里面的数据是不一致的，该模式对实际生产应用是非常危险的。

 

===================对ASYNCHRONOUS WRITETHROUGH CACHE GROUP建立索引============

Command> create index idx_awttable_id on awttable(name);
Command> indexes abm2.%;
Indexes on table ABM2.AWTTABLE:
AWTTABLE: unique T-tree index on columns:
    ID
IDX_AWTTABLE_ID: non-unique T-tree index on columns:
    NAME
2 indexes found.

===========================truacate table 对对ASYNCHRONOUS Writethrough cache group 影响================

1.truncate table on oracle

SQL> select * from awttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      5 dddddd
                                      6 dddddd
                                      7 rrrrrrrrrr
                                      1 AAAA
                                      2 bbbb
                                      3 cccc
                                      4 DDD

7 rows selected

SQL>
SQL>
SQL> truncate table awttable;

Table truncated

SQL> select * from awttable;

                                     ID NAME
--------------------------------------- ------------------------------

2.verify on tt

Command> select * from awttable;
< 1, AAAA >
< 2, bbbb >
< 3, cccc >
< 4, DDD >
< 5, dddddd >
< 6, dddddd >
< 7, dddddd >
7 rows found.
Command>
Command> load cache group cg_awt commit every 10 rows;
0 cache instances affected.
Command> commit;

---->truncate table 不会刷新到tt，手工进行load也不会刷新。

3.insert record on oracle

SQL> insert into awttable values(1,'BBBBB');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from awttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      1 BBBBB
                                     
4.verify record on tt

Command> load cache group cg_awt commit every 10 rows;
0 cache instances affected.
Command> commit;

--->在oracle里面插入跟tt相同的记录，即使手工load也不会刷新

5.insert record on oracle

SQL> insert into awttable values(8,'BBBBB');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from awttable;

                                     ID NAME
--------------------------------------- ------------------------------
                                      1 BBBBB
                                      8 BBBBB
                                     

6.verify record on tt

Command> load cache group cg_awt commit every 10 rows;
1 cache instance affected.
Command> commit;
Command>
Command> select * from awttable;
< 1, AAAA >
< 2, bbbb >
< 3, cccc >
< 4, DDD >
< 5, dddddd >
< 6, dddddd >
< 7, dddddd >
< 8, BBBBB >
8 rows found.

------>在oracle里面插入的数据跟tt不一致，手工进行load数据是可以刷新到tt。