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. For example, 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 supports various connection options, as outlined below. You must use the DEFAULT_ATTR option in the CREATE DSN statement to specify these options. For example,

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

Connection Options

DEFAULT_CATALOG=catalog-name - Used to specify the name of the catalog to set as the current catalog upon connecting. This option is useful for SQL Server connections and federated connections.

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 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. 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. 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. 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. DEFAULT_ATTR=(SQL_CURSORS=2)
	SQL_AC_BEHAVIOR=n 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. A value of `0` (default) means that no transactions are attempted under-the-covers and operations such as emulated UDPATE, DELETE or INSERT. 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). A value of `2` means that transactions are required. This option will fail if the underlying drivers do not support transactions. DEFAULT_ATTR=(SQL_AC_BEHAVIOR=0)
	SQL_MAX_COL_SIZE=n 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. DEFAULT_ATTR=(SQL_MAX_COL_SIZE=1048576)
	SQL_PUSHDOWN=n FedSQL statement handle option. This option tells FedSQL if and when it should try to push down SQL to the underlying driver. The values are 8, 2 or 0 (default). A value of 8: (PLAN_FORCE_PUSHDOWN_SQL) - Complete statement pushdown is required. If that is not possible, then the INSERT, UPDATE, DELETE, or CREATE TABLE AS statement should fail. A value of 2: (PLAN_DISABLE_PUSHDOWN_SQL) - Specifies that the INSERT, UPDATE, DELETE, or CREATE TABLE AS statement not be pushed down to the underlying driver. A value of 0 (default): Specifies that the FedSQL processor decides whether the INSERT, UPDATE, DELETE, or CREATE TABLE AS statement should be pushed down to the underlying driver. DEFAULT_ATTR=(SQL_PUSHDOWN=0)
	SQL_STMT_MEM_LIMIT=n 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. The following example allows 200 MB of memory: DEFAULT_ATTR=(SQL_STMT_MEM_LIMIT=209715200)
	SQL_TXN_EXCEPTIONS=n FedSQL connection handle option. Supports dynamic connections regardless of the specified transaction isolation. Possible values are 0 or 2 (default). Specify a value of 0 to disable support for dynamic connections. Specify a value of 2 to enable support for dynamic connections. DEFAULT_ATTR=(SQL_TXN_EXCEPTIONS=2)
	SQL_USE_EVP=n FedSQL statement handle option. This option optimizes the driver for large result sets. The possible values are 0 or 1 (default) and are used as follows: Specify 0 to turn optimization OFF. Specify 1 to enable optimization (ON). DEFAULT_ATTR=(SQL_USE_EVP=0)
	SQL_VDC_DISABLE=n FedSQL statement handle option. This option is used to allow or disallow use of cached data for a statement. The possible values are 0 (default) or 1 and are used as follows:: Specify a value of 0 to enable cached data. Specify a value of 1 to disable cached data. DEFAULT_ATTR=(SQL_VDC_DISABLE=1)
	SQL_XCODE_WARN=n 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 (default), 1 or 2 and are used as follows:: Specify 0 to return an error if data cannot be transcoded. Specify 1 to return a warning if data cannot be transcoded. Specify 2 to ignore transcoding errors. DEFAULT_ATTR=(SQL_XCODE_WARN=1)