SAS/ACCESS Interface to Greenplum |
Overview |
This section describes the LIBNAME statement that SAS/ACCESS Interface to Greenplum supports and includes examples. For details about this feature, see Overview of the LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing Greenplum.
LIBNAME libref greenplm <connection-options> <LIBNAME-options>; |
Arguments |
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
specifies the SAS/ACCESS engine name for the Greenplum interface.
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 Greenplum 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.
specifies the Greenplum server name or the IP address of the server host. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
specifies the Greenplum 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. You can specify DATABASE= with the DB= alias.
specifies the port number that is used to connect to the specified Greenplum database. If you do not specify a port, the default is 5432.
specifies the Greenplum 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.
specifies the password that is associated with your Greenplum 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.
specifies the configured Greenplum ODBC data source to which you want to connect. It is recommended that you use this option only if you have existing Greenplum ODBC data sources configured 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 defined.
define how SAS processes DBMS objects. Some LIBNAME options can enhance performance, while others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Greenplum with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.
Option | Default Value |
---|---|
ACCESS= |
none |
AUTHDOMAIN= |
none |
AUTOCOMMIT= |
operation-specific |
CONNECTION= |
SHAREDREAD |
CONNECTION_GROUP= |
none |
DBCOMMIT= |
1000 (inserting) or 0 (updating) |
DBCONINIT= |
none |
DBCONTERM= |
none |
DBCREATE_TABLE_OPTS= |
none |
DBGEN_NAME= |
DBMS |
DBINDEX= |
YES |
DBLIBINIT= |
none |
DBLIBTERM= |
none |
DBMAX_TEXT= |
1024 |
DBMSTEMP= |
none |
DBNULLKEYS= |
none |
DBPROMPT= |
none |
DBSASLABEL= |
COMPAT |
DEFER= |
none |
DELETE_MULT_ROWS= |
NO |
DIRECT_EXE= |
none |
IGNORE_ READ_ONLY_COLUMNS= |
none |
INSERTBUFF= |
automatically calculated based on row length |
MULTI_DATASRC_OPT= |
none |
PRESERVE_COL_NAMES= |
see Naming Conventions for Greenplum |
PRESERVE_TAB_NAMES= |
see Naming Conventions for Greenplum |
QUERY_TIMEOUT= |
0 |
QUOTE_CHAR= |
none |
READBUFF= |
automatically calculated based on row length |
REREAD_EXPOSURE= |
none |
SCHEMA= |
none |
SPOOL= |
none |
SQL_FUNCTIONS= |
none |
SQL_FUNCTIONS_COPY= |
none |
STRINGDATES= |
none |
TRACE= |
none |
TRACEFILE= |
none |
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 greenplm server=gplum04 db=customers port=5432 user=gpusr1 password=gppwd1; proc print data=mydblib.customers; where state='CA'; run;
In the next example, DSN=, USER=, and PASSWORD= are the connection options. The Greenplum 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 greenplm DSN=gplumSalesDiv user=gpusr1 password=gppwd1; proc print data=mydblib.customers; where state='CA';
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.