Chapter Contents

Previous

Next

LIBNAME Statement: DB2 Specifics

Valid:

in a DATA or PROC step

Arguments

libref

is any SAS name that serves as an alias to associate the SAS System with a database.

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. If you specify the appropriate system options or environment variables for your database prior to invoking SAS, 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 ROWSET_SIZE= option enables you to specify the number of rows to use when reading data from the DBMS. 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 in the particular database (which is accessed 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 represents a DBMS table or view), the SAS System uses the value that is specified after the data set name.

For more information, see SAS/ACCESS LIBNAME Statement .

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 DBMS, 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, SAS software supports reading, updating, creating, and deleting DBMS tables when the LIBNAME engine is used.

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

SAS/ACCESS Engine Connection Options

This section describes the connection options for DB2. The connection options are as follows:

	USER=
	PASSWORD=
	DATASRC=
	COMPLETE=
	NOPROMPT=
	PROMPT=
	REQUIRED=
	AUTOCOMMIT=

There are multiple ways that you can connect to the DBMS when using the LIBNAME statement. Use only one of the following methods for each connection since they are mutually exclusive:

• specify USER=, PASSWORD=, and DATABASE=, or

• specify COMPLETE=, or

• specify NOPROMPT=, or

• specify PROMPT=, or

• specify REQUIRED=.

USER=<'>username<'>

enables you to connect to a DB2 database with a user ID that is different from the default ID.

The USER= and PASSWORD= connections are optional in DB2. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID for your operating environment is used.

USER= can also be specified with the UID= alias.

PASSWORD=<'>password<'>

specifies the DB2 password that is associated with your DBMS user ID.

The USER= and PASSWORD= connection options are optional in DB2 because users may have default user IDs. If you specify USER=, you must specify PASSWORD=.

PASSWORD= can also be specified with the PW=, USING=, PASS=, and PWD= aliases.

DATASRC=<'>data-source-name<'>

specifies the DB2 data source or database to which you want to connect.

DATASRC= is an optional connection option. If you omit it, you connect by using a default environment variable.

DATASRC= can also be specified with the DSN=, DS=, and DATABASE= aliases.

AUTOCOMMIT=YES | NO

indicates whether or not updates are committed immediately after they are submitted.

If AUTOCOMMIT=NO, the SAS/ACCESS engine does the commit automatically when it reaches the end of the file. This is the default for everything except for the SQL Procedure Pass-Through Facility and read-only connections.

If AUTOCOMMIT=YES, no rollback is possible. This is the default for the SQL Procedure Pass-Through Facility and read-only connections.

COMPLETE=<'>CLI-connection-string<'>

specifies connection options for your data source or database. If you specify enough correct connection options, the SAS/ACCESS engine connects to your data source or database. Otherwise, you are prompted for the connection options with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the data source. You separate multiple options with a semicolon. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.

COMPLETE= is similar to the PROMPT= option. However, if COMPLETE= attempts to connect and fails, then a dialog box is displayed and you can edit values or enter additional values. COMPLETE= is optional.

See your driver documentation for more details.

NOPROMPT=<'>CLI-connection-string<'>

specifies connection options for your data source or database. You separate multiple options with a semicolon. If you specify enough correct connection options, the SAS/ACCESS engine connects to the data source or database. Otherwise, an error is returned and no dialog box is displayed. NOPROMPT= is optional. If connection options are not specified, the default settings are used.

PROMPT=<'> CLI-connection-string<'>

specifies connection options to the data source.

A dialog box is displayed, using the values from the PROMPT= connection string. You can edit any field before you connect to the data source. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.

PROMPT= is similar to the COMPLETE= option. However, unlike COMPLETE=, PROMPT= does not attempt to connect to the DBMS first. It displays the dialog box where you can edit or enter additional values. PROMPT= is optional.

REQUIRED=<'>CLI-connection-string <'>

specifies connection options for your data source or database. You separate multiple options with a semicolon.

If you specify enough correct connection options, such as user ID, password, and data source name, the SAS/ACCESS engine connects to the data source or database. Otherwise, a dialog box is displayed to prompt you for the connection options. Options in the dialog box that are not related to the connection are disabled. REQUIRED= only allows you to modify required fields in the dialog box. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.

REQUIRED= is similar to COMPLETE= because it attempts to connect to the DBMS first. However, if REQUIRED= attempts to connect and fails, then a dialog box is displayed and you can only edit values that are in the required fields. REQUIRED= is optional.

SAS/ACCESS LIBNAME Options

The SAS/ACCESS interface to DB2 supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Statement . 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:

	CURSOR_TYPE
	DBINDEX=
	PRESERVE_COL_NAMES=
	PRESERVE_TAB_NAMES=
	QUERY_TIMEOUT=
	READ_ISOLATION_LEVEL=
	READ_LOCK_TYPE=
	ROWSET_SIZE=
	SCHEMA=
	SPOOL=
	STRINGDATES=
	TRACE=
	TRACEFILE=
	UPDATE_ISOLATION_LEVEL=
	UPDATE_LOCK_TYPE =

CURSOR_TYPE=DYNAMIC | FORWARD_ONLY | KEYSET_DRIVEN | STATIC

specifies the cursor type for read-only and updatable cursors. Not all drivers support all cursor types. An error is returned if the specified cursor type is not supported.

By default, CURSOR_TYPE=DYNAMIC, but the driver is allowed to modify the default without an error.

If CURSOR_TYPE=DYNAMIC, then the cursor reflects all of the changes that are made to the rows in a result set as you scroll around the cursor. The data values and the membership of rows in the cursor can change dynamically on each fetch.

If CURSOR_TYPE=FORWARD_ONLY, then the cursor behaves like a DYNAMIC cursor except that it only supports fetching the rows sequentially.

If CURSOR_TYPE=KEYSET_DRIVEN, then the cursor determines which rows belong to the result set when the cursor is opened. However, changes that are made to these rows will be reflected as you scroll around the cursor.

If CURSOR_TYPE=STATIC, then the cursor builds the complete result set when the cursor is opened. No changes that are made to the rows in the result set after the cursor is opened will be reflected in the cursor. Static cursors are read-only.

CURSOR_TYPE= can also be specified with the CURSOR= alias.

DBINDEX=YES | NO

indicates whether or not SAS calls DB2 to find all indexes that are on the specified table.

Default value: YES.

For a full description of this option, refer to DBINDEX= .

PRESERVE_COL_NAMES=YES|NO

preserves spaces, special characters, and mixed case in DBMS column names.

Default value: NO

The default value for PRESERVE_COL_NAMES= under DB2 is NO because DB2 is case insensitive and all names default to uppercase. For a full description of this option, refer to PRESERVE_COL_NAMES= .

PRESERVE_TAB_NAMES=YES|NO

preserves spaces, special characters, and mixed case in DBMS table names.

Default value: NO

The default value for PRESERVE_TAB_NAMES= under DB2 is NO because DB2 is case insensitive and all names default to uppercase. For a full description of this option, refer to PRESERVE_TAB_NAMES= .

QUERY_TIMEOUT=number-of-records

specifies the number of seconds of inactivity to wait before canceling a query.

Default value: 0

The default value of 0 indicates that there is no time limit for a query. This option is useful when you are testing a query or if you suspect that a query might contain an endless loop.

QUERY_TIMEOUT= can also be specified with the TIMEOUT= alias.

READ_ISOLATION_LEVEL= RR | RS | CS | UR

defines the degree of isolation of the current application process from other concurrently running application processes. The isolation levels are as follows and are thoroughly described below:

	RR = Repeatable Read
	RS = Read Stability
	CS = Cursor Stability
	UR = Uncommitted Read

Default value: CS

The degree of isolation identifies

• the degree with which rows that are read and updated by the current application are available to other concurrently executing applications

• the degree with which update activity of other concurrently executing application processes can affect the current application.

The DB2 database manager supports four isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application process until the unit of work is complete. The isolation levels are defined in terms of several possible occurrences:

• Dirty read -- A transaction that exhibits this phenomenon has very minimal isolation from concurrent transactions. In fact, it will be able to see changes made that are by those concurrent transactions even before they commit.

  For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.

• Nonrepeatable read -- If a transaction exhibits this phenomenon, it is possible that it may read a row once and, if it attempts to read that row again later in the course of the same transaction, the row might have been changed or even deleted by another concurrent transaction. Therefore, the read is not (necessarily) repeatable.

  For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.

• Phantom reads -- When a transaction exhibits this phenomemon, a set of rows that it reads once might be a different set of rows if the transaction attempts to read them again.

  For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it will see a row that did not previously exist, a phantom.

The isolation levels for READ_ISOLATION_LEVEL= include the following:

• Repeatable Read (RR)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • does not allow phantom reads

• Read Stability (RS)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • allows phantom reads

• Cursor Stability (CS)
  • does not allow dirty reads

  • allows nonrepeatable reads

  • allows phantom reads

  This is the default value for DB2.

• Uncommitted Read (UR)
  • allows dirty reads

  • allows nonrepeatable reads

  • allows phantom reads

READ_ISOLATION_LEVEL= is ignored if READ_LOCK_TYPE= is not set to ROW.

READ_ISOLATION_LEVEL= can also be specified with the RIL= alias.

See Also: UPDATE_ISOLATION_LEVEL=.

READ_LOCK_TYPE=ROW | TABLE

specifies how DB2 tables are locked during a READ operation.

Default value: ROW

If READ_LOCK_TYPE=ROW, the row is locked for read operations. This prevents concurrent reads on a row.

If READ_LOCK_TYPE=TABLE, the table is locked for read operations. This prevents concurrent reads on a table.

For a full description of this option, refer to READ_LOCK_TYPE= .

See also: UPDATE_LOCK_TYPE=.

ROWSET_SIZE=number-of-rows

specifies the number of rows to use when reading data from the DBMS.

Default value: 0

When ROWSET_SIZE=0, no internal SAS buffering is performed. Setting ROWSET_SIZE=0 causes the SQLFetch API call to be used.

When ROWSET_SIZE=1, only one row is retrieved at a time. The higher the value for ROWSET_SIZE=, the more rows the DB2 engine retrieves in one fetch operation. This option reduces the amount of I/O that is used and can help improve performance. However, because SAS software stores the rows in memory, higher values for ROWSET_SIZE= use more memory. In addition, if too many rows are selected at once, then the rows that are returned to the SAS application might be out of date. For example, if someone else modified the rows, you would not see the changes. Setting ROWSET_SIZE=1 or greater causes the SQLExtendedFetch API call to be used.

SCHEMA=schema-name

enables you to read database objects, such as tables and views, in the specified schema.

SCHEMA= is optional. If it is omitted, you connect to the default schema, which is your user ID. In the following LIBNAME statement example, the SCHEMA= option causes any reference in SAS to mydb.employee to be interpreted by DB2 as scott.employee.

 libname mydb db2 SCHEMA=scott; 

SCHEMA= may also be specified with the OWNER= alias.

SPOOL=YES | NO

specifies whether or not SAS creates a utility spool file during read operations that are performed with the specified LIBNAME.

Default value: YES

For a full description of this option, refer to SPOOL= .

STRINGDATES=YES | NO

specifies whether or not to read date and time values from the DB2 database as character strings or as numeric date values.

Default value: NO

If STRINGDATES=YES, then the SAS application reads date-time values as character strings, 'YYYY-MM-DD'.

If STRINGDATES=NO, then the SAS application reads date-time values as numeric date values.

STRINGDATES=NO is used for Version 6 compatibility.

STRINGDATES= can also be specified with the STRDATES= alias.

TRACE=YES | NO

specifies whether or not to turn on tracing information that is used in debugging.

Default value: NO

If TRACE=YES, tracing is turned on, and the DB2 driver manager writes each function call to the trace file that is specified by TRACEFILE=.

If TRACE=NO, tracing is not turned on.

See also: TRACEFILE=

TRACEFILE=filename

specifies the filename to which the DB2 driver manager writes trace information.

Default value: none

TRACEFILE= is used only when TRACE=YES.

See also: TRACE=.

UPDATE_ISOLATION_LEVEL= CS | RS | RR

defines the degree of isolation of the current application process from other concurrently running application processes. The isolation levels are as follows and are thoroughly described here:

	CS = Cursor Stability
	RS = Read Stability
	RR = Repeatable Read

Default value: CS

The degree of isolation identifies

• the degree with which rows that are read and updated by the current application are available to other concurrently executing applications

• the degree with which update activity of other concurrently executing application processes can affect the current application.

The DB2 database manager supports three isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application process until the unit of work is complete. The isolation levels are defined in terms of several possible occurrences:

• Dirty read -- A transaction that exhibits this phenomenon has a very minimal isolation from concurrent transactions. In fact, it will be able to see changes that are made by those concurrent transactions even before they commit.

  For example, suppose that transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.

• Nonrepeatable read -- If a transaction exhibits this phenomenon, it is possible that it may read a row once and, if it attempts to read that row again later in the course of the same transaction, the row might have been changed or even deleted by another concurrent transaction. Therefore, the read is not (necessarily) repeatable.

  For example, suppose that transaction T1 retrieves a row, transaction T2 then updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.

• Phantom reads -- When a transaction exhibits this phenomemon, a set of rows that it reads once might be a different set of rows if the transaction attempts to read them again.

  For example, suppose that transaction T1 retrieves the set of all rows that satisfy some condition. Suppose that transaction T2 then inserts a new row that satisfies that same condition. If transaction T1 now repeats its retrieval request, it will see a row that did not previously exist, a phantom.

The isolation levels for UPDATE_ISOLATION_LEVEL= include the following:

• Repeatable Read (RR)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • does not allow phantom reads

• Read Stability (RS)
  • does not allow dirty reads

  • does not allow nonrepeatable reads

  • allows phantom reads

• Cursor Stability (CS)
  • does not allow dirty reads

  • allows nonrepeatable reads

  • allows phantom reads

  This is the default value for DB2.

UPDATE_ISOLATION_LEVEL= is ignored if UPDATE_LOCK_TYPE= is not set to ROW.

UPDATE_ISOLATION_LEVEL= may also be specified with the UIL= alias.

See Also: READ_ISOLATION_LEVEL=.

UPDATE_LOCK_TYPE = ROW|TABLE

specifies how a DB2 table is locked during an UPDATE operation.

Default value: ROW

If UPDATE_LOCK_TYPE=ROW, the row is locked for update operations. This prevents concurrent updates on a row.

If UPDATE_LOCK_TYPE=TABLE, the table is locked for update operations. This prevents concurrent updates on a table.

For a full description of this option, refer to UPDATE_LOCK_TYPE = .

See also: READ_LOCK_TYPE=.

Example: Specifying a LIBNAME Statement to Access DB2 Data

In this example, the libref MYDBLIB uses the DB2 engine to connect to a DB2 database by using the SAS/ACCESS engine connection option NOPROMPT=. PROC PRINT is used to display the contents of the DB2 table CUSTOMERS.

libname mydblib db2 
   noprompt="user=testuser;password=testpass;database=testdb;"

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

Chapter Contents

Previous

Next

Top of Page

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