Cache

CREATE CACHE

Description

Parameters

CREATE CACHE

Description

Use the CREATE CACHE statement to create a cache definition for a specific view. You can also specify options for the cache.

Parameters

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

[{OPTIONS SAS-table-option=value [ ... SAS-table-option=value ]}]

[USING ( fedsql_syntax_sql )]

[WITH COMMENT '…']

[DEFERRED]

[FORCE]

[EXEC|EXECUTE]

[BEFORE  (fedsql_syntax_sql) [FORCE]

       [,(fedsql_syntax_sql) [FORCE] […]]]

[AFTER  (fedsql_syntax_sql) [FORCE]

       [,(fedsql_syntax_sql) [FORCE] […]]]

[CLEANUP  (fedsql_syntax_sql) [FORCE]

       [,(fedsql_syntax_sql) [FORCE] […]]]

[SET] option_name = ’string_literal’ | numeric_literal

           | ON | OFF | YES | NO | TRUE | FALSE ]

“catalog”

Specifies the catalog name of the view to be cached.

“schema”

Specifies the schema name of the view to be cached.

“view”

Specifies the name of the cache view.

“cache-catalog”

Specifies the catalog name under which to create the cache.

“cache-schema”

Specifies the schema name under which to create the cache.

{OPTIONS SAS-table-option=value... }

Specifies the table options to use when the data cache table is created and populated.

[USING ( fedsql_syntax_sql )]

The optional USING clause provides a way for users to control how the cache table is created. The user must ensure that it is compatible with the view data cache table.

WITH COMMENT ‘...’

Text comments stored with the cache definition.

DEFERRED

The cache definition is stored but a cache table is not created or populated with data. Issue a separate REFRESH CACHE command to create and populate the cache table.

FORCE

The cache table and definition is retained even if an error occurs during REFRESH.

EXEC | EXECUTE

Use the EXEC | EXECUTE command with the BEFORE, AFTER, and CLEANUP options as specified below.

BEFORE

BEFORE (fedsql_syntax) [FORCE]

The statements in fedsql_syntax will be executed before the cache table is created and populated. The FORCE option suppresses any errors.

AFTER

AFTER (fedsql_syntax) [FORCE]

The statements in fedsql_syntax will be executed after the cache table is created and populated. The FORCE option suppresses any errors.

CLEANUP

CLEANUP (fedsql_syntax) [FORCE]

The statements in fedsql_syntax will be executed only in the event of an error during creation or population of the cache table. The FORCE option will suppress any errors.

Use the following keywords with the USING, BEFORE, AFTER, and CLEANUP clauses. The keywords must be in UPPERCASE and contain no blank spaces with the brackets.

{CACHE}

Expands to a fully qualified cache table name using “double quotation marks”.

{CACHE_CATALOG}

Expands to a cache catalog name. Does not use quotation marks.

{CACHE_SCHEMA}

Expands to a cache schema name. Does not use quotation marks.

{CACHE_TABLE}

Expands to a cache table name. Does not use quotation marks.

SET

SET	option_name=value

Specifies additional options and values to use during cache creation and population. The options are listed below.

ERRLMIT

Sets a limit on the number of errors to allow before a statement stops inserting data.

DBCOMMIT

Sets a limit on the number of modified rows to commit at one time, which affects transaction logging limits on the back-end database. This option overrides the ERRLIMIT option.

INSERTBUFF

Sets a limit for the number of rows inserted at a time which places a limit on a driver's row array size when inserting data.

CT_PRESERVE

Sets the CT_PRESERVE connection string option which controls how data types are mapped between the source view and the cache table. Valid options are FORCE|FORCE_COL|FORCE_COL_SIZE|STRICT|SAFE.

RESTART

RESTART='REFRESH'

REFRESH sets active or deferred caches to automatically refresh after each server start up. Suspended or disabled caches are not affected.

Note: FedSQL requires that option values be enclosed in single quotation marks. Any other value for RESTART= will produce an error. This value is case sensitive.

Examples

Here is an example of the CREATE CACHE statement:

CREATE CACHE ORACLE_SERVICE.TKTSTST1.view_red in "SAMPLE"."tktstst1"
     set CT_PRESERVE='SAFE'

ALTER CACHE

Description

With the ALTER CACHE statement, you can disable, enable or refresh cache tables. ALTER CACHE requires the CREATE CACHE or ALTER CACHE privilege.

Parameters

ALTER CACHE "catalog"."schema"."view" OPTION

“catalog”.’schema”.”view”

Specifies the catalog, schema and name of the view.

OPTION

Specifies an option for the statement as one of the following:

DISABLE

Disables use of the specified cache. References to the view use the view rather than the cached data.

ENABLE

Enables use of the specified cache after having been disabled.

REFRESH

Refreshes the cache table for the specified view.

Examples

Here are examples of the ALTER CACHE statement:

ALTER CACHE "catalog1"."schema1"."view1" DISABLE

ALTER CACHE [ "catalog2"."schema2". ] view2 ENABLE

ALTER CACHE [ "catalog3"."schema3". ] view3 REFRESH

DROP CACHE

Description

Use DROP CACHE to drop a cached view.

Parameters

DROP CACHE [ "catalog"."schema". ] view [FORCE ]

“catalog”.”schema”

Specifies the catalog and schema of the data cache view.

view

Specifies the name of the data cache view.

FORCE

Suppresses error messages if the cache to be dropped does not exist.

PURGE CACHE

Description

PURGE CACHE forces the removal of cache tables that are no longer needed. The ability to purge a cache is limited to system users, administrators, and users who have CREATE CACHE permission set for a server object.

Note: The PURGE CACHE statement is not the same as the PURGE CACHE option in the ALTER SERVER statement. Only system users or administrators (those with ADMINISTER privilege) on the SAS Federation Server can execute the PURGE CACHE option with the ALTER SERVER statement.

Parameters

PURGE CACHE

Last updated: March 6, 2018