The CREATE DATA SERVICE
statement enables you to create a data service using the following
syntax:
CREATE [DATA] SERVICE data-service
[CATALOG [NAME] catalog-name]
[DOMAIN [NAME] domain-name]
[REGISTER [( catalog-name1 [,catalog-name2 …]) | ALL]
CREATE [DATA] SERVICE data-service
Specifies the data
service name. The following rules apply:
-
The specified name must not match
the reserved name of the internal data service, BASE.
-
The specified name must not match
the name of any existing data service.
-
The specified name must not 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. One of:
DB2UNXPC
GENERIC
GENERIC_FED
GREENPLUM
ODBC
ODBC_FED
ORACLE
MDS
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 this data service. If omitted, the default
domain name matches the data service 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 "userid" ]
[ PWD 'password' ]
[ VALIDATE [N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1]]Note: When using register options,
the UID requires double quotation marks and the PWD requires single
quotation marks.
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 not specified or if no Boolean value keyword is specified
and either UID or PWD is specified. If VALIDATE is true and neither
UID, nor PWD are specified, then a user ID 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 no such credentials 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.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.
"{" OPTIONS ["(") data-service-option [{ "," data-service-option } ... ] [")"]"}"
data-service-option
Specifies the 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, see the Driver Reference
chapter.
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 option format would be CONOPTS( driver ODBC, conopts(
... ) ). The inner CONOPTS option happens to be a specific driver
connection string (list-valued) option while the outer CONOPTS 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 will be used. If not specified, the value
for case sensitivity will be set to the default for the data service.
(True/Sensitive for DB2, Oracle, and GreenPlum – False/Insensitive
for all other 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.
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)}