Skip navigation.
Downloads | Product Documentation | Support

Administration Guide

# Configuring the Query Results Cache

This chapter describes how to manage caching for stored queries in BEA Liquid Data for WebLogic. It contains the following sections:

- Understanding Results Caching
- Setting up the Results Cache Database
- Enabling the Results Cache
- Configuring a Security Policy for Purging the Cache Results
- Configuring Results Caching for Stored Queries
- Flushing the Cache
- Purging the Cache Programmatically

Note: Caching is used with stored queries only. Caching does not apply to ad-hoc queries, which are never cached.

# Understanding Results Caching

After the Query Processor executes a query, it returns to the client the data that resulted from query execution. If Liquid Data results caching is enabled, the first time a query is run, Liquid Data saves its results into a query results cache. The next time the query is run with the same parameters, Liquid Data checks the cache configuration and, if the results have not expired, quickly retrieves the results from the cache rather than re-running the query. In this way, for queries that are executed repeatedly with the same parameters, using the results query cache reduces processing time and enhances overall system performance.

The query results cache is disabled by default and must be enabled according to the instructions in Enabling the Results Cache. Once enabled, you can configure the cache for individual stored queries as needed, specifying how long query results are stored in the cache before they expire (time out), and explicitly flushing the query cache. For more information, see Configuring Results Caching for Stored Queries.

In general, the results cache should be periodically refreshed to reflect data changes in the underlying data stores. The more dynamic the underlying data, the more frequently the cache should be set to expire. For queries on static data, you can configure the results cache so that it never expires.

If the cache policy expires for a particular query, Liquid Data flushes the cache result automatically on the next invocation.

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. Upon the first invocation of a cached query, the Liquid Data Server checks the results cache to determine whether the cached results for this query are valid or have expired, and then proceeds accordingly.

Note: The query results cache is stored in a database that you must explicitly configure. For more information, see Setting up the Results Cache Database.

# Setting up the Results Cache Database

To use results caching, you must first set up the results cache database. To set up the results cache database you need to do the following:

- Step 1: Install and Configure the Database Server
- Step 2: Run the SQL Script to Create the Cache Database
- Step 3: Create the JDBC Data Source for the Cache Database

## Step 1: Install and Configure the Database Server

To use results caching, you first need to install and configure the database server according to your vendor's instructions. Liquid Data supports the following relational databases for caching:

- Oracle
- Microsoft SQL Server

## Step 2: Run the SQL Script to Create the Cache Database

After you have installed and configured a database server, you need to run a SQL script on your database server that creates the Liquid Data cache database. Liquid Data provides the following scripts (in `%WL_HOME%\liquiddata\server\dbscripts`):

Table 22-1 Cache Database Creation Scripts

Database | Script File Name
--- | ---
Oracle | `ldcache_oracle.ddl`
Microsoft SQL Server | `ldcache_mssqlserver.ddl`

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

After you have created the Liquid Data cache database, you need to create a JDBC data source in WebLogic Server that points to the Liquid Data cache database. Using the Administration Console, create a JDBC data source with the following name:

`ldCacheDS`

For details about creating a JDBC data source, see Creating a JDBC Data Source.

Note: If you are using Oracle for the 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, Enabling the Results Cache

# Enabling the Results Cache

By default, results caching is disabled in Liquid Data. To use results caching, you must explicitly enable it in the General tab on the Liquid Data node in the Administration Console. Before you enable caching, make sure that you have set up the Liquid Data cache database as described in Setting up the Results Cache Database.

Note: If caching is enabled but the cache data source has not been configured, an exception is thrown in the Administration Console.

To enable result caching in Liquid Data:

1. In the left pane of the Administration Console, click the Liquid Data node.
2. In the right pane, click the Configuration tab.
3. Click the General tab.

Figure 22-2 Enabling Results Caching in the General Tab on the Liquid Data Node

4. On the General tab, check the Cache Results box.
5. Click Apply.

Once caching is enabled, you must explicitly configure the results cache according to the instructions in the following section, Configuring Results Caching for Stored Queries.

# Configuring a Security Policy for Purging the Cache Results

You can set a security policy on the query cache. The security policy determines if a user can purge results from the cache. The cache security policy is global across the entire cache; that is, if a user has the authority to purge the cache, then that user can purge the cache for any query for which he has access. Depending on the security policy of different queries, the user might have permissions to see some queries and not others.

## Security Policy Methods

Table 22-3 describes the different methods you can configure for the cache purging security policy. Set these methods from the Methods drop list on the Define Security Policy page for the cache.

Table 22-3 Security Policy Methods for Purging the Cache

Method | Description
--- | ---
`ALL` | All of the below privileges.
`Read Configuration` | The ability to access the security policy for the cache (through the Edit Security Policy link).
`Modify Configuration` | The ability to access and modify the security policy for the cache (through the Edit Security Policy link).
`ConfigureCacheAPIAccess` | The ability to access the `purgeCache` APIs.

## To Configure a Cache Purging Security Policy

To configure a security policy to determine who has permission to purge the cache, perform the following steps:

1. Start the Administration Console (for details, see Starting the Administration Console).
2. In the left pane of the Administration Console, expand the node for your domain and click the Liquid Data node.
3. Navigate to the Cache tab on the Liquid Data Administration Console.
4. Click Define Security Policy or Edit Security Policy on the Cache tab. If the cache already has a policy defined, the link is labeled "Edit Security Policy"; if the cache has no defined policy, the link is labeled "Define Security Policy."
5. Assign a security policy for purging the cache and click Apply. For details on assigning security policies, see Assigning Security Policies to Liquid Data Objects.

# Configuring Results Caching for Stored Queries

Each stored query has its own cache policy that determines how Liquid Data manages results caching for that stored query. This section describes how to configure the cache policy for stored queries. It includes the following sections:

- To Create the Cache Policy
- To Edit the Cache Policy
- To Remove the Cache Policy

## To Create the Cache Policy

To configure the results cache for a stored query initially:

1. Navigate to the Repository tab on the Liquid Data node, as described in Navigating to the Repository Tab.
2. On the Repository tab, click the `stored_queries` folder.

The Administration Console displays a list of stored queries in the server repository.

Figure 22-4 List of Stored Queries in the Repository

3. Select the stored query for which you want to configure caching.
4. Click Cache.

The Administration Console displays the Configure Cache tab.

Figure 22-5 Configure Cache Tab

5. Enter the information described in the following table.

Table 22-6 Configure Cache Settings

Permission | Task
--- | ---
Query Name | Name of the selected stored query.
Never Expires | If selected (checked), the results cache for the selected stored query never expires. If cleared (not checked), you can configure an expiration time in the Expires After fields.
Expires After | Expiration time, which is calculated from when the query cache is first created. Days—Number of days, up to 999 days. Hours—Number of hours, up to 99 hours. Minutes—Number of minutes, up to 99 minutes. If Days, Hours, and Minutes are all set to zero (0), then the results cache never expires.

Note: The expiration time should reflect the degree to which the data in the underlying data source(s) is expected to change. In general, for more dynamic data (such as real-time data feeds), specify a shorter expiration time. For more static data (such as general product or personnel information), specify a longer expiration time.

6. Click Apply.

The Administration Console creates the cache policy and Liquid Data begins caching results for the selected query. Any expiration times are calculated starting with the time that the cache policy was initially created.

## To Edit the Cache Policy

To edit the cache policy for a stored query:

1. In the left pane, click Liquid Data.
2. Click the Cache tab.

Figure 22-7 Cache Tab of the Liquid Data Administration Console

3. Click the name of the stored query whose cache you want to configure.
4. Change the information described in Table 22-6, as needed.
5. Click Apply.

The Administration Console updates the selected cache policy and flushes any cached results for this stored query.

## To Remove the Cache Policy

To remove the cache policy for a stored query:

1. In the left pane, click Liquid Data.
2. Click the Cache tab (see Figure 22-7).
3. Click the trash can next to the stored query whose cache you want to delete.
4. When prompted, click Yes to confirm removal.

The Administration Console removes the selected cache policy and flushes any cached results for this query.

Note: If you delete a stored query for which caching is enabled, Liquid Data also deletes the cached query plan and any cached results. For more information, see Deleting Folders and Files in the Server Repository.

# Flushing the Cache

Flushing the cache is when all of the entries in the cache are purged from the cache. When the cache is flushed, all queries will execute against their data sources until they are cached again. Liquid Data flushes the cached query result for a given stored query whenever any of the following events occur:

- The stored query is updated or deleted.
- Caching is disabled on the Liquid Data Server.

Liquid Data flushes the cached query result for a given stored query on the next query invocation whenever any of the following events occur:

- The query results have expired per the cache policy.
- The cache policy for a stored query is updated or deleted.

# Purging the Cache Programmatically

You can use the `com.bea.ldi.cache.ejb` package to purge the cache programmatically. For details on using the EJB API to purge the cache, see the Using the Cache Purging APIs chapter in the Liquid Data Application Developer's Guide and the Javadoc.