FedSQL Driver Reference

Overview

The FedSQL language driver supports the FedSQL dialect, as documented in the SAS FedSQL Language Reference Guide. 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 main emphasis 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 will request the data from the data sources and then perform 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.

The FedSQL driver is also required for SAS Federation Server SQL Authorization Enforcement. If the DSN is configured to enable Federation Server SQL Authorization Enforcement, then the FedSQL driver is automatically loaded and used. The FedSQL dialect can also be requested when creating a DSN by choosing the FedSQL dialect for the DSN.

The FedSQL driver is used on top of a native data source driver and supports various connection options. To specify these options, use the DEFAULT_ATTR option in the CREATE DSN statement. For example,

CREATE DSN MYDSN UNDER "Oracle Service" CONNECT
'DEFAULT_ATTR=(SQL_MAX_COL_SIZE=500);DRIVER=ORACLE'

Connection Options

DEFAULT_ATTR=(attr=value;...)

Used to specify connection handle or statement handle attributes supported for initial connect-time configuration. Where attr=value corresponds to any of the following options:

SQL_CURSORS=n

DEFAULT_ATTR=(SQL_CURSORS=2)

FedSQL connection handle option. This option controls the driver’s use of client side result set cursors. The possible values are 0, 1 or 2.

0	A value of `0` causes the driver to use client side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.

1	A value of `1` causes the driver to always use client side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor will never be used.

2	A value of `2` (default) causes the driver to never use client side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor will be used if available, otherwise the cursor will be forward only.

SQL_AC_BEHAVIOR=n

DEFAULT_ATTR=(SQL_AC_BEHAVIOR=0)

FedSQL connection handle option. Specifies whether FedSQL should use transactions when processing complex operations. For example,

“CREATE
                                             TABLE xxx AS SELECT yyy FROM zzz”

or a multi-row delete statement that requires multiple operations to delete the underlying rows. Possible values are 0 (default), 1 and 2.

0	A value of `0` (default) means that no transactions are attempted under-the-covers and operations such as emulated UDPATE, DELETE or INSERT.

1	A value of `1` means that FedSQL tries to use transaction to better support the correct behavior when AUTOCOMMIT is ON (where individual operations like UPDATE, DELETE and INSERT should be atomic).

2	A value of `2` means that transactions are required. This option will fail if the underlying drivers do not support transactions.

SQL_MAX_COL_SIZE=n

DEFAULT_ATTR=(SQL_MAX_COL_SIZE=1048576)

FedSQL statement handle option. Allows a user to specify the size of the varchar or varbinary that is used for the potentially truncated long data when direct bind is not possible. The default value is 32767. The limit for this size is 1 MG. If the value exceeds 1 MG, FedSQL resets the value and returns an Option value changed warning

SQL_STMT_MEM_LIMIT=n

DEFAULT_ATTR=(SQL_STMT_MEM_LIMIT=209715200)

FedSQL statement handle option. Used to control the amount of memory available to FedSQL to answer SQL requests. (n)umber is treated as an integer and is specified in bytes.

SQL_TXN_EXCEPTIONS=n

DEFAULT_ATTR=(SQL_TXN_EXCEPTIONS=2)

FedSQL connection handle option. Supports dynamic connections regardless of the specified transaction isolation. Possible values are 0 or 2 (default).

0	Specify a value of `0` to disable support for dynamic connections.

2	Specify a value of `2` to enable support for dynamic connections.

SQL_USE_EVP=n

DEFAULT_ATTR=(SQL_USE_EVP=0)

FedSQL statement handle option. This option optimizes the driver for large result sets. The possible values are 0 or 1. 1 is the default.

0	Specify `0` to turn optimization OFF.

1	Specify `1` to enable optimization (ON).

SQL_VDC_DISABLE=n

DEFAULT_ATTR=(SQL_VDC_DISABLE=1)

FedSQL statement handle option. This option is used to allow or disallow use of cached data for a statement. The possible values are 0 or 1. 0 is the default.

0	Specify a value of `0` to enable cached data.

1	Specify a value of `1` to disable cached data.

SQL_XCODE_WARN=n

DEFAULT_ATTR=(SQL_XCODE_WARN=1)

FedSQL statement handle option. Used to warn if there is an error while transcoding data during row input or output operations. Possible values are 0, 1 or 2. The default is 0.

0	Specify `0` to return an error if data cannot be transcoded.

1	Specify `1` to return a warning if data cannot be transcoded.

2	Specify `2` to ignore transcoding errors.

DEFAULT_CATALOG=catalog-name

Specifies the name of the catalog that is set as the current catalog when connecting to the data source. This option is useful for SQL Server connections and federated connections.

Last updated: March 6, 2018