LIBNAME Statement for the Vertica Engine

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to Vertica supports. For general information about this feature, see LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing Vertica.

For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

vertica

specifies the SAS/ACCESS engine name for the Vertica interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. When you use the LIBNAME statement, you can connect to the Vertica server in one of two ways. Specify only one of these methods for each connection because they are mutually exclusive.

• SERVER=, DATABASE=, PORT=, USER=, PASSWORD=
• DSN=, USER=, PASSWORD=

Here is how these options are defined.

Note: All of the following connection options are also valid in the CONNECT statement when you use the SQL pass-through facility (SQL procedure) to connect to your DBMS.

SERVER=<'>Vertica-server-name<'>

specifies the server name or IP address of the Vertica server to which you want to connect. This server accesses the database that contains the tables and views that you want to access. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

DATABASE=<'>Vertica-database-name<'>

specifies the name of the database on the Vertica server that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

The database value is used to qualify table names formatted as database.table-name. If you specify the SCHEMA= LIBNAME option also, then a table name is formatted as database.schema.table-name.

Alias: DB=

Requirement: The DATABASE= option is required in the LIBNAME statement unless you specify the DSN= option.

PORT=port

specifies the port number that is used to connect to the specified Vertica server. If you do not specify a port, the default is 5433.

USER=<'>Vertica-user-name<'>

specifies the Vertica user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

Alias: UID

PASSWORD=<'>Vertica-password<'>

specifies the password that is associated with your Vertica user name. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

Alias: PWD=

DSN=<'>Vertica-data-source<'>

specifies the configured Vertica ODBC data source to which you want to connect. Use this option if you have existing Vertica ODBC data sources that are configured on your client. This method requires additional setup. This extra setup is done through the ODBC Administrator control panel on Windows platforms, through the odbc.ini file, or a similarly named configuration file on UNIX platforms. Therefore, it is recommended that you use this connection method only if you have existing, functioning data sources that have been specified.

PRESERVE_COMMENTS

lets you pass additional information (called hints) to Vertica DBMS for processing. These hints might direct the Vertica DBMS query optimizer to choose the best processing method based on your hint.

You specify PRESERVE_COMMENTS as an argument in the CONNECT statement. You then specify the hints in the Vertica DBMS SQL query for the CONNECTION TO component. Hints are entered as comments in the SQL query and are passed to and processed by Vertica DBMS.

LIBNAME-options

specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance; others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Vertica, with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For details, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for Vertica

Option	Default Value	Valid in CONNECT
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	NO	●
CONNECTION=	UNIQUE	●
CONNECTION_GROUP=	none	●
CONOPTS=	none	●
DBCLIENT_MAX_BYTES=	matches the maximum number of bytes per single character of the SAS session encoding	●
DBCOMMIT=	1000 when inserting rows, 0 when updating rows
DBCONINIT=	none	●
DBCONTERM=	none	●
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS	●
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024	●
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO	●
DBSASLABEL=	COMPAT
DBSERVER_MAX_BYTES=	usually 1	●
DBSLICEPARM=	none
DEFER=	NO	●
DELETE_MULT_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_READ_ONLY_COLUMNS=	NO
INSERT_SQL=	YES
INSERTBUFF=	based on row length
KEYSET_SIZE=	0
MULTI_DATASRC_OPT=	NONE
POST_STMT_OPTS=	none
PRESERVE_COL_NAMES=	see Data Types for Vertica
PRESERVE_TAB_NAMES=	NO
QUALIFIER=	none
QUERY_TIMEOUT=	0	●
QUOTE_CHAR=	none
READ_ISOLATION_LEVEL=	RC (see Locking in the Vertica Interface)	●
READ_LOCK_TYPE=	ROW	●
READBUFF=	1	●
REREAD_EXPOSURE=	NO	●
SCHEMA=	none
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	none
STRINGDATES=	NO	●
SUB_CHAR=	none
TRACE=	none	●
TRACEFILE=	none	●
UPDATE_ISOLATION_LEVEL=	RC (see Locking in the Vertica Interface)	●
UPDATE_LOCK_TYPE=	ROW	●
UPDATE_MULT_ROWS=	NO
UPDATE_SQL=	YES
UTILCONN_TRANSIENT=	NO

Vertica LIBNAME Statement Examples

No DSN is specified in this example. This example uses the recommended default values for the connection options to make the connection.

libname mydblib vertica server="mysrv1" port=5433 
   user=myusr1 password=mypwd1 database=mydb1;

A DSN is specified in this next example.

libname mydblib vertica dsn=mydsn1
   user=myusr1 password=mypwd1;