Greenplum Reference

Understanding the Driver for Greenplum

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

The SAS® Federation Server Driver for Greenplum supports most of the FedSQL functionality. The driver also supports the application's ability to submit native Greenplum SQL statements.

The SAS® Federation Server Driver for Greenplum 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.

Connection Options for Greenplum

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 following connection options are supported for the Greenplum database.

Option	Description
CATALOG	`CATALOG=catalog-identifier;` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. Any identifier is valid (for example, `catalog=gps_test`). Note: You must specify a catalog. For the Greenplum database, this is a logical catalog name to use as an SQL catalog identifier.
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=GREENPLUM`; Identifies the data service to which you want to connect, which is a Greenplum database. Note: You must specify the driver.
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 Greenplum database resides.

Advanced Options

The following advanced options are supported for the Greenplum database.

Option

Description

ALLOW UNQUOTED NAMES

ALLOW_UNQUOTED_NAMES=NO
                                    | YES

;

Specifies whether to enclose table and column names with quotation marks. Tables and columns are quoted when this option is set at NO (default). If set to YES, the driver will not automatically add quotation marks to table and column names if they are not specified. This allows Greenplum tables and columns to be created in the default lower case.

CLIENT ENCODING

CLIENT_ENCODING=cei;

Specifies an encoding different from the default to use on the client side.

CT_PRESERVE

CT_PRESERVE = STRICT | SAFE | FORCE | FORCE_COL_SIZE

Allows users to control how data types are mapped. Note that data type mapping will be 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:

STRICT The requested type must exist in the target database. No type promotion will occur. If the type does not exist, an error is returned.
SAFE Target data types will be upscaled only if they will not result in a loss of precision or scale. When character encodings are changed, the new column size will be 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 will be chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size will be 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 will be the same as the original encoding. This can be used to avoid <i>column size creep</i> that has been seen in some cases, but it means that the resulting column might be too large or too small for the target data.

DEFAULT_ATTR

DEFAULT_ATTR=(attr=value;...)

DEFAULT_ATTR is used to specify connection handle or statement handle attributes supported for initial connect-time configuration. Where attr=value corresponds to any of the options below:

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 will never be 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 will be 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). 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 will be 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.

MAX_BINARY_LEN

MAX_BINARY_LEN=value;

Specifies a value to limit the length of long binary fields (LONG VARBINARY). As opposed to other databases, Greenplum does not have a size limit for long binary fields.

MAX_CHAR_LEN

MAX_CHAR_LEN=value;

Specifies a value to limit the length of character fields (CHAR and VARCHAR). As opposed to other databases, Greenplum does not have a size limit for character fields.

MAX_TEXT_LEN

MAX_TEXT_LEN=value;

Specifies a value to limit the length of long character fields (LONG VARCHAR). As opposed to other databases, Greenplum does not have a size limit for long character fields.

NUM BYTES PER CHAR

NUMBYTESPERCHAR=value;

Specifies the default number of bytes per character.

PASSWORD

PASSWORD=password;

Specifies a password for the ID passed through the USER= option. The alias is PWD=.

Note: You must specify the PASSWORD= option.

SCHEMA

SCHEMA=value;

Specifies the default schema for the connection. If not specified, the schema (or list of schemas) will be determined based on the value of the schema search path defined on the database server.

STRIP BLANKS

STRIP_BLANKS=value;

Specifies whether to strip blanks from character fields.

USER

USER=user-id;

Specifies a Greenplum user ID. If the ID contains blanks or national characters, enclose it in quotation marks. The alias is UID=.

Note: You must specify the USER= option.

Greenplum 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: For DSNs where a wire protocol driver is specified and the catalog option is selected, only the schemas that have tables or views will be listed (not all schemas that exist). If you require different behavior, create a DSN without the catalog option.

When configuring an ODBC DSN using the Greenplum 64-bit Wire Protocol driver, select the following options on the Advanced tab:

Enable SQLDescribeParam
Fetch Ref Cursor
Application Using Threads