Skip navigation.
Downloads | Product Documentation | Support

Administration Guide

# Configuring the Query Results Cache

This chapter describes how to set up and manage caching for data services in AquaLogic Data Services Platform.

The chapter contains the following sections:

- Understanding Results Caching
- Setting Up Caching
- Purging Cache Entries

Note: Caching is only available for data service functions for which caching is allowed. For details see Caching Functions in the Using Data Services Design View chapter of the Data Services Developer's Guide.

Caching is not available for ad-hoc queries or XQuery security functions.

# 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.

When function caching has been authorized through Design View (see "Caching Functions" in the chapter of the Using Data Services Design View chapter of the Data Services Developer's Guide) the first time a data service function is run, Data Services Platform saves the results to a local query results cache. The next time the function is run with the same parameters, Data Services Platform 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. Once enabled, you can configure the cache and its time-to-live (TTL) for individual data service functions. Configuration tasks associated with caching include the following:

- Enabling caching for an application, and setting the cache data source and table names.
- Enabling caching of data service functions, and setting the cache time-to-live (which determines how long results are stored in cache).
- Monitoring and clearing 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. 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, Data Services Platform 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.

In the event of a Liquid Data Server shutdown, the contents of the results cache are retained. Upon server restart, the Liquid Data Server resumes caching as before. On first invocation of a cache-enabled function, the Liquid Data 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

Data Services Platform 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 see the following discussions related to bypassing cached data in the Application Developer's Guide:

- "Bypassing a Data Cache When Using the Mediator API" in the Accessing Data Services from Java Clients chapter.
- "Bypassing a Function Results Cache When Using a Data Service Controls" in the Accessing Data Services from Workshop Applications chapter.

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.

You can use any data source configured for WebLogic as the caching database. Data Services Platform 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 Data Services Platform application not share cache tables. There should be separate tables for each application.

To use results caching, you must have one of the following database servers installed and running:

- Oracle
- DB2
- Sybase
- Pointbase
- Microsoft SQL Server

Since the Data Services Platform cache may contain sensitive data, it is important to maintain access control over the cache database so that only authorized users can access it. Also, it is recommended that the JDBC data source used for cache 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 7-1 shows the steps for setting up caching.

Figure 7-1 Cache Setup Steps

The steps illustrated in Figure 7-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 have Data Services Platform set up the cache table automatically from the DSP Console using whichever 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:

`<WebLogicHome>``/liquiddata/dbscripts/`

For example:

`<WebLogicHome>``/liquiddata/dbscripts/oracle/ld_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 application.

It is recommended that each application keep its cached data in its own cache table. For example, you can name the table <appname>_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 7-6). See Table 7-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 Data Services Platform (as described inStep 3: Specify the Cache Data Source and Table on page 7-8), 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

Data Services Platform 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 either DB2 or Pointbase as your DBMS.

For DB2 and Pointbase, the scripts create the CINVKEY and CCONTENT columns (which store the results data) with a specific size, as shown in Table 7-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 7-1.

Table 7-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 an application 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 Administration Console to create a JDBC data source on the WebLogic Server that points to the database that you have set up for the Data Services Platform 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 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 Administration Console, you can set up the cache tables using the DSP Console.

To specify the cache database and enable caching:

1. Select the application node in the Navigation pane.

The General tab appears, as illustrated in Figure 7-2.

Figure 7-2 Enabling Results Caching for an Application

2. In the Cache section of the General tab, click Enable Cache.
3. Using the Cache Data source name drop-down list, choose the JNDI name of the data source you configured for the cache table.

If you did not create a cache table, choose the data source in which you want Data Services Platform to create the cache table.

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

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

5. Click Apply.

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 application, 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. Click the data service name in the Navigation pane.

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

Figure 7-3 Enabling Caching by Function

2. Check the Enable Cache checkbox for each function for which you want to enable caching.
3. Enter a time-to-live value, in seconds, for each cache-enabled function.

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

4. Click Apply to save your changes. Notice that you can also purge the cache by function on this page and view the current cached entries.

# Purging Cache Entries

Purging the cache removes cached entries from the cache database. When the cache is purged, each function will execute against its data sources until it is cached again. Data Services Platform 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 Liquid Data Server

Data Services Platform 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 application at once, or for individual functions. This section describes the following:

- Purging the Cache for an Application on page 7-13
- Purging the Cache for a Function on page 7-14

## Purging the Cache for an Application

You can purge the cache for an application using the General Application Settings page. To purge the cache for an application:

1. Select the application node in the Navigation pane of the DSP Console.

The General Application Settings page appears, as illustrated in Figure 7-4.

Figure 7-4 Purging the Cache for an Application

2. Click the Purge Cache link in the Cache section of the General tab.

The console asks for confirmation before purging the cache.

3. Click Yes.

The purge occurs immediately, without having to apply changes.

## Purging the Cache for a Function

You can purge the cache for individual functions using the Cache page, as illustrated in Figure 7-5.

Figure 7-5 Purging the Cache for a Function

To purge cache by function:

1. Click the data service for which you want to purge cache by function in the Navigation pane.
2. Click the Trash can next the function for which you want to purge cache.