Data Caching

Overview

Views and Caching

Requirements for Cached Views

Working with Cached Views

Overview

Creating a Cache

Alter a Cache

Disabling and Enabling Caches

Purge Cache

Drop Cache

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 FedSQL definer'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. 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 cache for the view is dropped.

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: The following table 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.
VS_Owner (View Schema Owner) is the owner of the schema where the cached view resides.
CS_Owner (Cache Schema Owner) is the owner of the schema where the cache tables reside.

Requirements for Cached Views
Who	Action	Privilege on Object
User	Create a cache Create or drop cached views within a schema Refresh cached views within the schema	CONNECT on ADMIN DSN CREATE CACHE on the VS ALTER CACHE on the VS
CS_Owner – owns all cache tables in its schema, and this identity is used for executing the view and saving the cached data.	Assigned to CS object Execute, Save cached data Must have a database login to the database of the cached tables if the cached location requires credentials.¹	CONNECT on Data service that contains cache tables.
VS_Owner – owner of the schema where the cached view resides.	Cache results sets in the CS for views owned by the VS_Owner.²	CREATE TABLESPACE on CS

¹ The CS_Owner 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 CS_Owner user during cache creation and refresh, and the CS_Owner must be able to select from the original view. During data cache connections, the CS_Owner connects to the databases that contain the CS and the VS using a credential search order (CSO) of "PERSONAL, SHARED".

² The server assumes the identity of the CS_Owner 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.

Note: Administrators implicitly have all privileges, including CREATE CACHE and ALTER CACHE.

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 SAS Federation Manager: User’s Guide.

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

Creating a Cache

CREATE CACHE "catalog"."schema"."view"
                                    IN "cache-catalog"."cache-schema"

Privileges: CREATE CACHE

Information Views: CACHES, OBJECTS, CONFIG_OBJECTS

Use the CREATE CACHE DDL statement to cache a definer's rights view or change an existing cache definition. 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.

Several options are available with the CREATE CACHE statement. For example, you can specify that a cache refreshes at server start up by specifying the

SET
                                       RESTART=’REFRESH’

when creating the cache.

Alter a Cache

ALTER CACHE "catalog"."schema"."view"
                                    REFRESH | DISABLE | ENABLE

Privileges: CREATE CACHE, ALTER CACHE (On the view)

Information Views: CACHES

To alter an existing cache, use the ALTER CACHE DDL statement. 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. Use DISABLE and ENABLE to temporarily disable caches.

Disabling and Enabling Caches

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.

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.

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

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

Purge Cache

PURGE CACHE

Privileges: System user, Administrator, CREATE CACHE (on the server object)

Purge Cache forces the removal of cache tables that are no longer in use. Only system users, administrators, or those with CREATE CACHE privilege on the server object 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.
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

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

Privileges: CREATE CACHE (on the view)

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.

Last updated: March 6, 2018