SAS/ACCESS Software for Relational Databases: Reference

ACCESS Procedure: DB2 Specifics

This section describes the statements that you use in the SAS/ACCESS Interface to DB2 under the OS/2 operating environment.

Operating Environment Information: PROC ACCESS is valid only for DB2 running under OS/2. It is not valid under any other operating environment. [cautionend]

ACCESS Procedure Statements for DB2

To create an access descriptor, you use database identification statements that supply DBMS-specific information to the SAS System. These database identification statements must immediately follow the CREATE statement that specifies the access descriptor to be created.

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

The SAS/ACCESS Interface to DB2 uses the following procedure statements in batch mode:

PROC ACCESS DBMS=DB2 <view-descriptor-options>;

CREATE <libref.>member-name. ACCESS|VIEW;

UPDATE <libref.>member-name. ACCESS|VIEW;

IN|DATABASE|DSN=<'>database-name<'>;

TABLE=<'>schema-name.table-name<'>

ASSIGN | AN<=>YES|NO;

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

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

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

QUIT | EXIT;

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

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

SELECT ALL|<'>column-identifier-1<'><...<'>column-identifier-n<'>>;

SUBSETselection-criteria;

UNIQUE | UN<=>YES | NO;

RUN;

IN|DATABASE|DSN= <'>database-name<'>;

specifies the name of the database where the DB2 table resides. Database name is limited to eight characters. The IN statement is required and follows the CREATE statement. DATABASE= and DSN= are aliases for the IN statement.

The database that you specify must already exist. If the database name contains the following special characters (_,$,@,#), you must enclose it in quotes. However, DB2 recommends against using special characters in database names.

TABLE= <'><schema-name.>table-name<'>;

identifies the DB2 table or DB2 view that you want to use to create an access descriptor. Table name is limited to 18 characters. If you quote the name, it is case-sensitive. The TABLE= statement is required.

Schema-name is a person's name or group ID that is associated with the DB2 table. The schema name is limited to eight characters.

The following example creates an access descriptor and a view descriptor that are based on DB2 data. For the DBMS= option in the PROC ACCESS statement, use db2.

options linesize=80;
/* create access descriptor  */

proc access dbms=db2;
   create adlib.customr.access;      
   in sample; user=testuser; password=testpass;
   table=sasdemo.customers;
   assign=yes;
   rename customer=custnum;
   format firstorder date9.;
   list all;

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

Chapter Contents
Previous
Next
Top of Page