5 Cache Group Operations

Loading and refreshing an automatic refresh cache group

If the automatic refresh state of an explicitly loaded cache group is PAUSED, the automatic refresh state is changed to ON after a LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on the cache group completes.

If the automatic refresh state of a dynamic cache group is PAUSED, the automatic refresh state is changed to ON after any of the following events occur:

• Its cache tables are initially empty, and then a dynamic load, a LOAD CACHE GROUP or an unconditional REFRESH CACHE GROUP statement issued on the cache group completes

• Its cache tables are not empty, and then an unconditional REFRESH CACHE GROUP statement issued on the cache group completes

If the automatic refresh state of a dynamic cache group is PAUSED, the automatic refresh state remains at PAUSED after any of the following events occur:

• Its cache tables are initially empty, and then a REFRESH CACHE GROUP .. WITH ID statement issued on the cache group completes

• Its cache tables are not empty, and then a dynamic load, a REFRESH CACHE GROUP .. WITH ID, or a LOAD CACHE GROUP statement issued on the cache group completes

The following restrictions apply when manually loading or refreshing an automatic refresh cache group:

• A LOAD CACHE GROUP statement can only be issued if the cache tables are empty, unless the cache group is dynamic.

• The automatic refresh state must be PAUSED before you can issue a LOAD CACHE GROUP statement, unless the cache group is dynamic in which case the automatic refresh state must be PAUSED or ON.

• The automatic refresh state must be PAUSED before you can issue a REFRESH CACHE GROUP statement.

• A LOAD CACHE GROUP statement cannot contain a WHERE clause, unless the cache group is dynamic in which case the WHERE clause must be followed by a COMMIT EVERY n ROWS clause.

• A LOAD CACHE GROUP or REFRESH CACHE GROUP statement cannot contain a WITH ID clause, unless the cache group is dynamic.

• A REFRESH CACHE GROUP statement cannot contain a WHERE clause.

• All tables and columns referenced in a WHERE clause when loading the cache group must be fully qualified. For example: user_name.table_name and user_name.table_name.column_name.

When an automatic refresh operation occurs on an explicitly loaded cache group, all committed inserts, updates and deletes on the cached Oracle tables since the last automatic refresh cycle are refreshed into the cache tables. New cache instances may be loaded into the cache tables. Cache instances that already exist in the cache tables are updated or deleted if the corresponding rows in the cached Oracle tables have been updated or deleted.

For a dynamic cache group, an automatic refresh operation only refreshes committed updates and deletes on the cached Oracle tables since the last automatic refresh cycle into the cache tables because only existing cache instances in the cache tables are refreshed. New cache instances are not loaded into the cache tables. To load new cache instances into the cache tables of a dynamic cache group, use a LOAD CACHE GROUP statement or perform a dynamic load operation. See "Dynamically loading a cache group" for more information about a dynamic load operation.

Loading and refreshing a cache group using a WITH ID clause

The WITH ID clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement enables you to load or refresh a cache group based on values of the primary key columns without having to use a WHERE clause. The WITH ID clause is more convenient than the equivalent WHERE clause if the primary key contains more than one column. Using the WITH ID clause allows you to load one cache instance at a time. It also enables you to roll back the transaction containing the load or refresh operation, if necessary, unlike the equivalent statement that uses a WHERE clause because using a WHERE clause also requires specifying a COMMIT EVERY n ROWS clause.

LOAD CACHE GROUP recent_orders WITH ID (1756,573)

LOAD CACHE GROUP recent_orders WHERE orderid = 1756 and itemid = 573
COMMIT EVERY 256 ROWS

A LOAD CACHE GROUP or REFRESH CACHE GROUP statement issued on an automatic refresh cache group cannot contain a WITH ID clause unless the cache group is dynamic.

You cannot use the COMMIT EVERY n ROWS clause with the WITH ID clause.

Loading and refreshing a multiple-table cache group

If you are loading or refreshing a multiple-table cache group while the cached Oracle tables are concurrently being updated, set the isolation level in the TimesTen database to serializable before issuing the LOAD CACHE GROUP or REFRESH CACHE GROUP statement. This causes TimesTen to query the cached Oracle tables in a serializable fashion during the load or refresh operation so that the loaded or refreshed cache instances in the cache tables are guaranteed to be transactionally consistent with the corresponding rows in the cached Oracle tables. After you have loaded or refreshed the cache group, set the isolation level back to read committed for better concurrency when accessing elements in the TimesTen database.

Improving the performance of loading or refreshing a large number of cache instances

You can improve the performance of loading or refreshing a large number of cache instances into a cache group by using the PARALLEL clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement. Specify the number of threads to use when processing the load or refresh operation. You can specify 1 to 10 threads. One thread fetches rows from the cached Oracle tables, while the other threads insert the rows into the TimesTen cache tables. Do not specify more threads than the number of CPUs available on your system or you may encounter decreased performance than if you had not used the PARALLEL clause.

You cannot use the WITH ID clause or the COMMIT EVERY 0 ROWS clause with the PARALLEL clause.

REFRESH CACHE GROUP western_customers COMMIT EVERY 256 ROWS PARALLEL 4

Example of manually loading and refreshing an explicitly loaded cache group

The following is the definition of the Oracle table that will be cached in an explicitly loaded AWT cache group. The Oracle table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100))

The following is the data in the oratt.customer cached Oracle table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

The following statement creates an explicitly loaded AWT cache group new_customers that caches the oratt.customer table:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))

The oratt.customer TimesTen cache table is initially empty.

Command> SELECT * FROM oratt.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

Update the cached Oracle table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

A REFRESH CACHE GROUP statement issued on an explicitly loaded cache group is processed by unloading and then reloading the cache group. As a result, the cache instances in the cache table matches the rows in the cached Oracle table.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instance affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >

Example of manually loading and refreshing a dynamic cache group

The following is the definition of the Oracle table that will be cached in a dynamic AWT cache group. The Oracle table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100))

The following is the data in the oratt.customer cached Oracle table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Wilkins    356 Olive St. Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr. Chicago IL

The following statement creates a dynamic AWT cache group new_customers that caches the oratt.customer table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))

The oratt.customer TimesTen cache table is initially empty:

Command> SELECT * FROM oratt.customer;
0 rows found.

The following LOAD CACHE GROUP statement loads the three cache instances from the cached Oracle table into the TimesTen cache table:

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
3 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >

Update the cached Oracle table by inserting a new row, updating an existing row, and deleting an existing row:

SQL> INSERT INTO customer
  2  VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');
SQL> UPDATE customer SET name = 'Angela Peterson' WHERE cust_num = 2;
SQL> DELETE FROM customer WHERE cust_num = 3;
SQL> COMMIT;
SQL> SELECT * FROM customer;
CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St. Portland OR
       2   East      Angela Peterson   356 Olive St. Boston MA
       4   East      Roberta Simon     3667 Park Ave. New York NY

A REFRESH CACHE GROUP statement issued on a dynamic cache group only refreshes committed updates and deletes on the cached Oracle tables into the cache tables. New cache instances are not loaded into the cache tables. Therefore, only existing cache instances are refreshed. As a result, the number of cache instances in the cache tables are either fewer than or the same as the number of rows in the cached Oracle tables.

Command> REFRESH CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >

A subsequent LOAD CACHE GROUP statement loads one cache instance from the cached Oracle table into the TimesTen cache table because only committed inserts are loaded into the cache table. Therefore, only new cache instances are loaded. Cache instances that already exist in the cache tables are not changed as a result of a LOAD CACHE GROUP statement, even if the corresponding rows in the cached Oracle tables were updated or deleted.

Command> LOAD CACHE GROUP new_customers COMMIT EVERY 256 ROWS;
1 cache instance affected.
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Peterson, 356 Olive St. Boston MA >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >

Types of SQL statements for which dynamic load is available

Dynamic load is available only for the following types of statements issued on a cache table in a dynamic cache group:

• SELECT, UPDATE or DELETE with an equality expression on the primary key column. The equality expression can only contain constants or parameters. For example:

  SELECT * FROM oratt.customer WHERE cust_num=50
  

  If the primary key is composite, the SELECT, UPDATE or DELETE statement must contain equality expressions on all of the primary key columns. For example:

  UPDATE oratt.orderdetails SET quantity = 5 WHERE orderid=2280 AND itemid=663
  

• SELECT, UPDATE or DELETE with an equality expression on the foreign key column. The equality expression can only contain constants or parameters. For example:

  DELETE FROM oratt.cust_interests WHERE custid=364
  

  If the foreign key is composite, the SELECT, UPDATE or DELETE statement must contain equality expressions on all of the foreign key columns. For example:

  SELECT * FROM oratt.orders WHERE ord_num=4955 AND cust_num=716
  

The SELECT, UPDATE or DELETE statements for which dynamic load is available must satisfy the following conditions:

• If the statement contains a subquery, the equality expression must be specified in the outermost query of the statement. The statement can only reference cache tables from one cache group but it can also reference non-cache tables.

• The statement cannot contain the UNION, INTERSECT or MINUS set operators.

Example of dynamically loading a cache group

The following is the definition of the Oracle table that will be cached in a dynamic AWT cache group. The Oracle table is owned by the schema user oratt.

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
 region   VARCHAR2(10),
 name     VARCHAR2(50),
 address  VARCHAR2(100))

The following is the data in the oratt.customer cached Oracle table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Wilkins    356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following statement creates a dynamic AWT cache group new_customers that caches the oratt.customer table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers
FROM oratt.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))

The oratt.customer TimesTen cache table is initially empty:

Command> SELECT * FROM oratt.customer;
0 rows found.

The following SELECT statement dynamically loads one cache instance from the cached Oracle table into the TimesTen cache table:

Command> SELECT * FROM oratt.customer WHERE cust_num = 1;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >

The following UPDATE statement dynamically loads one cache instance from the cached Oracle table into the TimesTen cache table, updates the instance in the cache table, and then automatically propagates the update to the cached Oracle table:

Command> UPDATE oratt.customer SET name = 'Angela Peterson' WHERE cust_num = 2;
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated data in the oratt.customer cached Oracle table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Peterson   356 Olive St., Boston MA
       3   Midwest   Stephen Johnson   7638 Walker Dr., Chicago IL

The following DELETE statement dynamically loads one cache instance from the cached Oracle table into the TimesTen cache table, deletes the instance from the cache table, and then automatically propagates the delete to the cached Oracle table:

Command> DELETE FROM oratt.customer WHERE cust_num = 3;
Command> SELECT * FROM oratt.customer;
< 1, West, Frank Edwards, 100 Pine St., Portland OR >
< 2, East, Angela Peterson, 356 Olive St., Boston MA >

The following is the updated data in the oratt.customer cached Oracle table.

CUST_NUM   REGION    NAME              ADDRESS
--------   -------   ---------------   ---------------------------    
       1   West      Frank Edwards     100 Pine St., Portland OR
       2   East      Angela Peterson   356 Olive St., Boston MA

Disabling dynamic loading

You can use the following mechanisms to disable dynamic loading on all cache tables in dynamic cache groups that are accessed within a particular connection:

• Set the DynamicLoadEnable DSN attribute to 0

• Call the SQLSetConnectOption() ODBC function with the TT_DYNAMIC_LOAD_ENABLE connection option and the value parameter set to 0. (There is no equivalent JDBC or OCI function to disable dynamic loading.)

  rc = SQLSetConnectOption(hDbc, TT_DYNAMIC_LOAD_ENABLE, 0)
  

  Call the SQLSetConnectOption() ODBC function with the TT_DYNAMIC_LOAD_ENABLE connection option and the value parameter set to 1 to re-enable dynamic loading.

You can use the following mechanisms to disable dynamic loading on all cache tables in dynamic cache groups that are accessed within a particular transaction:

• Use the ttIsql utility's set dynamicloadenable 0 command

• Call the ttOptSetFlag built-in procedure with the DynamicLoadEnable flag and the optimizer value set to 0.

  call ttOptSetFlag('DynamicLoadEnable', 0)
  

  Call the ttOptSetFlag built-in procedure with the DynamicLoadEnable flag and the optimizer value set to 1 to re-enable dynamic loading.

  The DynamicLoadEnable flag setting takes effect when a statement is prepared and it is the setting that is used when the statement is executed even if the setting has changed from the time the statement was prepared to when the statement is executed. After the transaction has been committed or rolled back, the original connection setting takes effect for all subsequently prepared statements.

Displaying dynamic load errors

You can configure TimesTen to return an error if a dynamic load operation fails. For example, a dynamic load fails if the SELECT, UPDATE or DELETE statement does not meet the requirements stated in "Types of SQL statements for which dynamic load is available".

You can use the following mechanisms to return an error if a dynamic load operation fails within a particular connection:

• Set the DynamicLoadErrorMode DSN attribute to 1

• Call the SQLSetConnectOption() ODBC function with the TT_DYNAMIC_LOAD_ERROR_MODE connection option and the value parameter set to 1. (There is no equivalent JDBC or OCI function to display dynamic load errors.)

  rc = SQLSetConnectOption(hDbc, TT_DYNAMIC_LOAD_ERROR_MODE, 1)
  

  Call the SQLSetConnectOption() ODBC function with the TT_DYNAMIC_LOAD_ERROR_MODE connection option and the value parameter set to 0 to suppress error reporting when a dynamic load operation fails.

You can use the following mechanisms to return an error if a dynamic load operation fails within a particular transaction:

• Use the ttIsql utility's set dynamicloaderrormode 1 command

• Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 1.

  call ttOptSetFlag('DynamicLoadErrorMode', 1)
  

  Call the ttOptSetFlag built-in procedure with the DynamicLoadErrorMode flag and the optimizer value set to 0 to suppress error reporting when a dynamic load operation fails.

  The DynamicLoadErrorMode flag setting takes effect when a statement is prepared and it is the setting that is used when the statement is executed even if the setting has changed from the time the statement was prepared to when the statement is executed. After the transaction has been committed or rolled back, the original connection setting takes effect for all subsequently prepared statements.

Changing the passthrough level for a connection or transaction

You can override the current passthrough level using the ttIsql utility's set passthrough command which applies to the current and all subsequent transactions in the session.

You can also override the setting for a specific transaction by calling the ttOptSetFlag built-in procedure with the PassThrough flag. The following procedure call sets the passthrough level to 3:

call ttOptSetFlag('PassThrough', 3)

The PassThrough flag setting takes effect when a statement is prepared and it is the setting that is used when the statement is executed even if the setting has changed from the time the statement was prepared to when the statement is executed. After the transaction has been committed or rolled back, the original connection setting takes effect for all subsequently prepared statements.

Determining the passthrough setting for a statement

You can call the SQLGetStmtOption() ODBC function with the TT_STMT_PASSTHROUGH_TYPE statement option to determine whether a statement is to be executed in the TimesTen database or passed through to the Oracle database for execution.

rc = SQLGetStmtOption(hStmt, TT_STMT_PASSTHROUGH_TYPE, &execDB)

If TT_STMT_PASSTHROUGH_NONE is returned to the output parameter of the SQLGetStmtOption() ODBC function, the statement is to be executed in TimesTen. Otherwise, if TT_STMT_PASSTHROUGH_ORACLE is returned to the output parameter, the statement is to be passed through to Oracle for execution.

You may not always be certain whether a SQL statement will be executed in TimesTen or passed through to Oracle for execution. For example, if PassThrough=1, the syntax of a SELECT statement determines whether it is executed in TimesTen or passed through to Oracle. In this case, prepare the statement before calling SQLGetStmtOption() with the TT_STMT_PASSTHROUGH_TYPE statement option.

There is no equivalent JDBC or OCI function to determine the passthrough setting for a statement.