Catalogs and Schemas :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

CREATE CATALOG

Description

Use CREATE CATALOG to create a catalog under a specific data service.

Tip

Use double quotation marks when specifying a catalog name.

Parameters

CREATE CATALOG "catalog" UNDER data-service

  [ NATIVE NAME native-name ]

   [ create-catalog-options ]

“catalog”

Specifies a logical catalog name. Surround the catalog name in double quotation marks.

data-service

Specifies the data service name under which the catalog is to be created.

native-name

Specifies the native catalog name if the native catalog name is not unique within the server. The native name is used resolve catalog name collisions between multiple data services that support catalogs. Client SQL always references the catalog through the logical catalog name regardless of whether a native name is specified. The native catalog is treated as a missing value if it reflects the same name as the logical catalog.

create-catalog-options

Specifies the options to create a catalog. This option only applies to the BASE data service.

create-catalog-options ::= 
create-catalog-options::=
conopts-configuration-list

conopts-configuration-list

If DRIVERdriver-name is omitted, the default driver for the data service is assumed. Associated options within the CONOPTS list are used for connections using the appropriate driver. The multiple driver syntax is not supported.

conopts-configuration-list::=
CONOPTS "(" [DRIVERdriver-name] ["," driver-connection-string-option ...] ")" ...

driver-name

Specifies the driver name.

driver-connection-string-option

Specifies the connection options that correspond to the specified driver. For a list of valid connection string options, see the driver reference topic for your specific data source.

Examples

These examples create a catalog under a specific data service:

CREATE CATALOG "catalog1_BASE" UNDER BASE

CREATE CATALOG "TKTEST" UNDER SQLServer1

This example creates a logical catalog (CATALOG1) and a native catalog (TKTEST) under the SQLSERVER1 data service:

CREATE CATALOG "Catalog1" UNDER SQLServer1 NATIVE NAME "TKTEST"

Catalog C1 created under a BASE data service with the COMPRESS connection option that activates row compression for SAS data sets:

CREATE CATALOG "c1" UNDER BASE {OPTIONS conopts (COMPRESS YES)}

ALTER CATALOG

Description

Use ALTER CATALOG to change the name of a catalog, rename a native catalog, or alter options.

Parameters

ALTER CATALOG "catalog" RENAME TO "newcatalogname"

ALTER CATALOG "catalog"

   [ NATIVE NAME "native-name" ]

   [ alter-catalog-options ]

“catalog”

Specifies the name of the existing catalog.

“newcatalogname”

Specifies the new catalog name.

“native-name”

Specifies the name of the native catalog.

alter-catalog-options

Specifies the options to alter the catalog. This option only applies to the BASE data service. The syntax for alter-catalog-options is the same as the syntax for alter-generic-options. All create-catalog-options are also supported.

Examples

Here are examples of the ALTER CATALOG statement:

ALTER CATALOG "catalog3_BASE" RENAME TO "catalog3_BASE_RENAME"

ALTER CATALOG "Catalog3" NATIVE NAME "TKTEST3_RENAME"

ALTER CATALOG "catalog1_BASE" {OPTIONS add CONOPTS(DRIVER BASE, ACCESS READONLY)}

ALTER CATALOG "catalog1_BASE" {OPTIONS set (CONOPTS(DRIVER BASE, ACCESS READONLY))}

ALTER CATALOG "catalog1_BASE" {OPTIONS xset CONOPTS(DRIVER BASE, COMPRESS YES)}

ALTER CATALOG "catalog1_BASE" {OPTIONS  drop CONOPTS(DRIVER BASE)}

DROP CATALOG

Description

Use the DROP CATALOG statement to drop a catalog.

Parameters

DROP CATALOG "catalog" [ drop-disposition ]

“catalog”

Specifies the catalog name. Catalog name requires double quotation marks.

drop-disposition

drop-disposition ::=
{RESTRICT | CASCADE} [FORCE]

Specifies a drop disposition as one of the following values:

RESTRICT	Specifies that the drop target is empty. This is the default value.
CASCADE	Specifies that container objects are dropped.
FORCE	Specifies the optional FORCE keyword that will suppress error messages when the data service does not exist. This additional option does not affect the performance of the RESTRICT or CASCADE options.

Examples

This example drops “CATALOG3”:

DROP CATALOG "catalog3"

This example drops “CATALOG1_BASE” and all associated container objects:

DROP CATALOG "catalog1_base" cascade

CREATE SCHEMA

Description

Use the CREATE SCHEMA statement to create a schema and designate an owner.

Parameters

CREATE SCHEMA [ "catalog"."schema"]

   [ AUTHORIZATION|OWNER owner ]

   [ create-schema-options ]

“catalog”

Specifies the optional catalog name under which to create the schema. This is useful for data sources that support catalog names. For those that do not, the catalog name must be the logical catalog name which defaults to the name of the data service.

“schema”

Specifies the name of the schema.

owner

Authorization identifier of the schema owner. If the AUTHORIZATION clause is not specified, schema ownership defaults to the SYSTEM user account. However, schema ownership by the SYSTEM user account could present problems with FedSQL views and data cache.

create-schema-options

Specifies an option list for the schema.

create-schema-options ::=

"{" OPTIONS ["("] schema-option

[ { "," schema-option } ... ] [")"] "}"

schema-option

Specifies the syntax for schema options. This option only applies to the BASE data service.

conopts-configuration-list

If DRIVER driver-name is omitted, the default driver for the data service is assumed. Associated options within the CONOPTS list are used for connections using the appropriate driver.

conopts-configuration-list ::=
CONOPTS "(" [DRIVER driver-name] [","driver-connection-string-option ...] ")"...

driver-name

Specifies the driver name.

driver-connection-string-option

Specifies the connection options that correspond to the driver which is specified in DRIVERdriver-name. For a list of valid connection string options, see the driver reference topic for your specific data source.

PRIMARYPATH path

Specifies the physical location for the SAS library, which is a collection of one or more SAS files. For example, in directory-based operating environments, a SAS library is a group of SAS files that are stored in the same directory. This option is required for BASE schemas.

PRIMARYPATH path ::=

      quoted-identifier

quoted-identifier

Specifies a single quoted or double quoted name.

Examples

Here are examples of the CREATE SCHEMA statement:

CREATE SCHEMA "catalog1_BASE"."schema1_BASE" {OPTIONS (primarypath 
'C:\schema1_BASE')}

CREATE SCHEMA "ORACLE1"."TKTSTST1"

CREATE SCHEMA "catalog1"."schema1" {OPTIONS primarypath 'C:\my_schema', 
conopts (LOCKTABLE EXCLUSIVE)}

ALTER SCHEMA

Description

Use the ALTER SCHEMA statement to change the name or options of an existing schema. You can also alter advanced options that are driver–specific.

Parameters

ALTER SCHEMA [ "catalog"."schema" ]  RENAME TO "newschema"

ALTER SCHEMA [ "catalog"."schema" ] AUTHORIZATION|OWNER TO owner

[ create-if option ]

ALTER SCHEMA [ "catalog"."schema" ]

[ alter-schema-options ]

“catalog”

Specifies the catalog name.

“schema”

Specifies the schema name to change.

“newschema”

Specifies the new schema name.

alter-schema-options

Specifies what options to alter in the schema.

 alter-schema-options ::=

   "{" OPTIONS ["("] alter-schema-option

[{"," alter-schema-option} ... ] [")"]"}"

alter-schema-option

Specifies the schema option to alter. This option only applies to the BASE data service.

 alter-schema-option ::=

[DROP schema-option-name ]
     [{ADD | SET} schema-option ]
       [create-if-option

schema-option

Specifies the syntax for schema options.

conopts-configuration-list

If DRIVERdriver-name is omitted, the default driver for the data service is assumed. Associated options within the CONOPTS list are used for connections using the appropriate driver.

conopts-configuration-list ::= CONOPTS "(" [DRIVER driver-name]
 ["," driver-connection-string-option ...] ")" ...

driver-name

Specifies the driver name.

driver-connection-string-option

Specifies the connection options that correspond to the driver which is specified in DRIVERdriver-name. For a list of valid connection string options, see the driver reference topic for your specific data source.

PRIMARYPATH path

Specifies the physical location for the SAS library, which is a collection of one or more SAS files. For example, in directory-based operating environments, a SAS library is a group of SAS files that are stored in the same directory. This option applies to BASE schemas only and is required.

path ::= quoted-identifier

quoted-identifier

Specifies a single quoted or double quoted name.

create-if-option

Creates the schema if it does not already exist using the remaining options.

create-if-option
CREATE_IF N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1

Examples

Here are examples of the ALTER SCHEMA statement:

ALTER SCHEMA "catalog1_BASE"."schema3_BASE" RENAME TO "schema3_BASE_RENAME"

ALTER SCHEMA "catalog1_BASE"."schema3_BASE" {OPTIONS set primarypath 'C:\mydir'}

ALTER SCHEMA "catalog1_BASE"."schema3_BASE" {OPTIONS add conopts (LOCKTABLE SHARE)}

DROP SCHEMA

Description

Use DROP SCHEMA to drop a schema.

Parameters

DROP SCHEMA [ "catalog"."schema" ]  [ drop-disposition ]

“catalog”

Specifies the catalog name.

“schema”

Specifies the schema name.

drop-disposition

Specifies the drop disposition and is one of the following values:

drop-disposition ::=
{RESTRICT | CASCADE} [FORCE]

RESTRICT	Specifies that the drop target is empty. This is the default value.
CASCADE	Specifies that contained objects are dropped.
FORCE	Specifies the optional FORCE keyword that will suppress error messages when the data service does not exist. This additional option does not affect the performance of the RESTRICT or CASCADE options.

Examples

Here are examples of the DROP SCHEMA statement:

DROP SCHEMA "catalog1_BASE"."schema1_BASE"

DROP SCHEMA "catalog1_BASE"."schema1_BASE" force