CREATE CACHE Statement

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

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

Execute command used with BEFORE, AFTER, and CLEANUP options.

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 UPPER CASE 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.

Examples:

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