Data Services

CREATE DATA SERVICE

Description

Parameters

CREATE DATA SERVICE

Description

The

CREATE
                           [DATA] SERVICE

statement enables you to create a data service. When you create a data service, a DSN with the same name is also created.

Parameters

CREATE [DATA] SERVICE data-service-name

 TYPE data-service-type

[CATALOG [NAME] catalog-name]

[DOMAIN [NAME] domain-name]

[REGISTER [( catalog-name1 [,catalog-name2 …]) | ALL] [register-options]]

[data-service-options]

CREATE [DATA] SERVICE data-service-name

Specifies the name of the data service. The following rules apply:

The name specified cannot match the reserved name of the internal data service, BASE.
The name specified cannot match the name of an existing data service.
The name specified cannot match the name of an existing catalog for defined data services that do not support catalog names, unless the CATALOG option is used to specify a different name.

TYPE data-service-type

Specifies the data service type. Here is a list of valid data service types:

DB2UNXPC
FEDSVR
GENERIC
GENERIC_FED
GREENPLUM
HIVE
MDS
NETEZZA
ODBC
ODBC_FED
ORACLE
POSTGRES
SAP
SAPHANA
SASHDAT
SQLSVR
TERADATA

CATALOG [NAME] catalog-name

Specifies the logical catalog name associated with the data service. The catalog name must be unique. If an identical catalog name is encountered, a warning message is issued. The default logical catalog name matches the data service name if omitted for data sources that do not support catalogs.

DOMAIN [NAME] domain-name

Specifies the SAS Metadata Server domain name associated with the specified data service. If omitted, the default domain name matches the data service name. The domain is checked against the authentication server and if not valid, will return an error only if VALIDATE is specified for the data service. If VALIDATE is not specified, a warning is presented but the data service is still created.

REGISTER [(catalog-name1 [, catalog-name2...]) | ALL]

REGISTER is an optional catalog registration specification. Specify a list of catalogs to register or use the ALL keyword to register all catalogs visible to the connection.

register-options  : :=
    [ UID "userid" ]
    [ PWD 'password' ]
    [ VALIDATE [N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1]]

Note: When using register options, the “userid” requires double quotation marks and the ‘password’ requires single quotation marks:

     UID “userid”

     PWD 'password'

The data service user ID and password are used for making the connection to the data source so that the connection and catalog names can be validated.

VALIDATE specifies whether the connection, domain and catalog names are to be validated:

     [ VALIDATE [N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1]]

If UID or PWD is specified, but VALIDATE is not, VALIDATE runs with a default of TRUE. If a UID or PWD is specified and VALIDATE is specified without a value, it defaults to TRUE. If VALIDATE is specified as TRUE but UID or PWD are not specified, then personal credentials are extracted on behalf of the caller, from the domain associated with the data service. The statement returns TKTS_ERROR if credentials do not exist in the domain.

If an explicit catalog list is specified and VALIDATE is TRUE, then each catalog must exist or the statement returns TKTS_ERROR. If VALIDATE is TRUE but no catalogs are specified (including REGISTER ALL), then only the connection itself is validated since the catalog list is either empty or supplied by the connection. If an attempt is made to register a catalog that has already been registered or associated with a data service, then the statement returns TKTS_SUCCESS_WITH_INFO.

data-service-options

Specifies the list of data service options.

data-service-options ::=
       "{" OPTIONS ["(") data-service-option
                       [{ "," data-service-option } ... ] [")"]"}"

data-service-option

Specifies the data service option.

data-service-option ::=
data-service-dependent-option | data-service-independent-option

data-service-dependent-option

Data service specific options.

GENERIC

The following options apply for GENERIC data services:

 LOCAL N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1

Specifies if the data service refers to a local data source or an external database. Local sources do not require secondary authentication, and as such, specification of the DOMAIN clause results in an error if the LOCAL option is specified as one of the true values. The default is NO. This option is not persisted.

GENERIC_FED

Any options that apply to multi-catalog data services will apply to GENERIC_FED data services.

GENERIC_FED-option ::= GENERIC-option

data-service-independent-option

data-service-independent-option ::=
conopts-configuration-list | case-sensitivity-option
conopts-configuration-list ::=
CONOPTS "(" [DRIVER driver-name] [","
driver-connection-string-option ...] ")" ...

driver-name

If the driver-name option 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. Some data services such as ORACLE accept connections from the ODBC driver as well. For these data services, two CONOPTS lists can be configured, one per driver to accept connections for the two drivers. The ODBC driver accepts a CONOPTS driver connection string option. To configure this option and suboptions within it, the configuration format is CONOPTS( driver ODBC, CONOPTS( ... ) ). The inner CONOPTS option, within the parenthesis, is a list-valued driver connection string, while the outer CONOPTS option groups arbitrary driver connection string options configured for the service.

driver-connection-string-option

Specifies the connection options that correspond to the driver specified in DRIVER=driver-name. The DATA_SERVICE and CATALOG connection string options should not be specified here since they are implied by the data service and the associated configured catalogs.

case-sensitivity-option ::=
CASE_SENSITIVITY "("
OBJECT N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1 ","
COLUMN N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1 ")"

This option specifies the case sensitivity to use when comparing identifiers for security purposes. False indicates case insensitive compares while True indicates case sensitive compares are used. If not specified, the value for case sensitivity is set to the default for the data service. The defaults are TRUE (sensitive) for DB2, Oracle, and GreenPlum, and FALSE (insensitive) for all other data services. Both OBJECT and COLUMN are required when specifying CASE_SENSITIVITY.

Note: SCHEMA, CATALOG, DATA SERVICE, DSN, USER, and GROUP identifiers are always compared in a case insensitive manner.

Examples

This example creates an Oracle data service with connection options that specify a client driver and path.

CREATE SERVICE ORASERV TYPE ORACLE domain ORA1 {OPTIONS ( conopts 
( Driver odbc, conopts(DSN tktsora)), conopt (Driver oracle, PATH tktsora) ) }

Create a DB2 data service that points to a DEV1 database:

CREATE SERVICE DB2_SERVICE TYPE DB2UNXPC domain DB2 {OPTIONS
 ( conopts (DB DEV1) ) }

Create a Teradata data service with a connection option for a server:

CREATE SERVICE TERA_SERVICE TYPE TERADATA domain TERA {OPTIONS
 ( conopts (Server kaching.unx.df.com) ) }

Create an SAP data service with connection options defining host, SAP system number and active batch job processing:

CREATE SERVICE SAPSERV TYPE SAP DOMAIN SAPDOMAIN {OPTIONS
 conopts(ashost apsrv.sup.com, sysnr 03, batch 1)}

ALTER DATA SERVICE

Description

Use ALTER DATA SERVICE to change the name or options of an existing data service.

Parameters

ALTER [DATA] SERVICE data-service RENAME TO newname

ALTER [DATA] SERVICE data-service

   [ CATALOG [NAME] catalog-name ]

   [ DOMAIN [NAME] domain-name ]

  [ REGISTER [( catalog-name1 [,catalog-name2 …]) | ALL] [register-options]]

   [alter-data-service-options ]

data-service

Specifies the data service name.

newname

Specifies the new data service name.

catalog-name

Specifies the catalog name.

domain-name

Specifies the domain name.

REGISTER [(catalog-name1 [, catalog-name2...]) | ALL]

Catalog registration specification. (Optional) Specify a list of catalogs to register or ALL keyword to register all catalogs visible to the connection.

register-options ::=
         [ UID principal ]
         [ PWD password ]
         [ VALIDATE [N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1]]

UID principal userID

Data Service user principal name.

PWD password

Data service user password.

VALIDATE [N[O] | F[ALSE] | OFF | 0 | Y[ES] | T[RUE] | ON | 1]

Specifies whether the connection and the catalog names are to be validated.

VALIDATE defaults to TRUE if an argument or a Boolean value is not specified. VALIDATE also defaults to true if a UID and PWD pair are not specified. If VALIDATE is TRUE and neither UID nor PWD are specified, then a data service user principal name and password (personal credentials) are extracted on behalf of the caller from the domain associated with the data service. The statement returns TKTS_ERROR if credentials do not exist.

If an explicit catalog list is specified and VALIDATE is TRUE, then each catalog must exist or the statement returns TKTS_ERROR. If VALIDATE is TRUE but no catalogs are specified (including REGISTER ALL), then only the connection itself is validated since the catalog list is either empty or supplied by the connection.

alter-data-service-options

Specifies the list of data service options to alter.

   alter-data-service-options::=

      "{" OPTIONS ["(") alter-data-service-option

                 [{ "," alter-data-service-option } ...] 
                                                            [")"]"}"

alter-data-service-option

Specifies the data service option to alter.

     alter-data-service-option ::=

        [ alter-operation ]data-service-option

data-service-option

Specifies the data service option.

     data-service-option::= 
conopts-configuration-list

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. Some data services such as ORACLE accept connections from the SAS Federation Server Driver for ODBC as well. For these data services, two CONOPTS lists can be configured, one per driver to accept connections for the two drivers. The SAS Federation Server Driver for ODBC accepts a CONOPTS driver connection string option. To configure this option and suboptions within it, the configuration format is CONOPTS( driver ODBC, CONOPTS( ... ) ). The inner CONOPTS option, within the parenthesis, is a list-valued driver connection string, while the outer CONOPTS option groups arbitrary driver connection string options configured for the service.

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. The DATA_SERVICE and CATALOG connection string options should not be specified here since they are implied by the data service and its configured catalogs. For a list of valid connection string options, see the driver reference topic for your specific driver.

Autoindex ON|OFF

This option is transient and valid for the SQL_LOG data service only. Specifies whether to create indexes (ON) or to drop indexes (OFF) for the EVENTS table used for SQL Logging. The default is ON.

Examples

This example is altering the ORACLE3 data service to define driver and DSN connection options:

ALTER DATA SERVICE ORACLE3 {OPTIONS conopts(Driver odbc,
 ODBC_DSN tktsora)}

Rename the ORACLE3 data service to ORACLE3_VER2:

ALTER DATA SERVICE ORACLE3 RENAME TO ORACLE3_VER2

This example drops the ODBC driver from the ORACLE3_VER2 data service:

ALTER DATA SERVICE ORACLE3_VER2 {OPTIONS DROP conopts(driver odbc) }

Define a catalog for the SERVICE1 data service:

ALTER SERVICE SERVICE1 CATALOG NEWCATALOG

DROP DATA SERVICE

Description

Use DROP DATA SERVICE to drop a specific data service.

Parameters

DROP [DATA] SERVICE data-service-name [drop-disposition]

data-service-name

Specifies the name of the data service to drop.

drop-disposition

Specifies the drop disposition as 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

This example drops the ORACLE3 data service:

drop DATA SERVICE ORACLE3

This example drops the MYSQL_SERVICE data service and all objects associated with it:

drop service "MYSQL_SERVICE" cascade force

This example drops the ORACLE1 data service and suppresses error messages:

drop data service ORACLE1 cascade

Last updated: March 6, 2018