LIBNAME Statement Specifics for DB2 under z/OS

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to DB2 under z/OS supports and includes an example. For details about this feature, see the LIBNAME statement.

Here is the LIBNAME statement syntax for accessing DB2 under z/OS interface.

LIBNAME libref db2 <connection-options> <LIBNAME-options>;

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.

db2

specifies the SAS/ACCESS engine name for the DB2 under z/OS interface.

connection-options

provides connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. Here is how these options are defined.

USER=<'>user-name<'>

lets you connect to a DB2 database with a user ID that is different from the default ID. The value for this option cannot exceed 8 characters. 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. If you do not specify the SCHEMA= or AUTHID= LIBNAME option, the value of the USER= option (if present) is used as the default schema. Authentication options are not checked when the LIBNAME is issued. They are checked only when a statement involving the libref is run. However, for explicit pass-through, credentials are checked immediately.

PASSWORD=<'>password<'>

specifies the DB2 password that is associated with your DB2 user ID. PASSWORD= is optional. If you specify USER=, you must specify PASSWORD=.

LOCATION=location

maps to the location in the SYSIBM.LOCATIONS catalog in the communication database. In SAS/ACCESS Interface to DB2 under z/OS, the location is converted to the first level of a three-level table name: location.authid.table. DB2 Distributed Data Facility (DDF) Communication Database (CDB) makes the connection implicitly to the remote DB2 subsystem when DB2 receives a three-level name in an SQL statement.

LOCATION= is optional. If you omit it, SAS accesses the data from the local DB2 database unless you have specified a value for the SERVER= option. This option is not validated until you access a DB2 table. If you specify LOCATION=, you must also specify the AUTHID= option.

SSID=DB2-subsystem-id

specifies the DB2 subsystem ID to connect to at connection time. SSID= is optional. If you omit it, SAS connects to the DB2 subsystem that is specified in the DB2SSID= SAS system option. The DB2 subsystem ID is limited to four characters. For more information, see Settings.

SERVER=DRDA-server

specifies the DRDA server to which you want to connect. SERVER= lets you access DRDA resources stored at remote locations. Check with your system administrator for system names. You can connect to only one server per LIBNAME statement.

SERVER= is optional. If you omit it, you access tables from your local DB2 database unless you have specified a value for the LOCATION= LIBNAME option.

Default: none.

For information about	See
accessing a database server on Linux, UNIX, or Windows using a libref	the REMOTE_DBTYPE= LIBNAME option
setting up DB2 z/OS so that SAS can connect to the DRDA server when the SERVER= option is used	installation instructions for this interface
configuring SAS to use the SERVER= option	installation instructions for this interface

LIBNAME-options

defines 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 z/OS, with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options

Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
AUTHID=	your user ID
CONNECTION=	SHAREDREAD
CONNECTION_GROUP=	none
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBLIBINIT=	none
DBLIBTERM=	none
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBSASLABEL=	COMPAT
DBSLICEPARM=	THREADED_APPS,2
DEFER=	NO
DEGREE=	ANY
DIRECT_EXE=	none
DIRECT_SQL=	YES
IN=	none
LOCATION=	none
MULTI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	NO
PRESERVE_TAB_NAMES=	NO
READBUFF=	1
READ_ISOLATION_LEVEL=	DB2 z/OS determines the isolation level
READ_LOCK_TYPE=	none
REMOTE_DBTYPE=	ZOS
REREAD_EXPOSURE=	NO
SCHEMA=	your user ID
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
UPDATE_ISOLATION_LEVEL=	DB2 z/OS determines the isolation level
UPDATE_LOCK_TYPE=	none
UTILCONN_TRANSIENT=	YES

DB2 under z/OS LIBNAME Statement Example

In this example, the libref MYLIB uses the DB2 under z/OS interface to connect to the DB2 database that the SSID= option specifies, with a connection to the testserver remote server.

libname mylib db2 ssid=db2
   authid=testuser server=testserver;
proc print data=mylib.staff;
   where state='CA';
run;