SAS/ACCESS Interface to DB2 Under z/OS

SQL Pass-Through Facility Specifics for DB2 Under z/OS

For general information about this feature, see Overview of the SQL Pass-Through Facility. DB2 z/OS examples are available.

Here are the SQL pass-through facility specifics for the DB2 under z/OS interface:

The dbms-name is DB2.
The CONNECT statement is optional.
The interface supports connections to multiple databases.
Here are the CONNECT statement database-connection-arguments:

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 setting up DB2 z/OS so that SAS can connect to the DRDA server when the SERVER= option is used, see the installation instructions for this interface.

Although you can specify any LIBNAME option in the CONNECT statement, only SSID= and SERVER= are honored.

Examples

This example connects to DB2 and sends it two EXECUTE statements to process.

proc sql;
   connect to db2 (ssid=db2);
   execute (create view testid.whotookorders as
            select ordernum, takenby, firstname,
            lastname, phone
            from testid.orders, testid.employees
            where testid.orders.takenby=
                  testid.employees.empid)
           by db2;
   execute (grant select on testid.whotookorders
            to testuser) by db2;
   disconnect from db2;
quit;

This next example omits the optional CONNECT statement, uses the default setting for DB2SSID=, and performs a query (shown in highlighting) on the Testid.Customers table.

proc sql;
   select * from connection to db2
     (select * from testid.customers where customer like '1%');
   disconnect from db2;
quit;

This example creates the Vlib.StockOrd SQL view that is based on the Testid.Orders table. Testid.Orders is an SQL/DS table that is accessed through DRDA.

libname vlib 'SAS-data-library'

proc sql;
  connect to db2 (server=testserver);
  create view vlib.stockord as
     select * from connection to db2
        (select ordernum, stocknum, shipto, dateorderd
            from testid.orders);
  disconnect from db2;
quit;

Top of Page