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

Key Information

For general information about this feature, see 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.
Although you can specify in the CONNECT statement any database-connection-arguments from the LIBNAME, 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 DB2SSID= setting, 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-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;