1. Cache Guide
  2. Methods for Transmitting Changes Between TimesTen and Oracle Databases
  3. Dynamic Cache Groups
  4. Examples of Dynamic Load of a Single Cache Instance

Examples of Dynamic Load of a Single Cache Instance

Provides an example that defines Oracle database tables, which are then cached into a dynamic AWT cache group.

The following is the definition of the Oracle Database tables that are to be cached in a dynamic AWT cache group. 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));

CREATE TABLE orders
(ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
 cust_num     NUMBER(6) NOT NULL,
 when_placed  DATE NOT NULL,
 when_shipped DATE NOT NULL);

CREATE TABLE orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY (orderid, itemid));

For example, the following data is 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. Then, run the following statement to create a dynamic AWT cache group new_customers that caches the sales.customer, sales.orders, and sales.orderdetails tables: 

CREATE DYNAMIC 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)),
sales.orders
 (ord_num      NUMBER(10) NOT NULL,
  cust_num     NUMBER(6) NOT NULL,
  when_placed  DATE NOT NULL,
  when_shipped DATE NOT NULL,
  PRIMARY KEY(ord_num),
  FOREIGN KEY(cust_num) REFERENCES sales.customer(cust_num)),
sales.orderdetails
 (orderid  NUMBER(10) NOT NULL,
  itemid   NUMBER(8) NOT NULL,
  quantity NUMBER(4) NOT NULL,
  PRIMARY KEY(orderid, itemid),
  FOREIGN KEY(orderid) REFERENCES sales.orders(order_num));

The following examples show the default behavior as DynamicLoadEnable defaults to 1:

The sales.customer TimesTen cache table is initially empty: 

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

The following SELECT statement with an equality condition on the primary key for the sales.customer table results in a dynamic load of a single cache instance: 

Command> SELECT * FROM sales.customer WHERE cust_num = 1;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >

If you do not use an equality condition on the primary key and you do not configure for dynamic load of multiple cache instances, then no dynamic load occurs for this example, since it would result in multiple cache instances. See Dynamically Loading Multiple Cache Instances for details on how to configure for this scenario. 

Command> SELECT * FROM sales.customer WHERE cust_num IN (1,2);

The following example contains equality expressions on all of the primary key columns for a primary key composite. The orderdetails table has a composite primary key of orderid and itemid. 

UPDATE sales.orderdetails SET quantity = 5 WHERE orderid=2280 AND itemid=663;

The following example shows an INSERT into the orders child table, which initiates a dynamic load. However, if you tried to insert into the customer table, which is the parent, no dynamic load occurs. 

INSERT INTO orders VALUES(1,1, DATE '2012-01-25', DATE '2012-01-30');

The following UPDATE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, updates the instance in the cache table, and then automatically propagates the update to the cached Oracle Database table: 

Command> UPDATE sales.customer SET name = 'Angela Peterson' WHERE cust_num = 2;
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated 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 Peterson   356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following DELETE statement dynamically loads one cache instance from the cached Oracle Database table into the TimesTen cache table, deletes the instance from the cache table, and then automatically propagates the delete to the cached Oracle Database table: 

Command> DELETE FROM sales.customer WHERE cust_num = 3;
Command> SELECT * FROM sales.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated 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 Peterson   356 Olive St., Boston MA

The following is an example of a dynamic load performed using all columns of a unique index on the root table. The departments table is defined in a dynamic AWT cache group. A unique index is created on this cache group consisting of the manager_id and location_id.

The following creates the departments table on the Oracle database.

Command> CREATE TABLE departments(
        department_id INT NOT NULL PRIMARY KEY,
        department_name VARCHAR(10) NOT NULL,
        technical_lead INT NOT NULL,
        manager_id INT,
        location_id INT NOT NULL);

The following creates the dynamic AWT cache group and a unique index on the dept_cg root table: 

Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP dept_cg
        FROM departments
        (department_id INT NOT NULL PRIMARY KEY, 
         department_name VARCHAR(10) NOT NULL, 
         technical_lead INT NOT NULL, 
         manager_id INT, location_id INT NOT NULL);

Command> CREATE UNIQUE INDEX dept_idx 
         ON departments
         (manager_id, 
          location_id);

The following inserts three records into the departments table on the Oracle database:

Command> INSERT INTO departments 
              VALUES (1, 'acct', 1, 1, 100);
1 row inserted.
Command> INSERT INTO departments 
          VALUES (2, 'hr', 2, 2, 200);
1 row inserted.
Command> INSERT INTO departments 
          VALUES (3, 'owner', 3, NULL, 300);
1 row inserted.
Command> commit;

On TimesTen, dynamically load a cache instance based on the unique index:

Command> SELECT * FROM departments;
0 rows found.
Command> SELECT * FROM departments 
           WHERE manager_id IS NULL AND location_id=300;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments;
< 3, owner, 3, <NULL>, 300 >
1 row found.
Command> SELECT * FROM departments 
          WHERE manager_id=2 AND location_id=200;
< 2, legal, 2, 2, 200 >
1 row found.
Command> SELECT * FROM departments;
< 2, legal, 2, 2, 200 >
< 3, owner, 3, <NULL>, 300 >
2 rows found.