LIBNAME Statement for the HAWQ Engine

Overview

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

Here is the LIBNAME statement syntax for accessing HAWQ.

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.

hawq

specifies the SAS/ACCESS engine name for the HAWQ 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 HAWQ database in 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=<'>server-name<'>

specifies the HAWQ server name or the IP address of the server host. If the server name contains spaces or nonalphanumeric characters or if it is an IP address, you must enclose it in quotation marks.

Alias: HOST=

DATABASE=<'>database-name<'>

specifies the HAWQ database 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.

Alias: DB=

PORT=port

specifies the port number that is used to connect to the specified HAWQ database. If you do not specify a port, the default is 5432.

Aliases: SERVICE=, SERVICE_NAME=

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

specifies the HAWQ user name (also called the user ID) that is used to connect to the database. If the user name contains spaces or nonalphanumeric characters, use quotation marks.

Alias: UID=

PASSWORD=<'>HAWQ-password<'>

specifies the password that is associated with your HAWQ user ID. If the password contains spaces or nonalphabetic characters, you must enclose it in quotation marks. You can also specify PASSWORD= with the PWD=, PASS=, and PW= aliases.

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

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

Aliases: DS=, DATASRC=

LIBNAME -options

specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to HAWQ 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 HAWQ

Option	Default Value	Valid in CONNECT
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	operation-specific	●
BULKLOAD=	NO	●
CONNECTION=	SHAREDREAD	●
CONNECTION_GROUP=	none	●
CURSOR_TYPE=	FORWARD_ONLY	●
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=	none
DBNULLKEYS=	YES
DBPROMPT=	none	●
DBSASLABEL=	COMPAT
DBSLICEPARM=	THREADED_APPS
DEFER=	none	●
DELETE_MULT_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_READ_ONLY_COLUMNS=	NO
IN=	none
INSERT_SQL=	YES
INSERTBUFF=	automatically calculated based on row length
KEYSET_SIZE=	0	●
MULTI_DATASRC_OPT=	NONE
POST_STMT_OPTS=	none
PRESERVE_COL_NAMES=	YES See Naming Conventions for HAWQ.
PRESERVE_TAB_NAMES=	see Naming Conventions for HAWQ
QUERY_TIMEOUT=	0	●
QUOTE_CHAR=	none
READ_ISOLATION_LEVEL=	RC	●
READ_LOCK_TYPE=	ROW	●
READBUFF=	automatically calculated based on row length	●
REREAD_EXPOSURE=	none	●
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
UPDATE_LOCK_TYPE=	ROW
UPDATE_SQL=	YES	●
UPDATE_MULT_ROWS=	NO
UTILCONN_TRANSIENT=	none

LIBNAME Statement Examples

In this example, SERVER=, DATABASE=, PORT=, USER=, and PASSWORD= are the connection options.

libname mydblib hawq server=hwq04 db=customers port=5432
    user=hwqusr1 password=hwqpwd1;

proc print data=mydblib.customers;
   where state='CA';
run;

In the next example, DSN=, USER=, and PASSWORD= are the connection options. The HAWQ data source is configured in the ODBC Administrator Control Panel on Windows platforms. It is also configured in the odbc.ini file or a similarly named configuration file on UNIX platforms.

libname mydblib hawq DSN=hwqSalesDiv user=hwqusr1 password=hwqpwd1;

proc print data=mydblib.customers;
   where state='CA';
run;