User Managed Cache Group

If the system managed cache groups (read-only, AWT, SWT) do not satisfy your application's requirements, you can create a user managed cache group that defines customized caching behavior.

Create a user managed cache group with customized caching behavior with one or more of the following cache table attributes:

Only TimesTen Classic supports user-managed cache groups.

Note:

When TimesTen manages operations for user managed cache groups, it connects to the Oracle database using the current user's credentials provided on the connection string. The current user's credentials can be provided with an Oracle Wallet pointed to by the PwdWallet connection attribute or with the UID, PWD, and OraclePwd connection attributes. TimesTen does not connect to the Oracle database with the Oracle cache administration user name and password registered with the ttCacheUidPwdSet built-in procedure when managing SWT cache group operations. See Providing Cache Administration User Credentials When Connecting and Registering the Cache Administration User Name and Password..

• You can specify the READONLY Cache Table Attribute on individual cache tables in a user managed cache group to define read-only behavior where the data is refreshed on TimesTen from the Oracle database at the table level.

• You can specify the PROPAGATE cache table attribute on individual cache tables in a user managed cache group to define synchronous writethrough behavior at the table level. The PROPAGATE Cache Table Attribute specifies that committed changes on the cache table are automatically and synchronously propagated to the cached Oracle Database table.

• You can define a user managed cache group to automatically refresh and propagate committed changes between the Oracle and TimesTen databases by using the AUTOREFRESH cache group attribute and the PROPAGATE cache table attribute. Using both attributes enables bidirectional transmit, so that committed changes on the TimesTen cache tables or the cached Oracle Database tables are propagated or refreshed to each other.

  See Automatically Refreshing a Cache Group for more information about defining an autorefresh mode, interval, and state.

• You can use the LOAD CACHE GROUP, REFRESH CACHE GROUP, and FLUSH CACHE GROUP statements to manually control the transmit of committed changes between the Oracle and TimesTen databases.

  See Manually Loading and Refreshing a Cache Group for more information about the LOAD CACHE GROUP and REFRESH CACHE GROUP statements. See Flushing a User Managed Cache Group for more information about the FLUSH CACHE GROUP statement.

• You can cache Oracle Database materialized views in a user managed cache group that does not use either the PROPAGATE or AUTOREFRESH cache group attributes. The cache group must be manually loaded and flushed. You cannot cache Oracle Database views.

The following sections provide more information about user managed cache groups:

• READONLY Cache Table Attribute

• PROPAGATE Cache Table Attribute

• Examples of User Managed Cache Groups

READONLY Cache Table Attribute

The READONLY cache table attribute can be specified only for cache tables in a user managed cache group.

READONLY specifies that the cache table cannot be updated directly. By default, a cache table in a user managed cache group is updatable.

Unlike a read-only cache group where all of its cache tables are read-only, in a user managed cache group individual cache tables can be specified as read-only using the READONLY cache table attribute.

The following restrictions apply when using the READONLY cache table attribute:

• If the cache group uses the AUTOREFRESH cache group attribute, the READONLY cache table attribute must be specified on all or none of its cache tables.

  See Automatically Refreshing a Cache Group for more information about using the AUTOREFRESH cache group attribute.

• You cannot use both the READONLY and PROPAGATE cache table attributes on the same cache table.

  See PROPAGATE Cache Table Attribute for more information about using the PROPAGATE cache table attribute.

• A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the READONLY nor the PROPAGATE cache table attribute.

  See Flushing a User Managed Cache Group for more information about the FLUSH CACHE GROUP statement.

• After the READONLY cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.

PROPAGATE Cache Table Attribute

The PROPAGATE cache table attribute can be specified only for cache tables in a user managed cache group.

PROPAGATE specifies that committed changes on the TimesTen cache table as part of a TimesTen transaction are automatically and synchronously propagated to the cached Oracle Database table. If the PROPAGATE cache table attribute is not specified, then the default setting for a cache table in a user managed cache group is the NOT PROPAGATE cache table attribute (which does not propagate committed changes on the cache table to the cached Oracle table).

All SQL statements run by an application on cached tables are applied to the cached tables immediately. All of these operations are buffered until the transaction commits or reaches a memory upper limit. At this time, all operations are propagated to the tables in the Oracle database.

Note:

If the TimesTen database or its daemon fails unexpectedly, the results of the transaction on either the TimesTen or Oracle databases are not guaranteed.

Since the operations in the transaction are applied to tables in both the TimesTen and Oracle databases, the process for committing is as follows:

1. After the operations are propagated to the Oracle database, the commit is first attempted in the Oracle database.

   • If an error occurs when applying the operations on the tables in the Oracle database, then all operations are rolled back on the tables on the Oracle database. If the commit fails in the Oracle database, the commit is not attempted in the TimesTen database and the application must roll back the TimesTen transaction. If the user tries to run another statement, an error displays informing them of the need for a roll back. As a result, the Oracle database never misses updates committed in TimesTen.

2. If the commit succeeds in the Oracle database, the commit is attempted in the TimesTen database.

   • If the transaction successfully commits on the Oracle database, the user's transaction is committed on TimesTen (indicated by the commit log record in the transaction log) and notifies the application. If the application ends abruptly before TimesTen informs it of the success of the local commit, TimesTen is still able to finalize the transaction commit on TimesTen based on what is saved in the transaction log.

   • If the transaction successfully commits on the Oracle database and a failure occurs before returning the status of the commit on TimesTen, then no record of the successful commit is written into the transaction log and the transaction is rolled back.

   • If the commit fails in TimesTen, an error message is returned from TimesTen indicating the cause of the failure. You then need to manually resynchronize the cache tables with the Oracle Database tables.

     Note:

     See Synchronous WriteThrough (SWT) Cache Group for information on how to re-synchronize the cache tables with the Oracle Database tables.

You can disable propagation of committed changes on the TimesTen cached tables to the Oracle database with the ttCachePropagateFlagSet built-in procedure. This built-in procedure can enable or disable automatic propagation so that committed changes on a cache table on TimesTen for the current transaction are never propagated to the cached Oracle Database table. You can then re-enable propagation for DML statements by resetting the flag to one with the ttCachePropagateFlagSet built-in procedure. After the flag is set back to one, propagation of committed changes to the Oracle database resumes. The propagation flag automatically resets to one after the transaction is committed or rolled back. See ttCachePropagateFlagSet in the Oracle TimesTen In-Memory Database Reference.

The following restrictions apply when using the PROPAGATE cache table attribute:

• If the cache group uses the AUTOREFRESH cache group attribute, the PROPAGATE cache table attribute must be specified on all or none of its cache tables.

  See Automatically Refreshing a Cache Group for more information about using the AUTOREFRESH cache group attribute.

• If the cache group uses the AUTOREFRESH cache group attribute, the NOT PROPAGATE cache table attribute cannot be explicitly specified on any of its cache tables.

• You cannot use both the PROPAGATE and READONLY cache table attributes on the same cache table.

  See READONLY Cache Table Attribute for more information about using the READONLY cache table attribute.

• A FLUSH CACHE GROUP statement cannot be issued on the cache group unless one or more of its cache tables use neither the PROPAGATE nor the READONLY cache table attribute.

  See Flushing a User Managed Cache Group for more information about the FLUSH CACHE GROUP statement.

• After the PROPAGATE cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.

• The PROPAGATE cache table attribute cannot be used when caching Oracle Database materialized views.

• TimesTen does not perform a conflict check to prevent a propagate operation from overwriting data that was updated directly on a cached Oracle Database table. Therefore, updates should only be performed directly on the TimesTen cache tables or the cached Oracle Database tables, but not both.

Examples of User Managed Cache Groups

Examples are provided for the definition of the Oracle Database tables that are to be cached in the user managed cache groups.

On the Oracle Database:

These Oracle database tables are owned by the schema user sales.

CREATE TABLE active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  region VARCHAR2(12) DEFAULT 'Unknown');

CREATE TABLE ordertab
 (orderid NUMBER(10) NOT NULL PRIMARY KEY,
  custid  NUMBER(6) NOT NULL);

CREATE TABLE cust_interests
 (custid   NUMBER(6) NOT NULL,
  interest VARCHAR2(10) NOT NULL,
  PRIMARY KEY (custid, interest));

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

The Oracle cache administration user must be granted the SELECT privilege on any cached tables. In this example, the table is the sales.active_customer table.

On the Oracle database as an administrator, grant the following privileges:

SQL> GRANT SELECT ON sales.active_customer TO cacheadmin;

On the TimesTen database:

Connect as the TimesTen cache administration user. Use the CREATE USERMANAGED CACHE GROUP statement to create a user managed cache group.

The following statement creates a user managed cache group update_anywhere_customers that caches the sales.active_customer table as shown in Figure 4-8:

CREATE USERMANAGED CACHE GROUP update_anywhere_customers
AUTOREFRESH MODE INCREMENTAL INTERVAL 30 SECONDS
FROM sales.active_customer
 (custid NUMBER(6) NOT NULL,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  PRIMARY KEY(custid),
  PROPAGATE);

Figure 4-8 Single-Table User Managed Cache Group

Description of "Figure 4-8 Single-Table User Managed Cache Group"

In this example, all columns except region from the sales.active_customer table are cached in TimesTen. Since this is defined with the PROPAGATE cache table attribute, updates committed on the sales.active_customer cache table on TimesTen are transmitted to the sales.active_customer cached Oracle Database table. Since the user managed cache table is also defined with the AUTOREFRESH cache attribute, any committed changes on the sales.active_customer Oracle Database table are transmitted to the update_anywhere_customers cached table.

In this example, the AUTOREFRESH cache group attribute specifies that committed changes on the sales.active_customer cached Oracle Database table are automatically refreshed to the TimesTen sales.active_customer cache table every 30 seconds.

If you manually created the Oracle Database objects used to enforce the predefined behaviors of a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute as described in The initCacheAdminSchema.sql Script, you need to set the autorefresh state to OFF when creating the cache group.

Then you need to run the ttIsql utility's cachesqlget command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle Database table that is cached in the user managed cache group.

See Manually Creating Oracle Database Objects for Cache Groups With Autorefresh.

The following statement creates a multiple-table user managed cache group western_customers that caches the sales.active_customer, sales.ordertab, sales.cust_interests, and sales.orderdetails tables as shown in Figure 4-9:

CREATE USERMANAGED CACHE GROUP western_customers
FROM sales.active_customer
 (custid NUMBER(6) NOT NULL,
  name   VARCHAR2(50),
  addr   VARCHAR2(100),
  zip    VARCHAR2(12),
  region VARCHAR2(12),
  PRIMARY KEY(custid),
  PROPAGATE)
  WHERE (sales.active_customer.region = 'West'),
sales.ordertab
 (orderid NUMBER(10) NOT NULL,
  custid  NUMBER(6) NOT NULL,
  PRIMARY KEY(orderid),
  FOREIGN KEY(custid) REFERENCES sales.active_customer(custid),
  PROPAGATE),
sales.cust_interests
 (custid   NUMBER(6) NOT NULL,
  interest VARCHAR2(10) NOT NULL,
  PRIMARY KEY(custid, interest),
  FOREIGN KEY(custid) REFERENCES sales.active_customer(custid),
  READONLY),
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.ordertab(orderid))
  WHERE (sales.orderdetails.quantity >= 5);

Figure 4-9 Multiple-Table User Managed Cache Group

Description of "Figure 4-9 Multiple-Table User Managed Cache Group"

Only customers in the West region who ordered at least 5 of the same item are cached.

Each cache table in the western_customers cache group contains a primary key. Each child table references a parent table with a foreign key constraint. The sales.active_customer root table and the sales.orderdetails child table each contain a WHERE clause to restrict the rows to be cached. The sales.active_customer root table and the sales.ordertab child table both use the PROPAGATE Cache Table Attribute so that committed changes on these cache tables are automatically propagated to the cached Oracle Database tables. The sales.cust_interests child table uses the READONLY Cache Table Attribute so that it cannot be updated directly.