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