1. Cache Guide
  2. Defining Cache Groups
  3. Implementing Aging in a Cache Group for TimesTen Classic
  4. Time-Based Aging in TimesTen Classic

Time-Based Aging in TimesTen Classic

Time-based aging deletes data from a cache group based on the aging policy's specified data lifetime and frequency. Time-based aging can be defined for all cache group types in TimesTen Classic.

The data lifetime defines the minimum age of data within the table. The comparison of the time is based on the timestamp, so data may not become a candidate for aging until longer than the specified lifetime (but never less that the specified lifetime).

Define a time-based aging policy for a cache group by using the AGING USE clause in the cache table definition of the CREATE CACHE GROUP statement. Aging occurs automatically if the aging state is set to its default of ON.

On the Oracle Database:

The following example are the definitions of the Oracle Database tables that are to be cached in the AWT cache group. The Oracle Database tables are owned by the schema user sales. 

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 order_item
(orditem_id NUMBER(12) NOT NULL PRIMARY KEY,
 ord_num    NUMBER(10),
 prod_num   VARCHAR2(6),
 quantity   NUMBER(3));

The Oracle cache administration user must be granted the SELECT, INSERT, UPDATE, and DELETE privileges on any cached tables. In this example, the table is the sales.orders and sales.order_item tables.

See Required Privileges for Cache Administration User for Cache Operations for all required privileges for different activities.

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

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.orders TO cacheadmin;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.order_item TO cacheadmin;

On the TimesTen database:

The following example defines a time-based aging policy on the AWT cache group ordered_items: 

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP ordered_items
FROM 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))
AGING USE when_placed LIFETIME 45 DAYS CYCLE 60 MINUTES ON,
sales.order_item
 (orditem_id NUMBER(12) NOT NULL,
  ord_num    NUMBER(10),
  prod_num   VARCHAR2(6),
  quantity   NUMBER(3),
  PRIMARY KEY(orditem_id),
  FOREIGN KEY(ord_num) REFERENCES sales.orders(ord_num));

Cache instances that are greater than 45 days old based on the difference between the current system timestamp and the timestamp in the when_placed column of the sales.orders table are candidates for aging. The aging process checks every 60 minutes to see if there are cache instances that can be automatically aged out or deleted from the cache tables.

The AGING USE clause requires the name of a non-nullable TIMESTAMP or DATE column used for time-based aging. We refer to this column as the timestamp column.

For each row, the value in the timestamp column stores the date and time when the row was most recently inserted or updated. The values in the timestamp column is maintained by your application. If the value of this column is unknown for particular rows and you do not want those rows to be aged out of the table, define the timestamp column with a large default value.

You can create an index on the timestamp column to optimize performance of the aging process.

You cannot add a column to an existing table and then use that column as the timestamp column because added columns cannot be defined as non-nullable. You cannot drop the timestamp column from a table that has a time-based aging policy defined.

Specify the lifetime in days, hours, minutes or seconds after the LIFETIME keyword in the AGING USE clause.

The value in the timestamp column is subtracted from the current system timestamp. The result is then truncated to the specified lifetime unit (day, hour, minute, second) and compared with the specified lifetime value. If the result is greater than the lifetime value, the row is a candidate for aging.

After the CYCLE keyword, specify the frequency in which aging occurs in days, hours, minutes or seconds. The default aging cycle is 5 minutes. If you specify an aging cycle of 0, aging is continuous.

The ALTER TABLE statement can be used to perform the following tasks associated with changing or defining a time-based aging policy on a cache group:

  • Change the aging state of a cache group by specifying the root table and using the SET AGING clause.

  • Change the lifetime by specifying the root table and using the SET AGING LIFETIME clause.

  • Change the aging cycle by specifying the root table and using the SET AGING CYCLE clause.

  • Add a time-based aging policy to a cache group that has no aging policy defined by specifying the root table and using the ADD AGING USE clause.

  • Drop the time-based aging policy on a cache group by specifying the root table and using the DROP AGING clause.

To change the aging policy of a cache group from time-based to LRU, use an ALTER TABLE statement on the root table with the DROP AGING clause to drop the time-based aging policy. Then use an ALTER TABLE statement on the root table with the ADD AGING LRU clause to add an LRU aging policy.

You must stop the cache agent before you add, alter or drop an aging policy on a cache group with autorefresh.