Oracle Reference

Understanding the SAS Federation Server Driver for Oracle

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

The Driver for Oracle supports most of the FedSQL functionality. The driver also supports the application's ability to submit native Oracle SQL statements.

The Driver for Oracle 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.

Data Service Connection Options for Oracle

Overview

To access data that is hosted on SAS Federation Server, a client must submit a connection string, which defines how to connect to the data. The data service connection arguments for Oracle Server include connection options and advanced options.

Connection Options

Connection options are used to establish a connection to a data source. Specify one or more connection options when defining a data service using the CREATE DATA SERVICE DDL statement.

The Driver for Oracle supports the following connection options.

Option	Description
CATALOG	`CATALOG=catalog—identifier;` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. Any identifier is valid such as `catalog=oracle_test`. You must specify a catalog. For the Oracle database, this is a logical catalog name to use as an SQL catalog identifier. 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=ORACLE`; Identifies the data service to which you want to connect, which is an Oracle database. Note: You must specify the driver.
PATH	`PATH=database-specification;` Specifies the Oracle connect identifier. A connect identifier can be a net service name, a database service name, or a net service alias.
USERID (UID)	`UID=user-id;` Specifies an optional Oracle user ID. If the user ID contains blanks or national characters, enclose it in quotation marks. If you omit an Oracle user ID and password, the default Oracle user ID OPS$sysid is used, if it is enabled.
PASSWORD (PWD)	`PWD=password;` Specifies an optional Oracle database password that is associated with the Oracle user ID. `PWD=` is always used with `UID=` and the associated password is case-sensitive. If you omit `PWD=`, the password for the default Oracle user ID OPS$sysid is used, if it is active.

Advanced Connection Options

The Driver for Oracle 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)

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:

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 TRACEFILE

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.

ORA_ENCODING

ORA_ENCODING=UNICODE;

Specifies that the Oracle data be returned in Unicode to SAS Federation Server. UNICODE is the default setting and is independent of the NLS_LANG environment variable setting.

ORNUMERIC

ORANUMERIC=NO
                                    | YES

Specifies how numbers read from or inserted into the Oracle NUMBER column will be treated. This option defaults to YES so that a NUMBER column with precision or scale is described as TKTS_NUMERIC. This option can be specified as both a connection option and a table option. When specified as both connection and table option, the table option value overrides the connection option.

NO Indicates that the numbers will be treated as TKTS_DOUBLE values. They might not have precision beyond 14 digits.
YES Indicates that non-integer values with explicit precision will be treated as TKTS_NUMERIC values. This is the default setting.

USE CACHED CATALOG

USE_CACHED_CATALOG=YES
                                    | NO;

Specifies whether to use the cached catalog rather than compiling a new catalog on every run. Setting this option to YES can improve the performance of the TKTSForeignKeys API. The default setting is YES.

Note: Before you can use this option, you must complete the following steps:

Create a materialized view. See the example code in Creating a Materialized View (USE_CACHED_CATALOG).
Use the ALTER DSN statement to add the USE_CACHED_CATALOG connection option. For more information about the ALTER DSN statement, see ALTER DSN Statement.

Creating a Materialized View (USE_CACHED_CATALOG)

The following example shows you how to create a materialized view. Use this script if USE_CACHED_CATALOG is set to YES above.

/*-----------------------SAS_CACHED_CATALOG.SQL--------------------------------*/
/* This script is used to create the materialized and the synonym needed to 
   get the ForeignKey metadata.  Work with your DBA to set this up. 
   Materialized views can be complex and so thorough understanding will help us
   use them effectively. Especially deciding how to do the refreshes.
   Here we provide the simplest possible steps to create the required materialized
   view and the command to refresh it manually. The materialized view below can 
   be created in any schema with any name. Feel free to add whatever REFRESH 
   options suits your purpose. Note that you might need additional steps based 
   on the REFRESH option setting. Here we provide the simplest possible way to 
   do this.  The PUBLIC synonym pointing to this Materialized view must be
   named "SAS_CACHED_FK_CATALOG_PSYN". This synonym must be visible to 
   PUBLIC (or the set of users who will be needing Foreignkey metadata) so that
   it is accessible from any   schema.  
*/

Create materialized view SAS_CACHED_FK_CATALOG_MATVIEW REFRESH ON DEMAND as SELECT
PKAC.OWNER as PKTABLE_SCHEM,
PKAC.TABLE_NAME as PKTABLE_NAME,
PKACC.COLUMN_NAME as PKCOLUMN_NAME,
FKAC.OWNER as FKTABLE_SCHEM,
FKAC.TABLE_NAME as FKTABLE_NAME,
FKACC.COLUMN_NAME as FKCOLUMN_NAME,
FKACC.POSITION as KEY_SEQ,
FKAC.CONSTRAINT_NAME as FK_NAME,
PKAC.CONSTRAINT_NAME as PK_NAME 
from 
sys.all_constraints PKAC, sys.all_constraints FKAC,
sys.all_cons_columns PKACC, sys.all_cons_columns FKACC

where  

FKAC.r_constraint_name=PKAC.constraint_name and
FKAC.constraint_name=FKACC.constraint_name and
PKAC.constraint_name=PKACC.constraint_name and PKAC.constraint_type='P' and
FKAC.constraint_type='R' and FKAC.owner=FKACC.owner and PKAC.owner=PKACC.owner
and PKAC.table_name=PKACC.table_name and FKAC.table_name=FKACC.table_name and
FKACC.position = PKACC.position ;

/* The synonym name *must* be SAS_CACHED_FK_CATALOG_PUBLIC_SYNONYM */
create public synonym  SAS_CACHED_FK_CATALOG_PSYN for SAS_CACHED_FK_CATALOG_MATVIEW;
grant all on  SAS_CACHED_FK_CATALOG_PSYN to PUBLIC;

/*---------Manual REFRESH of the Materialized View----------------------------*/
/* Note there are several ways to do this, consult with your DBA.
   Here are a couple of ways:
*/
execute DBMS_MVIEW.REFRESH('SAS_CACHED_FK_CATALOG_MATVIEW');
execute DBMS_SNAPSHOT.REFRESH('SAS_CACHED_FK_CATALOG_MATVIEW', '?');

Oracle Wire Protocol Driver Usage Notes

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: When you use a wire protocol driver to create an ODBC connection, the following special considerations apply:

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.
Verify that the Enable Bulk Load option is turned on in the ODBC DSN for databases that support this option. The Bulk Load option is not enabled by default in the newer wire protocol drivers. As a result, insert performance suffers.

When configuring an ODBC DSN using the Oracle Wire Protocol driver, set the following advanced options:

Application Using Threads
Enable SQLDescribeParam
Describe at Prepare
Enable N-CHAR Support
Enable Scrollable Cursors