SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Pass-Through Facility: DB2 Specifics

The following section describes the DBMS-specific arguments that you use in the CONNECT statement in order to establish a connection with a DB2 database.

Arguments to Connect to DB2

The CONNECT statement is optional when you are connecting to DB2. DB2 has two database-connection-arguments that you can specify in this statement. CONNECT can also be used to connect to multiple DB2 systems.

CONNECT TO DB2<AS alias><(SSID=DB2-subsystem-id>
<SERVER=DRDA-database-system)>;

SSID=DB2-subsystem-id

specifies the DB2 subsystem ID that you want to connect to. The ID is limited to four characters. Refer to Setting Your DB2 Subsystem Identifier for more information on setting SSID= or contact your DBA.

The SSID= argument is optional. If you omit it, the SAS System connects to the default DB2 subsystem that is specified by the SAS system option DB2SSID=. If your site has not set DB2SSID=, the SSID= argument is required.

Refer to Setting Your DB2 Subsystem Identifier for information on setting your subsystem ID or contact your DBA.

SERVER=DRDA-database-system

enables direct access to DRDA resources (such as SQL/DS tables) from the SAS/ACCESS interface to DB2. SERVER= is an optional statement.

Enter a DRDA database system name that is assigned by your system administrator to make the connection to the desired database system. Check with your system administrator for valid database system names. You can connect with only one system at a time.

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

The following example omits the optional CONNECT statement and performs a query shown in italics on the TESTID.CUSTOMERS table:

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

The next example creates the PROC SQL view VLIB.STOCKORD that is based on the table TESTID.ORDERS. The 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;

Chapter Contents
Previous
Next
Top of Page