CREATE DATA SERVICE Statement

The

CREATE
                  [DATA] SERVICE

statement enables you to create a data service using the following syntax:

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 specified name cannot match the reserved name of the internal data service, BASE.
The specified name cannot match the name of an existing data service.
The specified name 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

GENERIC

GENERIC_FED

GREENPLUM

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 Authentication 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 may 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. For details about the specific options for your data source, see Database Functionality and Driver Performance.

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 which is 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 its 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:

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

CREATE DATA SERVICE SQLServer1 TYPE SQLSVR domain SQLSERVER {OPTIONS 
( conopts ( conopts(DSN tktssql)) ) }

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

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

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