ACCESS Procedure Specifics for DB2 under z/OS

Key Information

See the ACCESS procedure for general information about this feature. DB2 under z/OS examples are available.

SAS/ACCESS Interface to DB2 under z/OS supports all ACCESS procedure statements in interactive line, noninteractive, and batch modes.

Here are the ACCESS procedure specifics for the DB2 under z/OS interface.

The DBMS= value is db2.
The database-connection-arguments are SSID=, SERVER=, and LOCATION=.

LOCATION=location

lets you further qualify where a table is located. In the DB2 z/OS 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.
Here is the TABLE= statement:

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 z/OS) user ID. In batch mode, however, you must specify an authorization ID, otherwise an error message is generated.

Examples

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

options linesize=80;
libname adlib 'SAS-library';
libname vlib 'SAS-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;

This 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-library';
libname vlib 'SAS-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;