CREATE DSN Statement

The CREATE DSN statement enables you to create a standard, single-service DSN or a federated DSN. A federated DSN is created to group one or more standard DSNs.

Here is the syntax to create a standard DSN:

CREATE DSN dsn-name

UNDER data-service

[DESCRIPTION ‘description text’]

[CONNECT ‘driver-connection-string-options’]

[create-dsn-options]

[AS ADMINISTRATOR]

Here is the syntax to create a federated DSN:

CREATE DSN dsn-name

[DESCRIPTION ‘description text’]

[create-dsn-options]

ADD "(" dsn-name
["," ...] ")"

[AS ADMINISTRATOR]

dsn-name

Specifies the DSN name (required). Quotation marks surrounding the DSN name are optional.

CREATE DSN dsn-name

data-service

Specifies the data service name. This option only applies to a standard DSN and is required.

UNDER data-service-name

The following naming rules apply:

The specified name must not match the reserved names of the internal BASE data service.
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.

DESC[RIPTION] ‘description-text’

Description of the DSN surrounded in single quotation marks. Use for a standard or federated DSN (optional).

[DESC 'description text']

CONNECT driver-connection-string-options

Specifies the connection string options.

[CONNECT driver-connection-string-options']

The Federation Server driver connection string options are an extension of the ODBC syntax that specifies options as semicolon-delimited key=value pairs. For more information about which connection options and advanced options are supported for each data service, see the driver reference topic for your data source.

create-dsn-options

Specifies what options are included with the DSN.

dsn-config-options

Specifies the options to configure with the DSN.

 dsn-config-options ::=
  "{" OPTIONS ["("] dsn-config-option
       [{"," dsn-config-option} ... ] [")"]"}"

dsn_config_option

Specifies the DSN configuration option as one of the following:

FEDSQL Y[ES] | T[RUE] | 1 | N[O] | F[ALSE] | 0

Specifies whether to use the FedSQL dialect. BASE DSN connections always use FedSQL. Therefore, FedSQL cannot be turned off for BASE DSNs. The FEDSQL option applies to both standard and federated DSNs.

SECURITY Y[ES] | T[RUE] | 1 | N[O] | F[ALSE] | 0

YES is the default value. Specifies whether to secure SQL statements before processing them. For example, if a DSN is defined to use SECURITY NO, Federation Server security is bypassed. Therefore, when you connect with the DSN, you are connecting with the privileges granted at the data source level. If a DSN is defined to use SECURITY YES, privileges granted through the Federation Server will be enforced in addition to those of the underlying data source. Used in conjunction with CSO SHARED, this feature facilitates management of a more granular security policy in the Federation Server over a less granular one in the back-end database.

If SECURITY is set to YES, FEDSQL is automatically set to YES.

The SECURITY option applies to both standard and federated DSNs. It corresponds to the Federation Server SQL Authorization Enforcement setting that is displayed for the DSN through the Federation Server Manager.

On a standard DSN, SECURITY NO will ignore any SQL privileges configured in the Federation Server. SECURITY YES enforces SQL privileges. See Security Permissions in the Federation Server Authorization section for a list of privileges that are affected by the SECURITY setting.

On a federated DSN, SECURITY NO indicates that the security setting of the child DSN is used. A setting of NO allows each child DSN to operate under the security settings that are configured for it. Setting SECURITY to YES activates SQL privilege enforcement for all of the child DSNs affiliated with the federated DSN. Effectively, the SECURITY setting on a federated DSN can demand privilege enforcement on child DSNs, but cannot be used to remove it.

CREDENTIALS_SEARCH_ORDER | CSO "(" cso-value [ {"," cso-value} ... ] ")"

cso-value ::= PERSONAL |SHARED

Specifies whether to use back-end credentials owned by the current user (PERSONAL) or shared among many users (SHARED). The DSN can be configured to search for either in the order specified. If a search is not specified, the default is CSO (PERSONAL,SHARED). Credentials Search Order applies to a standard DSN only.

For example, if a user owns a database login and a DSN is configured with a CSO value of PERSONAL, the DSN will use that user's database login to connect to the database. However, when the DSN is configured using CSO SHARED and the user is configured as an authorized consumer of a shared login, the DSN connects using the shared login credentials. Users, logins, and shared logins are created and managed using the Authentication Server. For more information, see the DataFlux Authentication Server User's Guide.

AS ADMINISTRATOR

[ AS ADMINISTRATOR ]

Creates the DSN using the ADMINISTRATOR role as the owner. With the ADMINISTRATOR role, the DSN is owned by the individual user. If the user is SYSTEM, the DSN is owned by SYSTEM. 'AS ADMINISTRATOR ' is optional and can be used in a standard or federated DSN.

Examples:

CREATE DSN "DSN1" UNDER BASE DESCRIPTION 'creating DSN1' NOPROMPT
'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA="schema1"' {OPTIONS (FEDSQL
NO,SECURITY NO)}

CREATE DSN BASEDSN under BASE NOPROMPT 'DATA_SERVICE=BASE;
LOCKTABLE=EXCLUSIVE'

CREATE DSN BASEDSN under BASE CONNECT 'CATALOG="catalog1_BASE";
LOCKTABLE=EXCLUSIVE'

CREATE DSN BASEDSN under BASE NOPROMPT '(CATALOG="catalog1_BASE";
LOCKTABLE=SHARE);(CATALOG="catalog2_BASE";LOCKTABLE=EXCLUSIVE)'

CREATE DSN BASEDSN under BASE NOPROMPT 'CATALOG="catalog1_BASE";
LOCKTABLE=SHARE;SCHEMA=(NAME="schema1_BASE";LOCKTABLE=EXCLUSIVE)'

CREATE DSN BASEDSN under BASE CONNECT 'CATALOG="catalog1_BASE";
LOCKTABLE=SHARE;SCHEMA=(NAME="schema1_BASE";LOCKTABLE=EXCLUSIVE);
SCHEMA=(NAME="schema2_BASE";ACCESS=TEMP)'

CREATE DSN MYDSN under MYSERV {OPTIONS CREDENTIALS_SEARCH_ORDER
(PERSONAL)}

CREATE DSN MYDSN under MYSERV {OPTIONS CREDENTIALS_SEARCH_ORDER
(PERSONAL, SHARED)}

CREATE DSN ORADSN UNDER ORASERVICE CONNECT 'ORA ENCODING=UNICODE;
ORANUMERIC=YES'

CREATE DSN MYFEDERATED_DSN ADD mydsn1, mydsn2, mydsn3) 
AS ADMINISTRATOR