FedSQL :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

About FedSQL

FedSQL is the implementation of SQL that SAS Federation Server uses to access relational data. FedSQL is designed to be ANSI SQL:1999 core compliant with some extensions. For applications, FedSQL provides a common SQL syntax across all data sources. That is, FedSQL is a vendor-neutral SQL dialect that accesses data from various data sources without requiring the application to submit queries in the SQL dialect that is native to the data source. In addition, a single FedSQL query can target data in several data sources and can return a single result set. When possible, FedSQL queries are optimized with multi-threaded algorithms to resolve large-scale operations.

FedSQL is a requirement for many functions on SAS Federation Server such as data federation, cached views, row-level security, and data quality.

For complete FedSQL statement reference, see the SAS FedSQL Language Reference.

About the FedSQL Language Driver

The FedSQL language driver supports the FedSQL dialect. When loaded, the FedSQL driver parses SQL requests, and then sends the parsed query to the appropriate SAS Federation Server driver to determine whether the functionality can be handled by the data service. The FedSQL driver includes an SQL processor, which supports the FedSQL dialect. The primary function of the FedSQL driver is to support federation of data sources. if an SQL submission is requesting data from DB2 to be joined with data from Oracle, the SQL processor requests the data from the data sources and then performs the join in SAS Federation Server. The FedSQL driver supports the FedSQL dialect over any data source. For example, if the SQL request is from a single data source that does not support a particular SQL function, the FedSQL processor guarantees implementation of the request.

See the FedSQL Driver Reference for a complete list of connection options.

Invoking the FedSQL Dialect

To invoke FedSQL, configure a DSN using these scenarios::

Specify the dialect in a DSN using CREATE DSN with the LANG connection option. Federated DSNs require that the FedSQL dialect be set. For BASE DSNs, the dialect defaults to FedSQL.
```
CREATE DSN "DSN1" UNDER BASE DESCRIPTION 'creating DSN1' NOPROMPT
'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA="schema1"' {OPTIONS (LANG YES)}
```
Note: You can execute the only syntax supported by the language based on the dialect specified in the LANG option. You cannot execute FedSQL with DS2 dialect, and you cannot execute DS2 statements using the FedSQL dialect.
Specify Invoking the FedSQL Dialect by setting SECURITY to YES on a DSN. When SECURITY is set to YES, FedSQL is automatically set to YES.
```
CREATE DSN "DSN1" UNDER BASE DESCRIPTION 'creating DSN1'
 NOPROMPT 'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA=(name="schema1_BASE")'
{OPTIONS(SECURITY YES)}
```

See CREATE DSN DDL for details about DSN options.

Federation Server SQL Authorization Enforcement

When Federation Server SQL Authorization Enforcement is enabled, the FedSQL driver is enabled, and the SQL dialect is automatically set to FedSQL. With FedSQL an additional layer of object-level security is enabled for the connection and SQL statements are secured before processing them. If Federation Server SQL Authorization Enforcement is disabled, object-level security is bypassed and the user is granted all privileges regardless of what the user has been granted or denied.

When Federation Server SQL Authorization Enforcement is disabled in SAS Federation Server Manager, an administrator has the option to choose native dialect, which is the dialect of the underlying data source. For example, if you are connected to Oracle, then the native dialect would be SQL supported by Oracle.

To disable Federation Server SQL Authorization Enforcement using administration DDL, include FEDSQL=NO in the CREATE DSN statement to default to native dialect.