Create Static Asynchronous WriteThrough Cache Groups

8 Create Static Asynchronous WriteThrough Cache Groups

An Asynchronous WriteThrough (AWT) cache group enforces a caching behavior where committed changes on the TimesTen cache tables are automatically and asynchronously propagated to the cached Oracle Database tables to keep these tables in the two databases synchronized.

Description of qasyncwritethroughcg.eps follows

Description of the illustration qasyncwritethroughcg.eps

The transaction commit on the TimesTen database occurs asynchronously from the commit on the Oracle database. This enables an application to continue issuing transactions on the TimesTen database without waiting for the Oracle database transactions to complete. When the changes resulting from the transactions in TimesTen are propagated to the Oracle database, transaction integrity and ordering is preserved. Transactions that have committed in TimesTen but have not yet made it to the Oracle database are protected by the TimesTen persistence mechanisms and, optionally, TimesTen high availability (if configured).

Since the AWT cache group uses the replication agent to asynchronously propagate transactions to the Oracle database, these transactions remain in the transaction log buffer and transaction log files until the replication agent confirms they have been fully processed by the Oracle database.

What happens if the data is modified on the Oracle database? Since data automatically flows from TimesTen cache to the Oracle database, any data changed in the cached Oracle database tables is not automatically propagated to the TimesTen cache. Changes propagated from the cache may overwrite changes made directly in the Oracle database. For tables that are cached using AWT, TimesTen should be considered the primary source where the application makes changes to the data. It is possible to refresh the AWT cache tables in TimesTen by using the REFRESH CACHE GROUP statement, which discards the table contents in TimesTen and repopulates the tables from the Oracle database.

Create Static Asynchronous WriteThrough Cache Group

The following graphic shows the steps necessary to create a static AWT cache group:

Description of the illustration createawtcg.eps

These steps are covered in the following sections:

After creating the static AWT cache group, you can perform optional tasks to verify that the cache group is performing as expected or to drop the cache groups to start over.

Optional Task	Description
Verify Static AWT Cache Group	Once the AWT cache group is created and loaded, you can verify the cache group and its data.
Drop the Cache Groups in the TimesTen and Oracle Databases	If you decide that you want to create another cache group in this guide, you can drop the cache group used in this section as it is used in the other sections.

Task 1: Identify the Schema on the Oracle Database

On the Oracle database, use SQL*Plus to:

Identify Oracle Database tables to cache.

Since the cache tables are based on the tables you want to cache in the Oracle Database, identify the Oracle Database tables that are to be cached.

Each table should be either:
- An Oracle Database table with a primary key on non-nullable columns. The TimesTen cache table primary key must be defined on the full Oracle Database table primary key.
- An Oracle Database table with non-nullable columns upon which a unique index is defined on one or more of the non-nullable columns in the table. The TimesTen cache table primary key must be defined on all of the columns in the unique index.
For example, you decide to cache the sales.customers table on the Oracle database. Then, note that the definition of the customers tables is:
```
CREATE TABLE sales.customers
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100));
Table created.
```
Since these tables are going to be cached in a read-write cache group, grant the SELECT, INSERT, UPDATE and DELETE privileges on the customer table to the Oracle cache administration user:
```
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customers TO cacheadmin;
```

Task 2: Start the Cache Agent

Start the ttIsql utility and connect to the cache1 DSN as the TimesTen cache administration user, including the TimesTen cache administration user and its credentials in an Oracle Wallet.

One of the most frequently used TimesTen utilities is the ttIsql utility. This is an interactive SQL utility that serves the same purpose for TimesTen as SQL*Plus does for Oracle Database.

```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
```
See Connect Using an Oracle Wallet with Credentials for directions on how to create an Oracle Wallet.
Start the cache agent. The cache agent is a TimesTen daemon process that manages many of the cache-related functions for a TimesTen database.
```
call ttCacheStart;
```

Task 3: Stop the Replication Agent

Stop the replication agent. The replication agent is a TimesTen daemon process that propagates committed changes on TimesTen cache tables in AWT cache groups to the cached Oracle Database tables.

CALL ttRepStop;

Task 4: Create the Static AWT Cache Group on the TimesTen Database

Create a static AWT cache group.

As the TimesTen cache administration user, create a static AWT cache group with the CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP SQL statement. Use the unique index columns as the primary key definition.

The following statement creates an AWT cache group awt_customers that caches the sales.customers table:

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

Note:

This SQL statement creates the cache group and the cache tables on the TimesTen database.

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

Task 5: Start the Replication Agent

Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups.

Running a CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement creates a replication scheme that enables committed changes on the TimesTen cache tables to be asynchronously propagated to the cached Oracle Database tables.

After you have created AWT cache groups, start the replication agent on the TimesTen database by calling the ttRepStart built-in procedure as the cache administration user.

Note:

The ttRepStart connects to the Oracle database using the credentials that were registered with the ttCacheUidPwdSet built-in procedure back in Register the Cache Administration User Name and Password. Since you set CacheAdminWallet=1 in the DSN, the credentials are passed within an Oracle Wallet that contains the cache administration user name and the passwords for both cache administration users.

After you have created an AWT cache group, start the replication agent on the TimesTen database. Exit ttIsql.

Command> CALL ttRepStart;
Command> exit;
Disconnecting...
Done.

Task 6: Load Initial Data

Load the cache group.

Start the ttIsql utility and connect to the cache1 DSN as the instance administrator.

Grant the SELECT, INSERT, UPDATE and DELETE privileges on the sales.customers cache table to the TimesTen cache administration user so that this user can issue SELECT, INSERT, UPDATE and DELETE SQL statements on this table. The INSERT, UPDATE and DELETE privileges on the sales.customers table are required to run write through operations from the TimesTen cache table to the cached Oracle Database table.
```
% ttIsql cache1
Command> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customers TO cacheadmin;
Command> exit;
Disconnecting...
Done.
```
Start the ttIsql utility and connect to the cache1 DSN as the TimesTen cache administration user.

Perform a LOAD CACHE GROUP statement for the first load of the AWT cache group since the cache tables are empty.
```
% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> LOAD CACHE GROUP awt_customers COMMIT EVERY 256 ROWS PARALLEL 3;
2 cache instances affected.
```

Query the contents of sales.customer cache table.

Command> SELECT * FROM sales.customers;
< 122 West     Jim Johnston >
< 663 MidWest  Pat Reed >
2 rows found.

Use the ttIsql cachegroups command to view the definition of the awt_customers cache group:

Command> cachegroups;

Cache Group CACHEADMIN.AWT_CUSTOMERS:

  Cache Group Type: Asynchronous Writethrough
  Autorefresh: No
  Aging: LRU on

  Root Table: SALES.CUSTOMERS
  Table Type: Propagate

1 cache group found.

Verify Static AWT Cache Group

Since this is a static AWT cache group, all changes made on the TimesTen database are automatically propagated to the Oracle database.

The following example inserts a new row, deletes an existing row, updates an existing row in the customers cache table, and commits the changes on the TimesTen database.

Description of the illustration writequicks7.eps

Connect to ttIsql as sales, then:

Command> INSERT INTO customers VALUES (342, "West", "Jane Stone");
1 row created.
Command> DELETE FROM customers WHERE cust_num=122;
1 row deleted.
Command> UPDATE customers SET region="East" WHERE cust_num=663; 
1 row updated.
Command> COMMIT;
Command> exit;
Disconnecting...
Done.

You can verify this by connecting to the Oracle database. As the Oracle database schema user sales, use SQL*Plus to query the contents of the customer table:

SQL> SELECT * FROM customers;
cust_num  region  name 
-----------------------------------------
342       West    Jane Stone
663       East    Pat Reed

Since this is a quick guide on how to create a static AWT cache group, see Cache Concepts and Asynchronous WriteThrough (AWT) Cache Group in the Oracle TimesTen In-Memory Database Cache Guide for a more thorough understanding of the concepts behind and the options for a static AWT cache group.

Drop the Cache Groups in the TimesTen and Oracle Databases

All of the examples in this book use the same cache groups. If you want to move on to try other cache group types in this guide, then drop the cache groups (and any cache metadata associated with those cache groups) in both the TimesTen and Oracle databases.

Stop the Replication Agent

As the TimesTen cache administration user, use the ttIsql utility to call the ttRepStop built-in procedure to stop the replication agent on the TimesTen database.

Command> call ttRepStop;
Command> exit
Disconnecting...
Done.

Drop the Cache Groups

Use the DROP CACHE GROUP statement to drop a cache group and its cache tables. On the Oracle database, the metadata objects used to manage the caching of the associated cached tables in the Oracle database are automatically removed.

Use the ttIsql utility to connect to the cache1 DSN as the instance administrator.

Grant the DROP ANY TABLE privilege to the TimesTen cache administration user so that this user can drop the underlying cache tables when dropping cache groups.

% ttIsql cache1
Command> GRANT DROP ANY TABLE TO cacheadmin;
Command> exit
Disconnecting...
Done.

Start the ttIsql utility and connect to the cache1 DSN as the cache administration user. Use ttIsql to drop the customer_orders AWT cache group.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> DROP CACHE GROUP awt_customers;

The awt_customers cache group and its respective cache table sales.customers are dropped from the TimesTen database. Any metadata objects created for managing the caching operations for this cache group are removed in the Oracle database.

See Dropping a Cache Group in the Oracle TimesTen In-Memory Database Cache Guide.