SAS/ACCESS Software for Relational Databases: Reference

ACCESS Procedure: DB2 Specifics

The following section describes the DBMS-specific statements that you use in the SAS/ACCESS interface to DB2.

ACCESS Procedure Statements for DB2

To create an access descriptor, you use the DBMS=DB2 option and the TABLE= database-description statements in the PROC ACCESS step. This database-description statement supplies the DBMS name to the SAS System. The TABLE= statement must immediately follow the CREATE statement. The CREATE statement specifies the access descriptor to be created.

Database-description statements are required only when you create access descriptors. Because DB2 information is stored in an access descriptor, you do not need to repeat this information when you create view descriptors.

Note: Unlike some other SAS/ACCESS interface products, the SAS/ACCESS interface to DB2 does not use the following procedure statements: USER=, PASSWORD=, and DATABASE=. [cautionend]

The SAS/ACCESS interface to DB2 uses the following procedure statements in interactive line, noninteractive, or batch mode.

PROC ACCESS <access-descriptor-options|view-descriptor-options>;

CREATE libref.member-name. ACCESS|VIEW;

UPDATE libref.member-name. ACCESS|VIEW;

SSID=DB2-subsystem-id;

SERVER=DB2-database-system|DRDA-database-system;

LOCATION=location;

TABLE=<authorization-id.>table-name;

ASSIGN <=> YES|NO|Y|N;

DROP <'>column-identifier-1<'><...<'>column-identifier-n<'>>;

FORMAT<'>column-identifier-1<'><=>SAS-format-name-1
<...<'>column-identifier-n<'><=>SAS-format-name-n>;

QUIT;

RENAME<'>column-identifier-1<'><=>SAS-variable-name-1
<...<'>column-identifier-n<'><=>SAS-variable-name-n;>

RESETALL|<'>column-identifier-1<'><...<'>column-identifier-n<'>>;

SELECTALL|<'>column-identifier-1<'><...<'>column-identifier-n<'>>;

SUBSETselection-criteria;

UNIQUE<=> YES|NO|Y|N;

LIST ALL | VIEW|<'>column-identifier <'>;

RUN ;

SERVER= DRDA-database-system;

enables direct access to DRDA resources (such as SQL/DS tables) from the SAS/ACCESS interface to DB2. SERVER= is an optional statement.

Enter a DRDA database system name assigned by your system administrator to make the connection to the desired database. Check with your system administrator for valid system names. You can connect with only one server at a time.

SSID=DB2-subsystem-id;

specifies the DB2 subsystem ID to use for the access descriptor. The DB2-subsystem-id is limited to four characters. Refer to Setting Your DB2 Subsystem Identifier for more information on setting SSID=.

The SSID= statement is optional. If you omit it, the SAS System connects to the DB2 subsystem that is specified by the SAS system option DB2SSID=. If your site has not set DB2SSID=, the SSID= statement is required.

Consult your DBA to determine when the DRDA resources are set up properly. Refer to Connections Using the Distributed Relational Database Architecture (DRDA) for more information.

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 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.

TABLE= <authorization-id.>table-name;

identifies the DB2 table or DB2 view that you want to use to create an access descriptor. The table-name is limited to 18 characters. The TABLE= statement is required.

The authorization-id is a user ID or group ID that is associated with the DB2 table. The authorization ID is limited to eight characters. If you omit the authorization ID, DB2 uses your TSO (or OS/390) user ID. In batch mode, however, you must specify an authorization ID, otherwise an error message is generated.

DB2 Restriction on Connections

The DB2 interface engine restricts the maximum concurrent open cursors to 32 when working from a single connection. Note that if you are working with a SAS view that accesses other views, 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 interface engine uses the Call Attachment Facility (CAF) or Recoverable Resource Manager Service Attachment Facility (RRSAF) to make an explicit connection to the local DB2 subsystem. For each connection to the CAF, the DB2 interface engine attaches a separate OS/390 subtask. Note that if you establish too many separate connections, you can adversely affect your performance.

Examples: Creating Access Descriptors and View Descriptors

The following example creates an access descriptor and a view descriptor that are based on DB2 data.

options linesize=80;
libname adlib 'SAS-data-library';
libname vlib 'SAS-data-library';

proc access dbms=db2;

   /* create access descriptor  */
   create adlib.customr.access;      
   table=testid.customers;
   ssid=db2;
   assign=yes;
   rename customer = custnum;
   format firstorder date7.;
   list all;

   /* create vlib.usacust view */
   create vlib.usacust.view;         
   select customer state zipcode name
          firstorder;
   subset where customer like '1%';
run;

The next example uses the SERVER= statement to access the SQL/DS table TESTID.ORDERS from a remote location. Access and view descriptors are then created, based on the table.

libname adlib 'SAS-data-library';
libname vlib 'SAS-data-library';

proc access dbms=db2;
  create adlib.customr.access;
  table=testid.orders;
  server=testserver;
  assign=yes;
  list all;

 create vlib.allord.view;
  select ordernum stocknum shipto dateorderd;
    subset where stocknum = 1279;
run;

Chapter Contents
Previous
Next
Top of Page