FedSQL Driver Reference

Overview

The FedSQL language driver supports the FedSQL dialect, as documented in SAS FedSQL Language Reference Guide. When loaded, the FedSQL driver parses SQL requests, and then sends the parsed query to the appropriate data source driver to determine whether the functionality can be handled by the data service. The FedSQL driver includes an SQL processor that 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 requests the data from the data sources and then performs the join. The FedSQL driver supports the FedSQL dialect regardless of the data source it comes from. 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.

Connection Options

CONOPTS=((connection string 1);(connection string 2); ... (connection string <n>)) - Specifies one or more data source connection strings. For example, the following illustrates a federated connection string including Oracle, Teradata, Netezza, and Base SAS data sources:
```
driver=sql;conopts=((driver=oracle;catalog=acat;uid=myuid;
pwd=myPass9;path=oraclev11.abc.123.com:1521/ORA11G);
(driver=teradata;catalog=bcat;uid=model;
pwd='{sas002}C5DDFFF91B5D31DFFFCE9FFF';
server=terasoar;database=model);(driver=netezza;uid=myuid;
pwd=myPass2;server=mysrvr;database=testdb;catalog=(ccat={TEST}));
(driver=base;catalog=dcat;schema=(name=dblib;primarypath=/u/mypath/mydir)))
```
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 is never 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 is used if available, otherwise the cursor is 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 UPDATE, DELETE, or INSERT are not guaranteed to be atomic. A value of `1` means that FedSQL tries to use transactions to better support the correct behavior when AUTOCOMMIT is set to ON (where individual operations like UPDATE, DELETE, and INSERT should be atomic ). A value of `2` means that transactions are required. This option fails if the underlying drivers do not support transactions. DEFAULT_ATTR=(SQL_AC_BEHAVIOR=0)
	SQL_MAX_COL_SIZE=n FedSQL statement handle option. Enables a user to specify the size of the `varchar` or `varbinary` that is used for 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, the INSERT, UPDATE, DELETE, or CREATE TABLE AS statement fails. 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 determine 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 that is available to FedSQL to answer SQL requests. (n) 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 when 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)