SAS Federation Server Driver for Netezza

About the SAS Federation Server Driver for Netezza
Prerequisites
Data Service Connection Options for Netezza
Overview
Connection Options
Advanced Connection Options

About the SAS Federation Server Driver for Netezza

The SAS Federation Server Driver for Netezza (Driver for Netezza) enables SAS Federation Server to read and update legacy Netezza tables. In addition, the driver creates Netezza tables that can be accessed by both SAS Federation Server and Netezza. Multiple schemas are supported for Netezza 7.0.3 and later.
The Driver for Netezza supports most of the FedSQL functionality.
The Driver for Netezza is a remote driver, which means that it connects to a server process in order to access data. The process might run on the same machine as SAS Federation Server, or it might run 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 Netezza

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 Netezza 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.
Note: SAS Federation Server automatically quotes SQL identifiers that do not meet the regular naming convention as defined in the SAS FedSQL Reference Guide.
DATABASE
DATABASE=database—name;
Identifies the database to which you want to connect, which resides on the server previously specified through the SERVER option.
DRIVER
DRIVER=NETEZZA;
Specifies the data service for the Netezza database to which you want to connect.
Note: You must specify the driver.
CONNECTION OPTIONS
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.
DSN
DSN=data_source_identifer;
Identifies the data source name to which you want to connect.
SERVER
SERVER=server_name;
Identifies the name of the server where the Netezza database resides.
PORT
PORT=port_number
Identifies the listen port of the server where the Netezza database resides.

Advanced Connection Options

The Driver for Netezza supports the following advanced connection options.
Option
Description
CLIENT ENCODING
CLIENT_ENCODING=cei
Used to specify encoding for the client.
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 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 ID
USER=“user-id”;
Specifies a Netezza user ID. If the ID contains blanks or national characters, enclose it in quotation marks. Alias: UID.
Note: You must specify the USER option.
PASSWORD
PASSWORD=password;
Specifies a password for the ID passed through the USER= option. Alias: PWD.
Note: You must specify the PASSWORD option with USER.
SCHEMA
SCHEMA=schema-name
Specifies a schema name that overrides the default schema. Multiple schemas are supported with Netezza 7.0.3 or later. Additional schemas can be specified in FedSQL by qualifying the table name. Alias: SCHEMANAME.
STRIP_BLANKS
STRIP_BLANKS=YES|NO;
Specifies whether to strip blanks from character fields.
READ ONLY
READONLY=YES|NO;
Specifies whether to connect to the Netezza database in Read-Only mode. The default is NO. Alias: READ_ONLY
SHOW SYSTEM TABLES
SHOWSYSTEMTABLES=YES|NO;
Specifies whether tables are included in the available table list. If set to YES or TRUE, system tables are included in the available table list. The default setting is NO. Alias: SST
NUMBER BYTES PER CHARACTER
NUMBYTESPERCHAR=value;
Specifies the default number of bytes per character.
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: March 6, 2018