Create Cache (EXT)

The Create Cache statement creates a cache that holds the data of a remote table. The data is not loaded into the cache when the Create Cache statement is executed; the data is loaded the first time that the remote table is executed or when a Refresh Cache statement on the remote table is executed. An error is returned if the remote table specified does not exist.

NOTE: Caches on views are not supported.

remote_table is the name of the remote table from which data is to be cached on the client. The name of the cached table is the same as the name of the remote table. When the table name is specified in a query, the cached table is accessed, not the remote table.

The remote table name can be a two-part name: schemaname.tablename. When specifying a two-part name, the specified remote table must be defined in the specified schema, and you must have the privilege to create objects in the specified schema.

REFERENCING is an optional clause that specifies the name of the remote table(s) for which a relationship cache is to be created. See “Relational Caches” and “Referencing Clause” for a complete explanation.

REFRESH_INTERVAL is an optional clause that specifies the length of time the data in the cached table can be used before being refreshed. See “Refresh Interval Clause” for a complete explanation.

INITIAL_CHECK is an optional clause that specifies when the driver initially checks whether the data in the cache needs refreshed. See “Initial Check Clause” for a complete explanation.

PERSIST is an optional clause that specifies the life span of the data in the cached table or view. See “Persist Clause” for a complete explanation.

ENABLED is an optional clause that specifies whether the cache is enabled or disabled for use with SQL statements. See “Enabled Clause” for a complete explanation.

CALL_LIMIT is an optional clause that specifies the maximum number of Web service calls that can be used to populate or refresh the cache. See “Call Limit Clause” for a complete explanation.

FILTER is an optional clause that specifies a filter for the primary table to limit the number of rows that are cached in the primary table. See “Filter Clause” for a complete explanation.

NOTE: If two or more optional clauses are specified, they must be specified in the order shown in the grammar description.

If the Referencing clause is specified, the Create Cache statement creates a set of related caches, one for each of the tables specified in the statement. This set of caches is referred to as a related or relational cache. The set of caches in a relational cache is treated as a single entity. They are refreshed, altered, and dropped as a unit. Any attributes specified in the Create Cache statement apply to the cache created for the primary table and to the caches created for all of the referenced tables specified.

A database session can have both standalone and relational caches defined, but only one cache can be defined on a table. If a table is referenced in a relational cache definition, a standalone cache cannot be created on that table.

The Referencing clause specifies the name of the remote table(s) for which a relationship cache is to be created; it is optional. The specified remote table must be related to either the primary table being cached or one of the other specified related tables. The remote table name cannot include a schema name. The referenced tables must exist in the same schema as the primary table.

remote_table_ref represents remote_table[.foreign_key_name]

remote_table specifies one or more tables related to the primary table that are to be cached in conjunction with the primary table.

foreign_key_name specifies the name of the foreign key relationship between the remote table and the primary table (or, optionally, another related table). If a foreign key name is not specified, the driver attempts to find a relationship between the remote table and one of the other tables specified in the relational cache. The driver first looks for a relationship to the primary table. If a relationship to the primary table does not exist, the driver then looks for a relationship to other referenced tables.

The Refresh Interval clause specifies the length of time the data in the cached table can be used before being refreshed; it is optional. The driver maintains a timestamp of when the data in a table was last refreshed. When a cached table is used in a query, the driver checks if the current time is greater than the last refresh time plus the value of Refresh_Interval. If it is, the driver refreshes the data in the cached table before processing the query.

0 specifies that the cache is refreshed manually. You can use the Refresh Cache statement to refresh the cache manually.

-1 resets the refresh interval to the default value of 12 hours.

interval_value is a positive integer that specifies the amount of time between refreshes. The default unit of time is hours (H). You can also specify M for minutes or D for days. For example, 60M would set the time between refreshes to 60 minutes.

The default refresh interval is 12 hours.

The Initial Check clause specifies when the driver performs its initial check of the data in the cache to determine whether it needs to be refreshed; it is optional.

ONFIRSTCONNECT specifies that the initial check is performed the first time a connection for a user is established. Subsequently, it is performed each time the table or view is used. A driver session begins on the first connection for a user and the session is active as long as at least one connection is open for the user.

FIRSTUSE specifies that the initial check is performed the first time the table or view is used in a query. Subsequently, it is performed each time the table or view is used.

DEFAULT resets the value back to its default, which is FIRSTUSE.

The Persist clause specifies the life span of the data in the cached table or view; it is optional.

TEMPORARY specifies that the data exists for the life of the driver session. When the driver session ends, the data is discarded. A driver session begins on the first connection for a user and the session is active if at least one connection is open for the user.

MEMORY specifies that the data exists beyond the life of the connection. While the connection is active, the cached data is stored in memory. When the connection is closed, the cached data is persisted to disk. If the connection ends abnormally, changes to the cached data may not be persisted to disk. This is the default.

DISK specifies that the data exists beyond the life of the connection. A portion of the cached data is stored in memory while the connection is active. If the size of the cached data exceeds the cache memory threshold, the remaining data is stored on disk. When the connection is closed, the portion of the cached data that is in memory is persisted to disk. If the connection ends abnormally, changes to the cached data held in memory may not be persisted to disk.

DEFAULT resets the PERSIST value back to its default, which is MEMORY.

WARNING: If you specify a value of MEMORY or DISK for the Persist clause, the remote data remains on the client past the lifetime of the application.

NOTE: You can design your application to force all cached data held in memory to be persisted to disk at any time by using the Checkpoint statement.

The Enabled clause specifies whether the cache is enabled or disabled for use with SQL statements; it is optional.

YES or TRUE specifies that the cache is enabled. When a cache is enabled, the driver accesses the cached data for the remote table or view when a query is executed.

The driver does not check whether the cache needs to be refreshed when the Alter Cache statement is used to enable the cache. The check occurs the next time that the cache is accessed.

NO or FALSE specifies that the cache is disabled, which means that the driver accesses the data in the remote table or view rather than the cache when a query is executed. The driver does not update the cache when inserts, updates, and deletes are performed on a remote table or view. To use the cache, you must enable it.

All data in an existing cache is persisted on the client even when the cache is disabled, except for the case where PERSIST is set to TEMPORARY.

The default is TRUE.

The Call Limit clause specifies the maximum number of Web service calls that can be used to populate or refresh the cache; it is optional.

0 specifies no call limit.

-1 resets the call limit back to its default, which is 0 (no call limit).

max_calls is a positive integer that specifies the maximum number of Web service calls.

NOTE: The call limit for a cache is independent of the Stmt_Call_Limit set on a database session. See “Alter Session Attributes” for details.

If the call limit of a cache is exceeded during the population or refresh of the cache, the cache is marked as partially initialized. At the next refresh opportunity, the driver attempts to complete the population or refresh of the cache. If the call limit (or other error) occurs during this second attempt, the cache becomes invalid and is disabled. All data in the cache is discarded after the second attempt to populate or refresh the cache fails. Before re-enabling the cache, consider altering the cache definition to allow more Web service calls or specify a more restrictive filter, or both.

The default value is 0.

Filter is an optional clause that specifies a filter for the primary table to limit the number of rows that are cached in the primary table. This clause is not supported for views.

where expression is any valid Where clause. See “Where Clause” for details. Do not include the Where keyword in the clause. The filter for an existing cache can be removed by specifying an empty string for the filter expression, for example, FILTER().

The default value is that cached data is not filtered.

The Referencing clause allows multiple related tables to be cached as a single entity. The following example creates a cache on the remote table account. The cache is populated with all accounts that had activity in 2010. Additionally, caches are created for the following remote tables: opportunity, contact, and opportunitylineitem. These caches are populated with the opportunities and contacts that are associated with the accounts stored in the accounts cache and the opportunity line items associated with the opportunities stored in the opportunity cache.

The following example caches all rows of the account table with a refresh interval of 12 hours, checks whether data of the cached table needs to be refreshed on the first use, persists the data beyond the life of the connection, and stores the data in memory while the connection is active.

The following example caches all active accounts in the account table with a refresh interval of 1 day, checks whether data of the cached table needs to be refreshed when the connection is established, and discards the data when the connection is closed.