LOAD CACHE GROUP

The LOAD CACHE GROUP statement loads data from Oracle database tables into a TimesTen cache group.

Required Privilege

No privilege is required for the cache group owner.

LOAD or LOAD ANY CACHE GROUP for another user's cache group.

INSERT, DELETE, UPDATE privileges on underlying tables.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

LOAD CACHE GROUP [Owner.]GroupName 
[WHERE ConditionalExpression] 
COMMIT EVERY n ROWS
[PARALLEL NumThreads [READERS NumReaders]]

or

LOAD CACHE GROUP [Owner.]GroupName
WITH ID (ColumnValueList)

Note:

The WITH ID clause is not supported in TimesTen Scaleout.

Parameters

Parameter	Description
[Owner.]GroupName	Name assigned to the cache group.
WHERE ConditionalExpression	Use the WHERE clause to specify a search condition to qualify the target rows of the cache operation. If you use more than one table in the WHERE clause and the tables have columns with the same names, fully qualify the table names.
COMMIT EVERY n ROWS	Use the COMMIT EVERY n ROWS clause to indicate the frequency (based on the number of rows that are loaded into the cache group) at which a commit is issued during the load operation. This clause is required if you do not specify the WITH ID clause. n represents the number of rows that are loaded into the cache group before the transaction is committed. Express n as an integer (where n >= 0). If you specify 0 for n, the LOAD CACHE GROUP statement is executed as one transaction.
[PARALLEL NumThreads]	Provides parallel loading for cache group tables. Specifies the number of loading threads to run concurrently. One thread performs the bulk fetch from the Oracle database and the other threads (NumThreads - 1 threads) perform the inserts into TimesTen. Each thread uses its own connection or transaction. The minimum value for NumThreads is 2. The maximum value is 10. If you specify a value greater than 10, TimesTen assigns the value 10.
[READERS NumReaders]	This option specifies the total number of threads from the NumThreads parameter to use for bulk fetching from the Oracle database. For example, if you specify a NumThreads parameter of 8 and a readers option of 3, then 3 threads are used to bulk fetch data from the Oracle database and 5 threads are used to load data into the TimesTen database. Express NumReaders as an integer where 0 < NumReaders < NumThreads.
WITH ID ColumnValueList	The WITH ID clauses enables you to use primary key values to load the cache instance. Specify ColumnValueList as either a list of literals or binding parameters to represent the primary key values. The WITH ID clause is not supported in TimesTen Scaleout.

Description

• LOAD CACHE GROUP loads all new cache instances from the Oracle database that satisfy the cache group definition and are not yet present in the cache group.

• Before issuing the LOAD CACHE GROUP statement, ensure that the replication agent is running if the cache group is replicated or is an AWT cache group. Make sure the cache agent is running.

• LOAD CACHE GROUP is executed in its own transaction, and must be the first operation in a transaction.

• LOAD CACHE GROUP only loads new (inserted) rows on the Oracle database tables into the corresponding TimesTen cache tables.

• Errors cause a rollback. When cache instances are committed periodically, errors abort the remainder of the load. The load is rolled back to the last commit.

• If the LOAD CACHE GROUP statement fails when you specify COMMIT EVERY n ROWS (where n >= 0), the content of the target cache group could be in an inconsistent state since some loaded rows are already committed. Some cache instances may be partially loaded. Use the UNLOAD CACHE GROUP statement to unload the cache group, then reload the cache group.

• Generally, you do not have to fully qualify the column names in the WHERE clause of the LOAD CACHE GROUP statement. However, since TimesTen automatically generates queries that join multiple tables in the same cache group, a column must be fully qualified if there is more than one table in the cache group that contains columns with the same name.

• When loading a read-only cache group:

  • The AUTOREFRESH state must be paused.

  • The LOAD CACHE GROUP statement cannot have a WHERE clause (except on a dynamic cache group).

  • The cache group must be empty.

• The automatic refresh state of a cache group may change after a LOAD CACHE GROUP operation completes. See Loading and Refreshing a Dynamic Cache Group with Autorefresh in the Oracle TimesTen In-Memory Database Cache Guide for information.

• Following the execution of a LOAD CACHE GROUP statement, the ODBC function SQLRowCount(), the JDBC method getUpdateCount(), and the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument return the number of cache instances that were loaded.

• Use the WITH ID clause as follows:

  • In place of the WHERE clause for faster loading of the cache instance

  • To specify binding parameters

  • To roll back the load transaction upon failure

Restrictions

• The LOAD CACHE GROUP...WITH ID clause is not supported in TimesTen Scaleout.

• Do not reference child tables in the WHERE clause.

• Do not specify the PARALLEL clause in the following circumstances:

  • With the WITH ID clause

  • With the COMMIT EVERY 0 ROWS clause

  • When database level locking is enabled (connection attribute LockLevel is set to 1)

• Do not use the WITH ID clause when loading these types of cache groups:

  • Static read-only cache group

  • Static user managed cache group with the autorefresh attribute

  • User managed cache group with the AUTOREFRESH and PROPAGATE attributes

• Do not use the WITH ID clause with the COMMIT EVERY n ROWS clause.

Examples

CREATE CACHE GROUP recreation.cache
    FROM recreation.clubs (
        clubname CHAR(15) NOT NULL,
        clubphone SMALLINT,
        activity CHAR(18),
        PRIMARY KEY(clubname))
    WHERE (recreation.clubs.activity IS NOT NULL);

LOAD CACHE GROUP recreation.cache
    COMMIT EVERY 30 ROWS;

Use the HR schema to illustrate the use of the PARALLEL clause with the LOAD CACHE GROUP statement. The COMMIT EVERY n ROWS clause is required. Issue the CACHEGROUPS command. You see cache group cg2 is defined and the autorefresh state is paused. Unload cache group cg2, then specify the LOAD CACHE GROUP statement with the PARALLEL clause to provide parallel loading. You see 25 cache instances loaded.

Command> CACHEGROUPS;

Cache Group SAMPLEUSER.CG2:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 1.5 Minutes

  Root Table: SAMPLEUSER.COUNTRIES
  Table Type: Read Only

  Child Table: SAMPLEUSER.LOCATIONS
  Table Type: Read Only

  Child Table: SAMPLEUSER.DEPARTMENTS
  Table Type: Read Only

1 cache group found.

Command> UNLOAD CACHE GROUP cg2;
25 cache instances affected.
Command> COMMIT;
Command> LOAD CACHE GROUP cg2 COMMIT EVERY 10 ROWS PARALLEL 2;
25 cache instances affected.
Command> COMMIT;

The following example loads only the cache instances for customers whose customer number is greater than or equal to 5000 into the TimesTen cache tables in the new_customers cache group from the corresponding Oracle database tables:

LOAD CACHE GROUP new_customers WHERE (oratt.customer.cust_num >= 5000) COMMIT
EVERY 256 ROWS;

See also

• REFRESH CACHE GROUP
• UNLOAD CACHE GROUP