Task 3: Create a Static Read-Only Cache Group on the TimesTen Database

Create a static read-only cache group with autorefresh on the TimesTen database.

1. As the TimesTen cache administration user, create a static read-only cache group on a TimesTen database with the CREATE READONLY CACHE GROUP SQL statement. Use the unique index columns as the primary key definition.

   The following example shows how the sales customers and orders tables on the Oracle database will be cached in a cache group called customer_orders.

   % ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
   Command> CREATE READONLY CACHE GROUP customer_orders
   AUTOREFRESH
   FROM sales.customers
    (cust_num NUMBER(6) NOT NULL,
     region   VARCHAR2(10),
     name     VARCHAR2(50),
     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.customers(cust_num));

   Note:

   This SQL statement creates the cache group and the cache tables on the TimesTen database. Autorefresh is added by default for read-only cache groups. Autorefresh defaults to incremental autorefresh running every 5 minutes.

   When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle Database tables and choose an equivalent or compatible data type for the columns in the cache tables. See Mappings Between Oracle Database and TimesTen Data Types

2. Exit the ttIsql utility that is connected as the TimesTen cache administration user. Then, restart the ttIsql utility and connect to the cache1 DSN as the instance administrator.

   Grant the SELECT privilege on the sales.customers and sales.orders cache tables to the TimesTen cache administration user so that this user can issue SELECT queries on these tables.

   Command> exit;
   Disconnecting...
   Done.
   % ttIsql cache1
   Command> GRANT SELECT ON sales.customers TO cacheadmin;
   Command> GRANT SELECT ON sales.orders TO cacheadmin;
   Command> exit;
   Disconnecting...
   Done.