SAS/ACCESS Interface to DB2 Under z/OS

ACCESS Procedure Specifics for DB2 Under z/OS

See 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.
Here are the database-description-statements.

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 is specified in the SAS system option, DB2SSID=. The DB2 subsystem ID is limited to four characters. See Settings for more information.

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 connect to only 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= LIBNAME option. There is no default value for this option.

For information about configuring SAS to use the SERVER= option, see the installation instructions for this interface.

LOCATION=location

enables you to 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-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;

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

Top of Page