The SYSTEM_CACHES catalog table stores the definitions of the caches created on remote tables. The data in the SYSTEM_CACHES table provides the name, type (single table or relational), status, and other information for each defined cache. The table name returned for a remote relational cache is the name of the primary table of the relational cache; however, its type is REMOTE RELATIONAL. You can query SYSTEM_CACHES to determine the caches currently defined by the driver. The values in the SYSTEM_CACHES table are read-only. The referenced tables of a relational cache can be determined by querying the SYSTEM_CACHE_REFERENCES catalog table (see SYSTEM_CACHE_REFERENCES Catalog Table).

The following table describes the columns of the SYSTEM_CACHES table, which is sorted on the following columns: CACHE_TYPE, TABLE_SCHEMA, and TABLE_NAME.

Table 1. SYSTEM_CACHES Catalog Table
Column Name Data Type Description
TABLE_CAT VARCHAR (128),NULLABLE The catalog that contains the remote table on which the cache is defined. It is NULL for this driver.
TABLE_SCHEM VARCHAR (128),NULLABLE The schema that contains the remote table on which the cache is defined.
TABLE_NAME VARCHAR (128),NOT NULL The name of the remote table on which the cache is defined.
CACHE_TYPE VARCHAR (20),NOT NULL The type cache, which can be either REMOTE TABLE or REMOTE RELATIONAL.
REFRESH_INTERVAL INTEGER,NOT NULL The refresh interval (in minutes).
INITIAL_CHECK VARCHAR (20),NOT NULL The value that defines when the initial refresh check is performed: ONFIRSTCONNECT or FIRSTUSE.
PERSIST VARCHAR (20),NOT NULL The value that defines whether the data in the cache is persisted past the lifetime of the connection: TEMPORARY, MEMORY, or DISK.
ENABLED BOOLEAN,NOT NULL The value that defines whether the cache is enabled for use with SQL statements: TRUE or FALSE.
CALL_LIMIT INTEGER,NOT NULL The maximum number of Web service calls that can be made when refreshing the cache. The value 0 indicates no call limit.
REFRESH_MODE INTEGER,NOT NULL For internal use only.
FILTER VARCHAR (128),NULLABLE The Where clause used to filter the rows that are cached.
LAST_REFRESH DATETIME, NULLABLE The time, in Coordinated Universal Time (UTC), the cache was last refreshed.
STATUS VARCHAR (30) The Cache status. Valid values are:
  • New: The cache has been created, but the data has not been populated.
  • Initialized: The cache has been created and the data has been populated.
  • Load aborted: The cache has been created, but the last attempt to populate the data failed. The cache is still valid. The next access attempts to populate the data again.
  • Invalid: The cache is invalid. The second attempt to populate the data failed.
  • Dirty: An insert or update operation has been performed on the cache and the cache has not been refreshed.