SAS Federation Server Driver for ODBC :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

Overview

This section provides functionality details and guidelines for the open database connectivity (ODBC) databases that are supported by the SAS Federation Server Driver for ODBC (Driver for ODBC).

ODBC standards provide a common interface to a variety of databases, including dBASE, Microsoft Access, Oracle, Paradox, and Microsoft SQL Server databases. Specifically, ODBC standards define APIs that enable an application to access a database if both the application and the database conform to the specification. ODBC also provides a mechanism to enable dynamic selection of a database that an application is accessing, so that users have the flexibility of selecting databases other than those that are specified by the application developer.

About the SAS Federation Server Driver for ODBC

The SAS Federation Server Driver for ODBC (Driver for ODBC) enables SAS Federation Server to read and update legacy ODBC database tables. In addition, the driver creates tables that can be accessed by both SAS Federation Server and an ODBC database.

The Driver for ODBC supports most of the FedSQL functionality. The driver also supports an application's ability to submit native database-specific SQL statements.

The Driver for ODBC is a remote driver, which means that it connects to a server process in order to access data. The process might be running on the same machine as SAS Federation Server, or it might be running on another machine in the network.

Prerequisites

Before configuring SAS Federation Server drivers, you must set environment variables that point to the client libraries required for your data source. See Setting Environment Variables for additional information.

Data Service Connection Options for ODBC

Overview

To access data that is hosted on SAS Federation Server, a client must submit a DSN that defines how to connect to the data. DSNs are associated with a data service that provides the foundation for the connection, such as user access control. See Working with Data Services for additional information.

Connection Options

The Driver for ODBC supports the following connection options.

Option	Description
CATALOG	`CATALOG=catalog-identifier;` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. For databases that do not support native catalogs, any identifier is valid (for example, `catalog=myodbc`). For databases like SQL Server that do support native catalogs, CATALOG= is not required. The connection defaults to CATALOG=* unless you specify a logical name for the catalog and map it to the native catalog name in the database. For example, to map the logical catalog `mycat` to the native catalog named `newusers`, use the following command: `catalog=(mycat=newusers);`. Catalog name maps can be used only with FedSQL. They are not valid with native SQL. Note: SAS Federation Server automatically quotes SQL identifiers that do not meet the regular naming convention as defined in the SAS FedSQL Reference Guide.
CONOPTS	`CONOPTS=(ODBC—compliant database connection string);` Specifies an ODBC-compliant database connection string using ODBC-style syntax. These options, combined with the ODBC_DSN option, must specify a complete connection string to the data source. If you include a DSN= or FILEDSN= specification within the CONOPTS= option, do not use the ODBC_DSN= connection option. However, you can specify the ODBC database-specific connection options by using CONOPTS=. Then you can specify an ODBC DSN that contains other connection information by using the ODBC_DSN= connection option. Here is an example string using the CONOPTS option: `DRIVER=ODBC;CONOPTS=(DRIVER={DataFlux 32-bit SQL Server Wire Protocol};SERVER=myserver;APP=Microsoft ODBC SDK;DATABASE=mydb)` This example uses the ODBC_DSN option with the CONOPTS option: `DRIVER=ODBC;ODBC_DSN=mydsn;CONOPTS=(DATABASE=mydb)`
DRIVER	`DRIVER=ODBC`; Calls the SAS Federation Server Driver for ODBC. This specifies that the data service to which you want to connect must be an ODBC-compliant database. Note: DRIVER is a required option. You must specify the driver.
ODBC_DSN	`ODBC_DSN=odbc dsn name` Specifies a valid ODBC-compliant database DSN that contains connection information for connecting to the ODBC-compliant database. You can use the CONOPTS= option in addition to ODBC_DSN= option to specify database-specific connection options not provided by SAS Federation Server. Do not specify the ODBC DSN in both CONOPTS= and ODBC_DSN=.

Advanced Connection Options

The Driver for ODBC supports the following advanced connection options for an ODBC-compliant database.

Option

Description

CLIENT_ENCODING

CLIENT_ENCODING=encoding-value

Specifies a client encoding value that overrides the default. The default is the encoding that is set on the machine on which SAS Federation Server is running.

CT_PRESERVE

CT_PRESERVE = STRICT | SAFE | FORCE | FORCE_COL_SIZE

Allows users to control how data types are mapped. Note that data type mapping is disabled when CT_PRESERVE is set to STRICT. If the requested type does not exist on the target database, an error is returned. The options are as follows:

STRICT The requested type must exist in the target database. No type promotion occurs. If the type does not exist, an error is returned.
SAFE Target data types are upscaled only if they do not result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
FORCE This is the default for all drivers. The best corresponding target data type is chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
FORCE_COL_SIZE This option is the same as FORCE, except that the column size for the new encoding is the same as the original encoding. This option can be used to avoid column size creep. However, the resulting column might be too large or too small for the target data.

ENABLE MULTIPLE ACTIVE RESULT SETS (MARS)

ENABLE_MARS=
                                       NO|YES

Enables or disables the use of multiple active result sets (MARS) on SQL Server. FedSQL cannot permit transactions on top of SQL Server because SQL Server only allows one cursor per transaction. Set this option to YES which gives FedSQL the ability to allow transactions under a given SQL Server connection.

DEFAULT_ATTR

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:

CURSORS=n- 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	Causes the driver to use client-side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.
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 not used.
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 will be forward-only.

Example: DEFAULT_ATTR=(CURSORS=2)

USE_EVP=n - Statement handle option. This option optimizes the driver for large result sets. The possible values are 0 (OFF) or 1 (ON), which is the default. Example: DEFAULT_ATTR=(USE_EVP=0)
XCODE_WARN=n - Statement handle option. Used to warn on character transcoding errors that occur during row input or output operations. Possible values are 0 (returns an error), 1 (returns a warning), or 2 (ignore transaction errors). 0 is the default. Example: DEFAULT_ATTR=(XCODE_WARN=1)

DEFAULT CURSOR TYPE

DEFAULT_CURSOR_TYPE=FORWARD_ONLY
                                       | KEYSET_DRIVEN | DYNAMIC | STATIC;

Specifies a valid default cursor type for new statements. The valid options are:

FORWARD_ONLY

Specifies a non-scrollable cursor that moves only forward through the result set. Forward-only cursors are dynamic in that all changes are detected as the current row is processed. If an application does not require scrolling, the forward-only cursor retrieves data quickly, with the least amount of overhead processing.

KEYSET_DRIVEN

Specifies a scrollable cursor that detects changes that are made to the values of rows in the result set but that does not always detect changes to deletion of rows and changes to the order of rows in the result set. A keyset-driven cursor is based on row keys, which are used to determine the order and set of rows that are included in the result set. As the cursor scrolls the result set, it uses the keys to retrieve the most recent values in the table.

It is sometimes helpful to have a cursor that can detect changes in the rows of a result set. A keyset-driven cursor uses a row identifier rather than caching the entire row into memory. It therefore uses much less disk space than other row caching mechanisms. Deleted rows can be detected when a SELECT statement that references the bookmark, row ID, or key column values fails to return a row.

DYNAMIC

Specifies a scrollable cursor that detects changes that are made to the rows in the result set. All INSERT, UPDATE, and DELETE statements that are made by all users are visible through the cursor. The dynamic cursor is good for an application that must detect all concurrent updates that are made by other users.

STATIC

Specifies a scrollable cursor that displays the result set as it existed when the cursor was first opened. The static cursor provides forward and backward scrolling. If the application does not need to detect changes but requires scrolling, the static cursor is a good choice.

Note: The application can still override this value, but if the application does not explicitly set a cursor type, this value will be in effect

DM_UNICODE

DM_UNICODE=unicode-setting

Specifies the Unicode setting for the Driver Manager. The default is UTF-8 which is the requirement for the SAS and DataFlux branded drivers. Use DM_UNICODE=UCS2 to connect to unixODBC based drivers so that the correct Unicode setting is realized. See Configuring ODBC Connections Using Third Party ODBC Drivers for additional information.

DRIVER TRACE

DRIVER_TRACE='API | SQL | ALL';

Requests tracing information, which logs transaction records to an external file that can be used for debugging purposes. The SAS Federation Server driver writes a record of each command that is sent to the database to the trace log based on the specified tracing level, which determines the type of tracing information. The tracing levels are as follows:

ALL Activates all trace levels.
API Specifies that API method calls be sent to the trace log. This option is most useful if you are having a problem and need to send a trace log to Technical Support for troubleshooting.
DRIVER Specifies that driver-specific information be sent to the trace log.
SQL Specifies that SQL statements that are sent to the database management system (DBMS) be sent to the trace log. Tracing information is DBMS specific, but most SAS Federation Server drivers log SQL statements such as SELECT and COMMIT.

Default: Tracing is not activated.

Note: If you activate tracing, you must also specify the location of the trace log with DRIVER_TRACEFILE=. Note that DRIVER_TRACEFILE= is resolved against the TRACEFILEPATH set in ALTER SERVER. TRACEFILEPATH is relative to the server's content root location.

(Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

Interaction: You can specify one trace level, or you can concatenate more than one by including the | (OR) symbol. For example, driver_trace='api|sql' generates tracing information for API calls and SQL statements.

DRIVER TRACE FILE

DRIVER_TRACEFILE='filename';

Used to specify the name of the text file for the trace log. Include the filename and extension in single or double quotation marks. For example: driver_tracefile='\mytrace.log'

Default: The default TRACEFILE location applies to a relative filename, and it is placed relative to TRACEFILEPATH.

Requirement: DRIVER_TRACEFILE is required when activating tracing using DRIVER_TRACE.

Interaction: (Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

DRIVER TRACE OPTIONS

DRIVER_TRACEOPTIONS=APPEND | THREADSTAMP | TIMESTAMP;

Specifies options in order to control formatting and other properties for the trace log:

APPEND Adds trace information to the end of an existing trace log. The contents of the file are not overwritten.
THREADSTAMP Prepends each line of the trace log with a thread identification.
TIMESTAMP Prepends each line of the trace log with a time stamp.

Default: The trace log is overwritten with no thread identification or time stamp.

USER

USER=user-ID;

Specifies the user ID for logging on to the ODBC-compliant database, such as Microsoft SQL Server, with a user ID that differs from the default ID.

Note: The alias is UID=.

PASSWORD

PASSWORD=password;

Specifies the password that corresponds to the user ID in the database.

Note: The alias is PWD=.

Here are example connection strings using the SAS Federation Server Driver for ODBC:

This connection string specifies an ODBC DSN:

driver=odbc; uid=scott; pw=mypw; odbc_dsn=myOracleDSN;
    catalog=odbc_oracle;

This connection string specifies catalog name maps to access multiple catalogs on Microsoft SQL Server:

driver=odbc; uid=jfox; pw=mypw; odbc_dsn=mySQLdsn;
    catalog=(cat1=mycat; cat2=testcat; cat3=users;

Configuring ODBC for Hadoop

Connection Options

In addition to the ODBC data service connection options above, the following option is available for Hive and Impala using ODBC:

Option	Description
SCHEMA	`SCHEMA=schema-name;` This option is for use with Apache Hive or Cloudera Impala only. Specifies the name of the schema that is passed to FedSQL. This example connection string specifies a schema name that is passed to FedSQL for Hive or Impala connections: DRIVER=ODBC;UID=hadoop;PWD=mypw;CONOPTS= (DSN=tktshive);SCHEMA=schema1;CATALOG=CATALOG_HIVE;

See the SAS Federation Server Driver for Apache Hive for additional information about configuring ODBC options for Hive.

Wire Protocol Driver Usage Notes

Overview

SAS Federation Server provides a number of wire protocol ODBC drivers that communicate directly with a database server, without having to communicate through a client library. When you configure the ODBC drivers on Windows or UNIX, you have the opportunity to set certain options. SAS products run best when these options are selected. Some, but not all, are selected by default.

Windows	The options are located on the Advanced or Performance tabs in the ODBC Administrator.
UNIX	The options are available when configuring data sources using the `dfdbconf` tool. Values can also be set by editing the `odbc.ini` file in which their data sources are defined.

Note: The behavior of a DSN using a wire protocol driver with the catalog option selected, returns only the schemas that have associated tables or views. To list all existing schemas, create a DSN without the catalog option selected.

MySQL

When configuring an ODBC DSN using the MySQL Wire Protocol driver, select the following advanced options:

Application Using Threads
Enable SQLDescribeParam

SQL Server and SQL Server Legacy

Configure the following Advanced options for the SQL Server Wire Protocol driver and the SQL Server Legacy Wire Protocol driver:

Application Using Threads
Enable Quoted Identifiers
Fetch TWFS as Time
Fetch TSWTZ as Timestamp

Note:

Significant performance improvements have been realized when using the SQL Server Legacy Wire Protocol Driver, as compared to the SQL Server Wire Protocol Driver.
The SQL Server Legacy Wire Protocol Driver does not support transactions when used with FedSQL enabled because the driver only allows a single statement per connection while FedSQL requires multiple statements per connection when using transactions.