CREATE CACHE Statement

This statement creates a cache definition for the specified view in the cache catalog and schema with the specified options.

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. A separate REFRESH CACHE command is needed to create and populate the cache table.

FORCE

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

EXEC | EXECUTE

Command used with BEFORE, AFTER, and CLEANUP options.

BEFORE	The statements in fedsql_syntax will be executed before the cache table is created and populated. The FORCE option will suppress any errors.
AFTER	The statements in fedsql_syntax will be executed after the cache table is created and populated. The FORCE option will suppress any errors.
CLEANUP	The statements in fedsql_syntax will be executed in the event of an error during creation or population of the cache table. The FORCE option will suppress any errors.

[SET] <option name> = <value>

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

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.

Notes:

Use the following keywords with the USING, BEFORE, AFTER, and CLEANUP clauses only. The keywords must be UPPER CASE with no blank spaces within the braces {....}.

{CACHE}

Expands to fully qualified cache table name using quotation marks, for example: “catalog”.”schema”.”cache-table”

{CACHE_CATALOG}

Expands to cache catalog name. Do not use quotation marks.

{CACHE_SCHEMA}

Expands to cache schema name. Do not use quotation marks.

{CACHE_TABLE}

Expands to cache table name. Do not use quotation marks.

Examples:

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