How To: Creation fo Cache Group Using Cache Administrator:

http://localhost:17004/cache/
Login Page:

From Timesten

In our case:
TimesTen DSN: myDSN
Oracle Username: hr
Oracle Password: hr
TimesTen Password: hr

Welcome Page:

From Timesten

Select Create under Create Cache Group Definitions.

Shows the creation Page:

From Timesten

We will be creating a User Managed Cache Group Definition.

From Timesten

We select User Managed, propogate all tables (from the attributes section), Autorefresh every 5 seconds(from the autorefresh definitions).
Our Root table would be HR. REGIONS, child tables would be HR.COUNTRIES and HR.LOCATIONS.

Edit the table setting, by selecting the columns we need and defining the foreign key relations in the tables:

From Timesten

Defining foreign key for the table countries and regions:

From Timesten

Select required columns and attributes for the countries table:

From Timesten

Define foreign key relation between countries and locations table:

From Timesten

From Timesten

Define required columns and attributes for the locations table:

From Timesten

Press Save definition and it will take you to the Definition Summary Page:

From Timesten

Now click on create under the Cache Group section in the menu on the left:

From Timesten

Press Create Cache Group:

From Timesten

This will create the cache group called OUR_TEST.

Below is the SQL to create the same group manually (ttIsql myDSN):

SQL Code for creating the same user managed cache group:

CREATE USERMANAGED CACHE GROUP "OUR_TEST"
AUTOREFRESH MODE INCREMENTAL INTERVAL 5 SECONDS
STATE ON
FROM
"HR"."REGIONS" (
"REGION_ID" NUMBER NOT NULL,
"REGION_NAME" VARCHAR2(25 BYTE),
PRIMARY KEY("REGION_ID"), PROPAGATE
),
"HR"."COUNTRIES" (
"COUNTRY_ID" CHAR(2 BYTE) NOT NULL,
"COUNTRY_NAME" VARCHAR2(40 BYTE),
"REGION_ID" NUMBER,
PRIMARY KEY("COUNTRY_ID"),
FOREIGN KEY("REGION_ID")
REFERENCES "HR"."REGIONS"("REGION_ID"), PROPAGATE
),
"HR"."LOCATIONS" (
"LOCATION_ID" NUMBER(4) NOT NULL,
"STREET_ADDRESS" VARCHAR2(40 BYTE),
"POSTAL_CODE" VARCHAR2(12 BYTE),
"CITY" VARCHAR2(30 BYTE) NOT NULL,
"STATE_PROVINCE" VARCHAR2(25 BYTE),
"COUNTRY_ID" CHAR(2 BYTE),
PRIMARY KEY("LOCATION_ID"),
FOREIGN KEY("COUNTRY_ID")
REFERENCES "HR"."COUNTRIES"("COUNTRY_ID"), PROPAGATE
);

Lets check the cache group we created using the cache administrator.

oracle@oracle:~/TimesTen/timesten/startup$ 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> 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

Cache Group HR.OUR_TEST:

Cache Group Type: User Managed
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Minutes

Root Table: HR.REGIONS
Table Type: Propagate

Child Table: HR.COUNTRIES
Table Type: Propagate

Child Table: HR.LOCATIONS
Table Type: Propagate

2 cache groups found.
Command>
Lets check the record count:
Command> select * from regions;
0 rows found.
Oops no rows show up....we still need to create a cache agent.

Command> load cache group our_test commit every 20 rows;
8289: Manual LOAD/REFRESH of cache group requires AUTOREFRESH STATE to be PAUSED, but the STATE of HR.OUR_TEST is ON. The LOAD is not allowed
The command failed.
Command>

We need to use ttAdmin to create a cache agent, this is to set the password for the cache agent:

oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheUidPwdSet -cacheUid hr -cachePwd hr myDSN
Enter password for 'hr':
Cache User Id : hr
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : False

Now start the cache agent:
oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheStart myDSN
Enter password for 'hr':
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : True

Now lets check the record count again.

oracle@oracle:~/TimesTen/timesten/bin$ 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 * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
4 rows found.

We got the rows this time!!!!
Check the Cache agent policy, by default its manual that means the agent will not start automatically when the timesten daemon restarts.
Command> call ttCachePolicyGet;
manual
1 row found.

Lets change it to auto start:
Command> call ttCachePolicySet ('always');
Command> call ttCachePolicyGet;
always
1 row found.

Lets check the status of our TimesTen daemon:
oracle@oracle:~/TimesTen/timesten/bin$ ttstatus
TimesTen status report as of Mon Mar 16 09:51:10 2009

Daemon pid 6427 port 17000 instance timesten
TimesTen server pid 6436 started on port 17002
TimesTen webserver pid 6434 started on port 17004

------------------------------------------------------------------------
Data store /home/oracle/TimesTen/data
There are 11 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x4d0148ad ID 2752530
Type PID Context Connection Name ConnID
Cache Agent 7276 0x081e2758 Handler 2
Cache Agent 7276 0x0826b710 Timer 3
Cache Agent 7276 0x082dce30 Aging 4
Cache Agent 7276 0x0839d710 timestenorad 6
Cache Agent 7276 0x0841b1e0 timestenorad 7
Subdaemon 6431 0x08098e38 Worker 2042
Subdaemon 6431 0x0810f020 HistGC 2046
Subdaemon 6431 0x0816e4c0 Checkpoint 2044
Subdaemon 6431 0x081ad950 Flusher 2043
Subdaemon 6431 0x0820cdf0 Aging 2045
Subdaemon 6431 0x0824c280 Monitor 2047
Replication policy : Manual
Cache agent policy : Always
TimesTen's Cache agent is running for this data store
------------------------------------------------------------------------
Data store /home/oracle/TimesTen/timesten/info/TT_timesten
There are no connections to the data store
Replication policy : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
oracle@oracle:~/TimesTen/timesten/bin$

To check the success of our configuration, lets try adding rows:
Adding row from oracle:
SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> insert into regions values (5,'Antartica');
1 row created.
SQL> commit;
Commit complete.

Checking Timesten:

oracle@oracle:~$ 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 * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
5, Antartica
5 rows found.

Command>

Row updated.

Now adding row from TimeTen:
Command> insert into regions values (6,'Artic');
1 row inserted.

Checking Oracle DB:
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
6 rows selected.

Working both ways. Now lets check the foreign key definitions we made:

Command> insert into countries values ('AT','MYCOUNTRY',10);
3001: Foreign key violation [REGIONS]: the row in child table HR.COUNTRIES has no key in the parent table HR.REGIONS
The command failed.

Well the definitions works well!!!!