Chapter Contents

Previous

Next

SAS/ACCESS LIBNAME Statement: DB2 Specifics

Valid:

Anywhere

Arguments

libref

is any 8-character SAS name that serves as an alias to associate the SAS System with a database, schema, server, or group of tables and views.

SAS/ACCESS-engine-name

is a SAS/ACCESS engine name for your DBMS, in this case, DB2. SAS/ACCESS engines are implemented differently in different operating environments. The engine name is required.

SAS/ACCESS-engine-connection-options

are options that you specify in order to connect to a particular database; these options are different for each database. If the SAS/ACCESS engine connection options contain characters that are not allowed in SAS names, enclose the values of the options in quotation marks. On some DBMSs, if you specify the appropriate system options or environment variables for your database, you can often omit the SAS/ACCESS engine connection options.

SAS/ACCESS-LIBNAME-options

are options that apply to the objects in a DBMS, such as its tables or indexes. For example, the READ_LOCK_TYPE= option enables you to set a table lock on an operation. Support for many of these options is DBMS-specific.

Some SAS/ACCESS LIBNAME options can also be specified as SAS/ACCESS engine data set options. When you specify an option in the LIBNAME statement, it applies to objects and data that are referenced by the libref. A SAS/ACCESS data set option applies only to the data set on which it is specified. If a like-named option is specified in both the SAS/ACCESS engine LIBNAME statement and after a data set name (which references a DBMS table or view), the SAS System uses the value that is specified after the data set name.

For more information, refer to SAS/ACCESS Data Set Options

Details

The LIBNAME statement associates a libref with a SAS/ACCESS engine in order to access tables or views in a database management system (DBMS). The SAS/ACCESS engine enables you to connect to a particular DBMS and, therefore, to specify a DBMS table or view name in a two-level SAS name.

For example, in MYLIB.EMPLOYEES_Q2, MYLIB is a SAS libref that points to a particular group of DBMS objects, and EMPLOYEES_Q2 is a DBMS table name. When you specify MYLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. Beginning in Version 7, the SAS/ACCESS LIBNAME supports reading, updating, creating, and deleting DBMS tables.

To disassociate or clear a libref from a DBMS, use a LIBNAME statement and specify the libref (for example, MYDBLIB) and the CLEAR option as follows:

libname mydblib CLEAR;

Clearing the libref causes the database engine to disconnect from the database, closing any open plans and releasing any resources that are associated with that connection.

See SAS/ACCESS LIBNAME Options for more information about options that you can use in the LIBNAME statement.

SAS/ACCESS Engine Connection Options

The SAS/ACCESS engine connection options for DB2 are as follows:

	SSID=
	SERVER=

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 was specified in the SAS system option, DB2SSID=. For more information, see Setting Your DB2 Subsystem Identifier. The DB2-subsystem-id is limited to four characters.

SERVER=DRDA server

specifies the DRDA server that you want to connect to. SERVER= enables you to access DRDA resources stored at remote locations. Check with your system administrator for system names. You can only connect to 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= option. There is no default value for this option.

Note:   Refer to the OS/390 installation instructions for information about configuring SAS to use the SERVER= option.  

DB2 Restriction on Connections

The DB2 engine restricts the maximum concurrent open cursors to 32 for a given connection. Note that if you are working with a DB2 table that accesses other tables, you could be opening more cursors than you are aware of.

Beginning in Version 7, there is no limit to the number of connections that you can have to DB2. The DB2 engine uses the Call Attachment Facility (CAF) or the Recoverable Resource Manager Sercvice Attachment Facility (RRSAF) to make an explicit connection to the local DB2 subsystem. The DB2 engine creates one connection to DB2 from the main SAS task. For each subsequent connection to the CAF or RRSAF, the DB2 engine attaches a separate OS/390 subtask.

SAS/ACCESS LIBNAME Options

The SAS/ACCESS interface to DB2 supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options, except for DBINDEX=, DBPROMPT=, and DBMAX_TEXT=. In addition to the supported options, the following LIBNAME options are used only in the interface to DB2 or have DB2-specific aspects to them:

	AUTHID=
	CONNECTION=
	IN=
	LOCATION=
	PRESERVE_COL_NAMES=
	PRESERVE_TAB_NAMES=
	READ_LOCK_TYPE=
	SPOOL=
	UPDATE_LOCK_TYPE=

This section describes the LIBNAME statement and its options that are specific to DB2. The LIBNAME statement and options that can be used in most databases are fully described in "SAS/ACCESS Software for Relational Databases." This section describes the connection options for DB2 and any DB2-specific LIBNAME options.

AUTHID=authid

enables you to qualify your DB2 table names with an authorization ID, user ID, or group ID.

When you specify the AUTHID= option, every table that is referenced by the libref is qualified as authid.tablename before any SQL code is passed to DB2. If you do not specify a value for AUTHID=, the table name is not qualified before it is passed to DB2. Once DB2 receives the table name, it automatically qualifies it with your OS/390 user ID. You can override the LIBNAME AUTHID= option by using the AUTHID= data set option.

AUTHID= is limited to 8 characters. This option is not validated until you access a table.

CONNECTION= SHAREDREAD | GLOBALREAD | UNIQUE | SHARED | GLOBAL

enables you to share one connection for reading, updating, and outputting to tables.

In addition to the SHAREDREAD, GLOBALREAD, and UNIQUE values, which are described in SAS/ACCESS LIBNAME Statement, you can also specify SHARED or GLOBAL for CONNECTION= in the SAS/ACCESS interface to DB2. The SHARED value allows you to share one connection for tables referenced by the given libref. The GLOBAL value allows you to share a connection for tables referenced by all librefs for which CONNECTION=GLOBAL is specified.

Use this option with caution. If the connections that are shared are for reading and updating, there is a good possibility that the read cursors will have to be resynchronized if a commit or rollback is performed by the update or output connections. If the cursors are resynchronized, there is no guarantee that the new solution table will match the original solution table that was being read.

You can use CONNECTION=SHARED to eliminate the deadlock that can occur when you create and load a DB2 table from an existing DB2 table that resides in the same database or tablespace. This only happens in certain output processing situations and is the only recommended use for the CONNECTION=SHARED option.

In the following example, DB2DATA.NEW is created in the database TEST. Because the table DB2DATA.OLD exists in the same database, the option CONNECTION=SHARED allows the DB2 engine to share the connection for both reading the old table and for creating and loading the new table.

libname db2data db2 connection=shared;
data db2data.new (in = 'database test');
 set db2data.old;
run;

In the following example, you have two different librefs that share one connection.

libname db2lib db2 connection=global;
libname db2data db2 connection=global;
data db2lib.new(in='database test');
   set db2data.old;
run;

If you did not use the CONNECTION= option in the above examples, you would deadlock in DB2 and get the following error:

ERROR:  Error attempting to CREATE a DBMS table.  
ERROR:  DB2 execute error DSNT408I SQLCODE = -911,
ERROR:  THE CURRENT UNIT OF WORK HAS BEEN ROLLED 
        BACK DUE TO DEADLOCK.

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement

IN='database-name.tablespace-name'| 'DATABASE database-name'

enables you to specify the database and tablespace in which you want to create a new table. The IN= option is relevant only when you are creating a new table. If you omit this option, the DB2 default is to create the table in the default database, implicitly creating a simple tablespace.

'database.tablespace' specifies the names of the database and tablespace. Enclose the entire specification in single quotes.

'DATABASE database-name' specifies only the database name. Enclose the entire specification in single quotes.

LOCATION=location

enables you to further qualify exactly where a table resides.

In the DB2 engine, the location is converted to the first level of a three-level table name: LOCATION.AUTHID.TABLE. The connection to the remote DB2 subsystem is done implicitly by DB2's Distributed Data Facility (DDF) when DB2 receives a three-level table 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.

PRESERVE_COL_NAMES=YES|NO

preserves spaces, special characters, and case in DB2 column names.

The default value for PRESERVE_COL_NAMES= under DB2 is NO because DB2 converts table and column names to uppercase by default. However, DB2 is a case-sensitive DBMS. To preserve the case of the column names that you send to DB2, use quotation marks around the column names.

PRESERVE_NAMES= is a combination alias for both PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES=. If you set PRESERVE_NAMES=YES, it is equivalent to setting both of these options to YES.

For a full description of PRESERVE_COL_NAMES=, refer to SAS/ACCESS LIBNAME Statement

PRESERVE_TAB_NAMES=YES|NO

preserves spaces, special characters, and case in DB2 table names.

The default value for PRESERVE_TAB_NAMES= under DB2 is NO because DB2 converts table and column names to uppercase by default. However, DB2 is a case-sensitive DBMS. To preserve the case of the table names that you send to DB2, use quotation marks around the table names.

PRESERVE_NAMES= is a combination alias for both PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES=. If you set PRESERVE_NAMES=YES, it is equivalent to setting both of these options to YES.

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement

READ_LOCK_TYPE=TABLE

specifies that a SHARED table lock is set on a DB2 table during a READ operation.

READ_LOCK_TYPE= is useful if you want to lock out concurrent changes in order to access an entire table as it is at that particular moment. You can also use it to prevent timeouts from contention with other application processes that are reading the same table.

The default behavior is to allow DB2 to handle the locking process. Consult your DBA to determine what the locking process is for your installation of DB2.

If you set READ_LOCK_TYPE=TABLE, then you must also set the CONNECTION= option to UNIQUE, which means that there is a separate physical connection for each table that is opened in your SAS application. You cannot share a connection when a DB2 table is locked. If you do not set the CONNECTION= option to UNIQUE, the SAS step fails.

If you set READ_LOCK_TYPE=TABLE, you might also want to evaluate whether or not you want to change the SPOOL= option from its default value of YES. If the table is locked and its data cannot be changed during the read transaction, you may not need to create a utility spool file.

Note:   Use READ_LOCK_TYPE= with caution because it locks all the tables in a nonsegmented table space, even if they are not the table that is specifically locked. The locks are held until a commit point or until the connection is freed. All other application processes are locked out of the non-segmented table space for the duration of the lock.  
For a full description of this option, refer to SAS/ACCESS LIBNAME Statement

SPOOL=YES|NO

specifies whether SAS creates a utility spool file during read operations that require two passes through the table and that are performed with the specified libref.

The default value is SPOOL=YES. For a full description of this option, refer to SAS/ACCESS LIBNAME Statement

UPDATE_LOCK_TYPE = TABLE

specifies that an exclusive table lock is set on a DB2 table during an UPDATE operation.

If you are updating a large part of a table, you can improve your performance by using UPDATE_LOCK_TYPE= to lock the entire table and prevent other application processes from having concurrent access to it. This is more efficient than locking each page as it is updated and then unlocking it when the changes are committed. You can also use UPDATE_LOCK_TYPE= to prevent timeouts from contention with other application processes that are updating the same table.

The default behavior is to allow DB2 to handle the locking process. Consult your DBA to determine what the locking process is for your installation of DB2.

If you set UPDATE_LOCK_TYPE=TABLE, then you must also set the CONNECTION= option to UNIQUE, which means that there is a separate physical connection for each table that is opened in your SAS application. You cannot share a connection when a DB2 table is locked. If you do not set the CONNECTION= option to UNIQUE, the SAS step fails.

If you set UPDATE_LOCK_TYPE=TABLE, you might also want to evaluate whether or not you want to change the SPOOL= option from its default value of YES. If the table is locked and its data cannot be changed during the update transaction, you might not need to create a utility spool file.

Note:   Use UPDATE_LOCK_TYPE= with caution because it locks all the tables in a nonsegmented table space, even if they are not the table that is specifically locked. The locks are held until a commit point or until the connection is freed. All other application processes are locked out of the non-segmented table space for the duration of the lock.  

For a full description of this option, refer to SAS/ACCESS LIBNAME Statement

Specifying a LIBNAME Statement to Access DB2 Data

In this example, the libref MYLIB uses the DB2 engine to connect to the DB2 database that is specified by the SSID= option with a connection to the remote server, testserver. The SAS/ACCESS engine connection options are SSID= and SERVER=.

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

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.