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

About the SAS Federation Server Driver for SAP HANA

The SAS Federation Server Driver for SAP HANA (Driver for SAP HANA) enables Read and Write access to SAP HANA data sources. The driver supports both native SQL and FedSQL dialects.

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 SAP HANA

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.

When configuring a data service, you must include one of the following configurations to establish connection to an SAP HANA system:

SERVER and INSTANCE
SAPHANA_DSN or a DSN in CONOPTS
SERVER and PORT
SERVER with a full server name and port

The following table describes the data service connection options for SAP HANA.

Option	Description
CATALOG	`CATALOG=mysaphanacatalog` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. CATALOG is a required option. Note: SAS Federation Server automatically quotes SQL identifiers that do not meet the regular naming convention as defined in the SAS FedSQL Reference Guide.
DRIVER	`DRIVER=SAPHANA` Identifies the type of data service to which you want to connect. The data service SAPHANA represents the SAP HANA database.
SAPHANA_DSN, DB, DATABASE	Specifies the configured SAP HANA ODBC datasource to which you want to connect. Use this option if you have existing SAP HANA ODBC datasources that are configured on your client. This method requires additional setup, either through the ODBC Administrator control panel on Windows platforms or through the odbc.ini file on UNIX platforms. Here is an example of an odbc.ini entry in UNIX: [SAPHANADSN] SERVERNODE=107.20.242.225:30015 Connection options specified in CONOPTS= are appended to the connection string. Use CONOPTS or SAPHANA_DSN to specify the DSN. Do not use both of these options to specify the DSN.
SERVER	Specifies the server name or IP address of the SAP HANA server. The port can be included in the specified value. The port number is 3[instance-number]15 (for example, 30015 for instance number 00). You can specify a list of hostnames separated by a semicolon to support failover. If a host is not available, the next host from the list is used. alias: SERVERNODE, SERVER, HOST Here are some examples using the SERVER= option: SERVER=<’>server-name<’> SERVER=<’>server-name:port<’> SERVER=‘server-name:port;failover-server-name1:port;failover-server-name2:port’
PORT	`PORT=30015` Specifies the port number that is used to connect to the specified SAP HANA server. If you do not specify the port, the instance number, or include the port number in the server specification, the default 30015 is used. Note: 3[instance]15 is the port for the standard SQL communication for client access. This is the only port required for client access.
INSTANCE	Specifies the instance number of the SAP HANA database engine. The port number is 3[instance-number]15. For example, 30015 is the port number for INSTANCE number 00. If the port number is explicitly specified in either the PORT= or the SERVER= option, the INSTANCE= option is ignored, and a warning is written to the server log.
CONOPTS	`CONOPTS=(ODBC-compliant 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.
UID	`UID=‘user-ID’` Specifies the SAP HANA user name, or user ID that you use to connect to a database. If the user name or ID contains spaces or nonalphanumeric characters, enclose it in quotation marks.
PWD	`PWD=’user password’` Specifies the password that is associated with your SAP HANA user name. If the password contains spaces or non-alphanumeric characters, you must enclose it in quotation marks. You can also specify PASSWORD= with the PWD=, PASS=, and PW= aliases.

Secure Sockets Layer (SSL) Connection Options

The Driver for SAP HANA supports SSL. Here are the connection options for SSL.

Option	Description
ENCRYPT	`ENCRYPT=0\|1` Used to enable or disable SSL encryption. The default is 0 (NO).
SSLCRYPTOPROVIDER	`SSLCRYPTOPROVIDER=SAPCRYPTO \| OPENSSL \| MSCRYPTO` Specifies the cryptographic library provider for SSL connectivity. Alias: SSLPROVIDER
SSLKEYSTORE	`SSLKEYSTORE=’file_path’` Specifies the path to the keystore file that contains the server’s private key. If a value is not specified, the ODBC driver uses the default `$HOME/.ssl/key.pem`.
SSLTRUSTSTORE	`SSLTRUSTSTORE=’file_path’` Specifies the path to the truststore file that contains the server’s certificate. If a value is not specified, the ODBC driver uses the default `$HOME/.ssl/trust.pem`. Note: Leave this option empty if you are using the mscrypto cryptographic library.
SSLVALIDATECERTIFICATE	`SSLVALIDATECERTIFICATE=NO\|YES\|0\|1` Set this option to validate the server’s certificate. Setting to YES or 1 activates validation. The default is NO or 0. If this option is not specified, the ODBC driver uses the default and does not validate certificates.
SSLHOSTNAMEINCERTIFICATE	`SSLHOSTNAMEINCERTIFICATE=’string’` Specifies the host name to use for validation. Use this host name when validating a server’s certificate using SSLVALIDATECERTIFICATE. Alias: SSLHOSTNAMEINCERT
SSLCREATESELFSIGNEDCERIFICATE	`SSLCREATESELFSIGNEDCERTIFICATE=NO\|YES\|0\|1` Specifies if a self-signed certificate is created if the keystore cannot be found. If set to YES, a self-signed certificate is created in the event that the keystore is not found. If this option is not specified, the driver uses the default, which is NO. Alias: SSLCREATECERT

Advanced Connection String Options

The Driver for SAP HANA supports the following advanced connection options.

Option

Description

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.

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 as follows:

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. Therefore, it 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 override this value, but if the application does not explicitly set a cursor type, the value specified in DEFAULT_CURSOR_TYPE is in effect.

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.

TABLE TYPE

TABLE_TYPE=ROW|COLUMN|LOCAL|LOCAL
                                    TEMPORARY|GLOBAL|GLOBAL TEMPORARY

Specifies the default table type when creating tables using FedSQL (CREATE TABLE). This option can be overridden by the TABLE_TYPE table option. If the table store type is not specified in connection options nor in the table options, then the default SAP HANA store type is used.

ROW

Creates a table using ROW-based storage in SAP HANA.

COLUMN

Creates a table using COLUMN-based storage in SAP HANA.

LOCAL | LOCAL TEMPORARY

Creates a local temporary table in SAP HANA. The table definition and data are visible only in the current session.

GLOBAL | GLOBAL TEMPORARY

Creates a global temporary table in SAP HANA. The global temporary tables are globally available, and the data is visible only in the current session.