Data Caching :: SAS(R) Federation Server 4.1: Administrator's Guide

Overview

Data caching can be used to manage the performance of frequently accessed data sources to minimize impact on the databases and operational servers. You can free up resources for the high-availability data sources by caching data that is used often and fairly constant. Overall, data caching can have a positive effect on user satisfaction and system performance.

When query optimization alone is not sufficient, caching provides an alternative with greater flexibility than traditional replication and consolidation techniques. Any FedSQLdefiner's rights view can be used to create a cache and caches can be refreshed periodically to remain synchronized with their parent views. Queries can be processed against caches just as if you were accessing the original data source.

A definer's rights view can be cached in any catalog defined in SAS Federation Server, with the exception of any catalog defined in an MDS service. When the cache is refreshed, the view is executed and the results are stored in a table which is named and maintained by SAS Federation Server. When a user selects from the cached view, results are returned from the data stored in the table, and not from a dynamic execution of the view. This improves performance by eliminating the need to derive a new FedSQL execution plan, fetching data from slow or unavailable data sources, and performing SQL operations such as joins or function evaluations. Instead, the view execution merely fetches the data stored in the table created during the cache refresh.

Data caching can have a positive impact on server performance. You can use data caching to pre-calculate results. Using the following example, a SELECT from view V1 would fetch data and calculate the results, returning a single number. If you cache the result anyone selecting from the view will receive the result immediately. For example,

CREATE VIEW V1 AS SELECT AVG(B1) FROM A,B,C,D,E 
where E .C1 < (SELECT AVG(C1) FROM E) AND B.C1 = A.C1 AND 
C.C1 < B.C1 AND D.C1 * E.C1 > SUM(B.C1)

You can also cache tables or result sets so that they remain consistent during multiple queries. For example, an ORDERS table is updated continuously during the day as customers purchase products. Caching the data guarantees consistent results and reduces the load on the servers, freeing resources to process incoming orders.

Views and Caching

SAS Federation Server through the use of FedSQL, allows users to cache data from a definer's rights view, creating a materialized view of the data, also known as the cache table. A cache table is a snapshot of the target view from a specific point in time.

Only definer's rights views can be used to cache data. When a definer's rights view is executed, it uses the credentials of the view’s schema owner rather than the current user’s credentials, to access catalogs that are referenced in the view. A definer's rights view returns the same result from the underlying database, no matter who is requesting the data. This allows a single copy of the review result set to be cached and consumed by all users. You can use SAS Federation Server authorization, including table, column and row-level security, to provide a granular and user-specific access to the view.

If a definer’s rights view is altered to an invoker’s rights view, the view is no longer cached.

Before you begin, ensure that the following prerequisites and configuration tasks have been addressed for both definer’s rights views and cached views.

Requirements for Cached Views

Note: This section uses the following acronyms:

VS (View Schema) is the schema where the cached view resides.
CS (Cache Schema) is the schema where the cache tables reside.
VSO (View Schema Owner) is the owner of the schema where the cached view resides.
CSO (Cache Schema Owner) is the owner of the schema where the cache tables reside.

Here are the requirements for definer’s rights views that are cached:

A user that is allowed to create or drop cached views within a schema should be granted CREATE CACHE privilege on the VS or an object in its inheritance hierarchy. This privilege also allows the user to refresh the cache.
A user that is allowed to refresh cached views within the schema should be granted ALTER CACHE privilege on the VS, unless they already have CREATE CACHE privilege. ALTER CACHE also allows the user to enable or disable a cache.
A user that is creating a cache also needs the CONNECT privilege on the ADMIN DSN.
Only definer’s rights views can be cached.
Administrators implicitly have all privileges, including CREATE CACHE and ALTER CACHE.
A schema owner must be assigned to the CS object. This is the cache table schema owner, also referred to as the CSO. The CSO effectively owns all cache tables in its schema, and this identity is used for executing the view and saving the cached data.
The CSO must have CONNECT privilege on the data service the cache tables are in.
The CSO must have a database login to the database of the cached tables if the cached location requires credentials. This can be a personal login or a shared login. This is needed for cache creation and refresh.
The CSO must have a database login to the database of the cached view. This can be a personal login or a shared login. The server impersonates the CSO user during cache creation and refresh, and the CSO must be able to select from the original view.

Note: During data cache connections, the CSO connects to the databases that contain the CS and the VS using a Credential Search Order of "PERSONAL, SHARED". See Credentials Search Order (CSO) for DSN Connectionsfor more information.
The VSO must be granted CREATE TABLESPACE privilege on the CS for the cache tables. This privilege allows the server to cache results sets in the CS for views owned by the VSO. The server assumes the identity of the CSO user to create and drop cached tables in the CS, to insert and delete rows in the cache table, and to select data from the cache table during client access.

Working with Cached Views

Overview

You can configure cached views using one of these methods:

Issue administration DDL statements such as CREATE CACHE, REFRESH CACHE, ALTER CACHE, and DROP CACHE.
Use the Data Cache module in SAS Federation Server Manager.

Administration DDL statements are described in Appendix 1. Procedures for caching data in SAS Federation Server Manager are described in the application's online Help.

The following scenarios describe how data operations are performed using various DDL statements.

Creating a Cache

Use the CREATE CACHE DDL statement cache a definer's rights view or change an existing cache definition. The CREATE CACHE privilege is required on the view that is being cached.

A cache table is created when the CREATE CACHE statement is executed. A cache table is also created when the ALTER CACHE statement is executed with the REFRESH option. A cache table is a snapshot of the target view from a specific point in time.

The CREATE CACHE statement adds entries to the OBJECTS and CONFIG_OBJECTS information views. Several options are available with the CREATE CACHE statement.

Alter a Cache

To alter an existing cache, use the ALTER CACHE DDL statement with available options REFRESH, ENABLE and DISABLE.

To refresh an existing cached view, use ALTER CACHE with the REFRESH option. The REFRESH option creates a new cache table which is a snapshot of the target view when the refresh is done. The required privileges are CREATE CACHE or ALTER CACHE on the view that is being cached or refreshed.

The following example refreshes an existing cached view using the most recent definition:

ALTER CACHE  [view_catalog_na me.[view_schema_name.]]view_name REFRESH

You can also disable and enable a cached view using ALTER CACHE. For additional details, see Disabling and Enabling Cached Views.

Purge Cache

Purge Cache forces the removal of outdated cache tables that are no longer in use. Only system users or those with ADMINISTER privilege can execute this DDL statement.

There are two commands that you can use to purge cache tables:

Use the explicit PURGE CACHE DDL statement to activate the cache table cleanup process for all cache tables created through SAS Federation Server. When PURGE CACHE is issued, messages are returned indicating the cache tables that were successfully removed and what problems were encountered. This command has no options.
```
PURGE CACHE
```
Schedule cache table cleanup for certain intervals using the ALTER SERVER statement. The syntax to set the time-out is:
```
ALTER SERVER {OPTIONS(xset PURGE_CACHE XX)}
```
where xx is the time-out value in minutes.
- A negative value indicates that the cleanup thread will wake only when the PURGE CACHE command is issued. It never wakes up automatically.
- A value of 0 indicates that the cleanup thread wakes whenever a CREATE CACHE, ALTER CACHE REFRESH, DROP CACHE, or PURGE CACHE statement is issued, or when the view is dropped. Note that this might not clean up all old caches since some cache views might be in use at the time of cleanup.
- A positive value indicates how often (in minutes) the cleanup thread wakes up to remove orphaned cache tables.
Note: Cleanup is not run on deferred caches. A cache is deferred when CREATE CACHE includes an option value of [DEFERRED].

Drop Cache

Use the DROP CACHE DDL statement or issue a DROP VIEW command to drop a cache. Invoking DROP VIEW also drops all of the view's associated cache tables. DROP CACHE requires CREATE CACHE privilege on the view.

DROP CACHE [view_catalog_name.[view_schema_name.]]view_name [FORCE]
DROP VIEW [view_catalog_name.[view_schema_name.]]view_name [FORCE]

Disabling and Enabling Cached Views

Overview

In the event that a cached view needs to be taken offline for any reason, it can be temporarily disabled. Disabling a cached view does not drop or delete a cached view. Instead, the cached view is temporarily suspended while the users are rerouted to the original definer’s rights view that the cached view was built on. When the cached view is enabled, users are transparently directed back to the actual cached view.

Disabling a Cached View

Use the ALTER CACHE DDL statement with the DISABLE option to disable a cached view. ALTER CACHE requires the CREATE CACHE or ALTER CACHE privilege on the view.

The following example disables an existing cached view and redirects users to the original definer’s rights view while the cached view is offline:

ALTER CACHE [view_catalog_name.[view_schema_name.]]view_name  DISABLE

When the cached view is disabled, the original definer’s rights view is used. During the time that the current cached view remains disabled but continues to be reported with a status of suspended, the disabled cache view displays the following behavior:

An ALTER SERVER REFRESH refreshes the cached view but does not enable the cached view. It remains disabled with a status of suspended.
A CREATE CACHE behaves normally and the cached view remains disabled in a suspended status.
An ALTER CACHE ENABLE re-enables the cached view and drops the suspended status.
An ALTER CACHE DISABLE on a cached view that is disabled in a suspended status, returns a success message.
An ALTER CACHE ENABLE on a cached view that is not disabled also returns a success message.

Enabling a Cached View

Use the ALTER CACHE DDL statement with the ENABLE option to reinstate a disabled cached view. Once a cached view is enabled, users are redirected from the original definer’s rights view to the actual cached data. ALTER CACHE requires the CREATE CACHE or ALTER CACHE privilege on the view.

The following example enables a cached view that is currently disabled and brings the cached data online:

ALTER CACHE [view_catalog_name.[view_schema_name.]]view_name ENABLE