Working with Data Services

Overview of Data Services

Creating a Data Service

Overview

Native Catalog Support

Identifier Case Sensitivity

Database Login Prerequisite

Creating a Data Service with Administration DDL

Driver Search Order

Overview of Data Services

To access data, the administrator must create and configure data services for SAS Federation Server. Data services contain connection information and driver specifics to connect with data sources such as Oracle or Base SAS data sets.

Data services contain information that identifies the location of tables residing in your data source. If a data source does not support native catalogs, SAS Federation Server enables you to define a logical catalog name to use as an SQL identifier. This allows unique identification of each data source when performing heterogeneous operations.

Data services that require logins must be associated with a domain in SAS Metadata Server. When users connect to the data source through a data source name (DSN), the domain name is used to retrieve user credentials associated with that data service. The credentials are then passed along to the back-end database. User credentials are stored in SAS Metadata Server.

Data services can also contain optional information to control SAS Federation Server driver behavior, such as locking semantics and tracing. Data services form the foundation for connectivity to a data source and you can assign privileges that control user access to the data. However, relational databases provide authorization that limits the operations that can be performed on the data. SAS Federation Server respects authorizations that are defined and enforced on a third-party database. Authorizations defined on a third-party database overrule permissions and privileges that are set on SAS Federation Server.

Creating a Data Service

Overview

You can use administration DDL or SAS Federation Server Manager to create a data service. A new DSN is automatically generated each time a new data service is created. This is a standard DSN given the same name as the data service. However, if the DSN name already exists on the server, it will not be created. If the data service is renamed, the DSN name remains unchanged.

By default, a BASE data service is created the first time that SAS Federation Server is started. Only one BASE data service can exist in a SAS Federation Server installation, and it cannot be modified or deleted.

Native Catalog Support

When creating a data service for a data source that supports native catalogs, and using the REGISTER option, the server attempts to connect to the database to acquire a list of catalogs. Credentials are required to secure the connection. If the connection cannot be made, creation of the data service fails. The same requirement for pre-registered credentials applies when creating a data service to ODBC with native catalog support, or for any data services that support native catalogs.

Identifier Case Sensitivity

When creating an ODBC data service, the server must query the data source to acquire its identifier case sensitivity property. The identifier case sensitivity property is used to create security entries in the server’s system tables and is stored with the data service.

Database Login Prerequisite

Due to the requirement for a database connection described above, the following database login prerequisite applies to SQLSERVER, ODBC and ODBC_FED data services. These actions must be completed before creating the data service, and must be accomplished in SAS Metadata Server.

A database login must be registered in the domain that will be associated with the new data service.
The domain must be registered in SAS Metadata Server for it to be accessible when creating the data service with administration DDL or SAS Federation Server Manager.

Creating a Data Service with Administration DDL

Here is an example of the DDL statement, CREATE DATA SERVICE. See CREATE DATA SERVICE DDL for details and a complete list of options. You can also alter and drop data services using DDL.

CREATE [DATA] SERVICE data-service
TYPE data-service-type
[CATALOG [NAME] catalog-name]
[DOMAIN [NAME] domain-name]
[REGISTER [( catalog-name1 [,catalog-name2 …]) | ALL]
[register-options]]
[data-service-options]

Use CATALOG to register a catalog name for data sources that do not support native catalogs.

Use REGISTER to register the native catalog names for those data sources that support native catalogs, such as SQL Server. If an identical catalog name is encountered, a warning message is issued and the catalog is not registered. In this case use the CREATE CATALOG DDL statement to provide a mapped name for the native name.

Driver Search Order

If specifying more than one driver when creating a data service, the first driver listed in the statement is used as the default driver. Here is an example data service definition:

CREATE DATA SERVICE ORACLE_TEST TYPE ORACLE CATALOG ORACLE_TEST DOMAIN ORA1
 {OPTIONS CONOPTS (DRIVER ORACLE, PATH TKTSORA ), CONOPTS (DRIVER ODBC,
     ODBC_DSN TRAFFIC)}

In this case, the native ORACLE driver is the default. To change the default driver, you must first drop the drivers from the data service using ALTER SERVICE DDL. After dropping the drivers, add the drivers to the data service putting the default driver first. In the following example, two drivers, ORACLE and ODBC, are dropped from the ORACLE_TEST data service:

ALTER DATA SERVICE ORACLE_TEST {OPTIONS DROP CONOPTS(DRIVER ORACLE), DROP
CONOPTS(DRIVER ODBC)}

After the drivers are dropped, add the drivers again, specifying ODBC first so that it becomes the default driver:

ALTER DATA SERVICE ORACLE_TEST {OPTIONS ADD CONOPTS ( DRIVER ODBC, ODBC_DSN
TRAFFIC ), ADD CONOPTS ( DRIVER ORACLE, PATH TKTSORA ) }

Last updated: March 6, 2018