Example of Manually Loading and Refreshing a Static Cache Group

This example shows the definition of an Oracle Database table that is to be cached in a static AWT cache group.

On the Oracle database:

The Oracle Database table is owned by the schema user sales.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));

The following is the data in the sales.customer cached Oracle Database table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

On the TimesTen database, connect as the TimesTen cache administration user. The following statement creates a static AWT cache group new_customers that caches the sales.customer table:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num));

The sales.customer TimesTen cache table is initially empty.

Command> SELECT * FROM sales.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle Database table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

On the Oracle database, modify the cached Oracle Database table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

Back on the TimesTen database as the TimesTen cache administration, run a REFRESH CACHE GROUP statement on a static cache group, which is processed by unloading and then reloading the cache group. As a result, the cache instances in the cache table matches the rows in the cached Oracle Database table.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instance affected.
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >