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 thePROPAGATE
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
, andFLUSH 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
andREFRESH CACHE GROUP
statements. See Flushing a User Managed Cache Group for more information about theFLUSH CACHE GROUP
statement. -
You can cache Oracle Database materialized views in a user managed cache group that does not use either the
PROPAGATE
orAUTOREFRESH
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
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, theREADONLY
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
andPROPAGATE
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 theREADONLY
nor thePROPAGATE
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:
-
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.
-
-
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, thePROPAGATE
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, theNOT PROPAGATE
cache table attribute cannot be explicitly specified on any of its cache tables. -
You cannot use both the
PROPAGATE
andREADONLY
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 thePROPAGATE
nor theREADONLY
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 follows Description of Figure 4-8 follows](img/example2.png)
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 follows Description of Figure 4-9 follows](img/example1.png)
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.