# Administration Guide

# Configuring Query Results Cache

This chapter describes how to set up and manage caching for data services in Oracle Data Service Integrator. It contains the following sections:

- Understanding Results Caching
- Setting Up Caching
- Monitoring and Purging Data Cache

Note: | Caching is not available for ad-hoc queries and XQuery functions for security.

## Understanding Results Caching

By caching data returned by data service functions, you can improve response times for clients and reduce the processing burden on back-end systems.

Note: | When results sets are cached, there are chances of using stale data instead of the updated information.

To use results caching, a database that is certified for Oracle Data Service Integrator caching support should be installed and running. Such DBMS systems are identified in the Supported Configurations.

You can specify if you want to enable caching for functions in the Workshop for WebLogic Overview mode. When you run the function the first time, the query results for the function are saved to a local query results cache. The next time the function is run with the same parameters, Oracle Data Service Integrator checks the cache configuration and, if the results have not expired, retrieves the results from the cache rather than from the external source.

A cache entry exists for the results of each function invocation with distinct parameters. In cases when a cache-enabled function is invoked twice with two different parameters, two cache entries will be created.

By default caching is disabled. If you enable it, you can configure the cache and its time-to-live (TTL) for individual data service functions through the Oracle Data Service Integrator Administration Console.

To enable caching for data service functions, you need to:

- Enable caching at the dataspace level and set the cache data source and table names.
- Enable caching of data service functions, and set the cache time-to-live (which determines how long results are stored in cache).
- Monitor and clear the cache, as required.

The TTL setting is set individually for each data service function. In general, the more dynamic the underlying data, the more frequently the cache should be set to expire.

Note: | Cached data is valid until the TTL limit goes past the time at which it is cached regardless of other changes in the configuration between that time.

In some cases, caching should not be used at all. Here are two examples:

- If the data changes frequently and real-time access to it is critical cache should not be enabled. On the other hand, for functions that return static data, you can configure the results cache so that it never expires. If the cache policy expires for a particular function, Oracle Data Service Integrator flushes the cache result automatically on the next invocation.
- Cache should never be set for functions without parameters. Every physical data service function based around a relational table, for example, falls into this category. Caching such a function can have a very negative impact of performance unless the table itself has very few records.

If an Oracle Data Service Integrator-enabled server shutdown occurs, the contents of the results cache are retained. When the server restarts, it resumes caching as before. On first invocation of a cache-enabled function, the Oracle Data Service Integrator-enabled server checks the results cache to determine whether the cached results for this function are valid or have expired, and then proceeds accordingly.

### Caching API

Oracle Data Service Integrator provides an API allowing client applications to bypass any existing cached results in favor of the physical data source. This API provides automatic client-side cache refresh of the affected function. For details about forcing data cache update and read-through, refer to “Forcing Data Cache Read-through and Update” in the Invoking Data Services from Java Clients chapter. Application Developer’s Guide:

Note: | Caching is particularly effective in cases when significant processing has been applied against large data sets, producing filtered results. For optimal performance, it is recommended that you not enable caching on functions that simply return large data sets directly from a relational database data source.

Oracle Data Service Integrator can set up the cache table in the data source for you (if the server is in development mode), or you can create it yourself as described in the following section. Note that it is recommended that the dataspace not share cache tables. There should be separate tables for each dataspace.

Note: | To prevent unauthorized access to sensitive data in the cache, it is important to maintain access control over the cache database. Also, make sure that the JDBC data source used for caching is not be used for other purposes.

## Setting Up Caching

The steps for setting up cache depend on several factors, including whether you are in development or production mode and whether you need to customize the cache table schema. Figure 8-1 shows the steps for setting up caching.

Figure 8-1 Cache Setup Steps

The steps illustrated in Figure 8-1 are described in the following sections:

- Step 1: (Optional) Run the SQL Script to Create the Cache Tables
- Step 2: Create the JDBC Data Source for the Cache Database
- Step 3: Specify the Cache Data Source and Table
- Step 4: Enabling Caching by Function

### Step 1: (Optional) Run the SQL Script to Create the Cache Tables

For a WebLogic server that is in development mode, you can set up the cache table automatically from the Oracle Data Service Integrator Administration Console using the data source you choose. For production environments, or if you want to customize the cache schema, you will need to run the SQL scripts manually.

You can create the cache table using SQL scripts in the subdirectory corresponding to a particular DBMS at the following location:

<ALDSP_HOME>`/dbscripts/`

For example:

`<`ALDSP_HOME`>``/dbscripts/oracle/dsp_cache.sql`

To create the cache table:

1. Open the script from the subdirectory that corresponds to your DBMS and modify the name of the created table so that it is unique for the dataspace.

It is recommended that you store the cached data for each dataspace in its own cache table. For example, you can name the table <dsname>_CACHE.

2. Make any other schema changes, as required.

You should not change the column names or otherwise modify the structure of the schema tables (except in specific cases, as noted in Modifying the Cache Table Structure on page 8-6). See Table 8-1 for information about the cache table schema.

3. Run the script.
4. Index the table based on the CHASH column (for retrieval) and the CUID column (for record updates).

When the table is created automatically by Oracle Data Service Integrator (as described in Step 3: Specify the Cache Data Source and Table on page 8-7), an index for CHASH is created. The automatically created name is the table name with "_INDEX" appended to it.

Note: | On DB2, the name is truncated to a maximum of 18 characters.

#### Modifying the Cache Table Structure

Oracle Data Service Integrator requires that its cache tables have a specific schema. Therefore, you should generally not modify the structure of the cache table. In some cases, however, the default column sizes may need to be adjusted based on the deployment. This may be a requirement in cases when you have data services that frequently serve result sets that are larger than the content columns in the default database tables and you are using DB2 as your DBMS.

For DB2, the scripts create the CINVKEY and CCONTENT columns (which store the results data) with a specific size, as shown in Table 8-1. If any serialized keys or content need to be larger than that size, the table schema should be adjusted accordingly before running the script.

Before attempting to implement customizations to the cache table, you should be familiar with the schema as shown in Table 8-1.

Table 8-1 Cache Table Schema
Column | Description
--- | ---
CUID | Unique numeric identifier for the cache entry.
CHASH | Hash value of the key (CINVKEY) as a 64-bit integer. This field enables fast searches, since searching by the key itself is inefficient as the key is stored as a binary object. (In fact, searching by the key itself is impossible for any DBMS for which the scripts create the CINVKEY as a BLOB type.)
CEXPIRE | Timestamp value indicating when the record expires. This value is computed during record insertion as current time plus the TTL value defined for the function.
CFID | Serialized name of the function. When the table is created automatically, VARCHAR(512) type is used. The value should be adjusted to a lower or higher size if names of all functions in a dataspace are smaller or if some names are larger then 512 characters.
CFARITY | The number of arguments the function accepts. This is used to differentiate functions in case of function overloading (not currently used).
CINVKEY | The serialized invocation identifier consisting of the function and its arguments (created with a size of 50 kilobytes on a Pointbase DBMS).
CCONTENT | Binary data constituting the cached results. (Created with size of 1 gigabyte for DB2 and 200K for a Pointbase DBMS.)

### Step 2: Create the JDBC Data Source for the Cache Database

After creating the cache table, you can use the WebLogic Server Administration Console to create a JDBC data source on the WebLogic Server that points to the database that you have set up for the Oracle Data Service Integrator cache.

Note: | If using Oracle as your cache database, you must set the Honor Global Transactions setting to `FALSE` (it is set to `TRUE` by default). When you create the Oracle JDBC data source in the WebLogic Server Administration Console, you must uncheck the Honor Global Transactions box.

Once created, you can enable the result cache as described in the following section.

### Step 3: Specify the Cache Data Source and Table

After configuring the table that you want to use for caching as a JDBC data source in the WebLogic Server Administration Console, you can set up the cache tables using the Oracle Data Service Integrator Administration Console.

To specify the cache database and enable caching:

1. Select the dataspace node in the Navigation pane. The General tab appears, as shown in Figure 8-2.
Figure 8-2 Enabling Results Caching for a Dataspace

2. Click Lock & Edit to acquire the lock.
3. In the Data Cache section of the General tab, click Enable Cache.
4. Specify the JNDI name of the data source you configured for the cache table in the Data source name list box.

If you did not create a cache table, choose the data source in which you want Oracle Data Service Integrator to create the cache table.

5. If you created a custom cache table for the dataspace, enter its name in the Cache table name field.

Otherwise, either enter another name for Oracle Data Service Integrator to use when creating the table or leave the field blank, in which case the default name, <dsName>_CACHE, will be used.

6. Click Save > Activate Changes.

Once caching is enabled, you need to configure results caching for each function.

### Step 4: Enabling Caching by Function

After enabling Cache settings for the dataspace, you can configure data service function caching. For each function, you can specify whether caching should be enabled, and set the time-to-live (in seconds) for cache entries.

To enable caching by function:

1. Make sure that the System Administration category is selected.
2. Click the data service name in the Navigation pane.

The Data Cache page appears, as illustrated in Figure 8-3.

Figure 8-3 Enabling Caching by Function

3. Click Lock & Edit to acquire the lock.
4. Select the Enable Data Cache checkbox for each function for which you want to enable caching.

Note: | Make sure that you set the Allow Data Caching property for the function to true in Oracle Data Service Integrator IDE, before enabling data caching on the console. For example, to enable caching for ADDRESS(), set Allows Data Caching property to true in Oracle Data Service Integrator IDE, as shown in Figure 8-4.

Figure 8-4 Configuring the Allow Data Caching Property in Oracle Data Service Integrator IDE

5. Enter a time-to-live (TTL) value, in seconds, for each cache-enabled function.

The more dynamic the underlying data, the more frequently the cache should be set to expire.

6. Select the Add Identity Key in Cache if you want to store the caching information of the identity keys of Oracle Data Service Integrator resources. This enables securing the data cache values that depend on other environmental variables. For more information about this feature, refer to Caching Identity Keys for Security.
7. Click Save > Activate to save your changes.

#### Caching Identity Keys for Security

This features provides the ability to filter cached entries based on user profile. When you select the Add Identity Keys in Cache checkbox, the data cache values become user-specific, which ensures that relevant data cache entries are available to the corresponding user. For example, if two users, User A and User B, are accessing the cached values for functions, then User A will be able to view values specific to User A’s transactions and User B will be able to view cached values for transactions done by User B.

This feature is especially useful when an external data source is mapped and managed through Oracle Data Service Integrator Administration Console.

## Monitoring and Purging Data Cache

You can manage function-level data caching using the Operations category. Selecting the Operation category displays the Monitor tab as shown in Figure 8-5.

Figure 8-5 Monitoring Data Cache Values

This tab provides runtime cache statistics for functions and allows you purge the cache.

The Number of Data Cache Entries field displays the number of results that have been cached in the data cache.

Note: | The Operations category pertains to the runtime monitoring of deployed artifacts. In other words, the Operations category depends on the core (deployed) session. By contrast, other categories such as Service Explorer and Security relate to the session in progress.

### Purging Data Cache

Purging the cache removes cached entries from the cache database. When the cache is purged, each function executes against its data sources until it is cached again.

Oracle Data Service Integrator flushes the cached query result for a given stored query whenever any of the following events occur:

- The data service function is modified or deleted
- Caching is disabled on the server

Oracle Data Service Integrator flushes the cached function result on the next invocation whenever any of the following events occur:

- The function results have expired per the cache policy
- The cache policy for a function is updated or deleted

You can also purge the cache manually, either for the entire dataspace at once, or for individual functions, as described in the following sections.

#### Purging the Cache for a Dataspace

To purge the cache for a dataspace:

1. Select the dataspace from the navigation pane.
2. Click the Operations category.
Figure 8-6 Purging the Cache for a Dataspace

3. Click Purge Data Cache in the Monitor tab.

#### Purging the Cache for a Function

You can purge the cache for individual functions using the Monitor tab in the Operations category, as illustrated in Figure 8-5.

To purge cache by function:

1. From the navigation tree, select the data service for which you want to purge cache by function.
2. Click the Trash icon in the Purge Data Cache field to purge cache for the function.