This section describes the LIBNAME statement that SAS/ACCESS Interface to DB2 under UNIX and PC Hosts supports.For general information about this feature, see LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing DB2 under UNIX and PC Hosts.
For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
db2specifies the SAS/ACCESS engine name for the DB2 under UNIX and PC Hosts interface.
provides 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 DB2 several ways. Specify only one of these methods for each connection because they are mutually exclusive.
Here is how these options are defined.
lets you connect to a DB2 database with a user ID that is different from the default ID. USER= is optional. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID for your operating environment is used.
Alias: UID=
specifies the DB2 password that is associated with your DB2 user ID. PASSWORD= is optional. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you specify USER=, you must specify PASSWORD=.
Alias: PWD=
specifies the DB2 database to which you want to connect. DATASRC= is optional. If you omit it, you connect by using a default environment variable.
Alias: DATABASE=, DB=, DSN=
specifies connection information for your database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the database. See your DB2 documentation for more details.
This option is not available on UNIX platforms.
specifies connection information for your database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned (no dialog box is displayed in SAS windowing environment).
specifies connection information for your database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately attempt to connect to the DBMS. Instead, it displays a dialog box in SAS windowing environment that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the database.
Restriction: This option is not available on UNIX platforms.
specifies connection information for your database for PCs only. Separate the multiple options with semicolons. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, a dialog box prompts you for the connection options. REQUIRED= lets you modify only required fields in the dialog box.
This option is not available on UNIX platforms.
specifies 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 DB2 under UNIX and PC Hosts, 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.
|
Option |
Default Value |
Valid in CONNECT |
|---|---|---|
|
none |
|
|
|
none |
|
|
|
NO |
● |
|
|
NO |
||
|
SHAREDREAD |
● |
|
|
none |
● |
|
|
none |
● |
|
|
1 for character data types 3 for graphic data types with UTF8 encoding (1 for graphic data types with other encodings) |
● |
|
|
1000 (when inserting rows), 0 (when updating rows), 10,000 (when bulk loading rows) |
|
|
|
none |
● |
|
|
none |
● |
|
|
none |
|
|
|
DBMS |
● |
|
|
NO |
|
|
|
none |
|
|
|
none |
|
|
|
1024 |
● |
|
|
NO |
|
|
|
YES |
|
|
|
YES |
|
|
|
NO |
● |
|
|
none |
● |
|
|
NONE |
|
|
|
NO |
● |
|
|
none |
|
|
|
YES |
|
|
|
NO |
|
|
|
NO |
|
|
|
none |
|
|
|
automatically calculated based on row length |
|
|
|
none |
|
|
|
NONE |
|
|
|
YES (see Naming Conventions for DB2 under UNIX and PC Hosts) |
|
|
|
NO |
● |
|
|
YES (see Naming Conventions for DB2 under UNIX and PC Hosts) |
|
|
|
NO |
● |
|
|
none |
● |
|
|
0 |
● |
|
|
automatically calculated based on row length |
● |
|
|
CS |
|
|
|
ROW |
● |
|
|
NO |
● |
|
|
your user ID |
|
|
|
YES |
|
|
|
none |
|
|
|
none |
|
|
|
none |
|
|
|
NO |
● |
|
|
CS (see Locking in the DB2 under UNIX and PC Hosts Interface) |
|
|
|
ROW |
● |
|
|
YES |
|
|
|
YES |
|
|
|
NO |
● |
In this example, the libref MyDBLib uses the DB2 engine and the NOPROMPT= option to connect to a DB2 database. PROC PRINT is used to display the contents of the DB2 table Customers.
libname mydblib db2
noprompt="dsn=userdsn;uid=myusr1;pwd=mypwd1;";
proc print data=mydblib.customers;
where state='CA';
run;