SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts

SQL Pass-Through Facility Specifics for DB2 Under UNIX and PC Hosts

For general information about this feature, see Overview of the SQL Pass-Through Facility. DB2 under UNIX and PC Hosts examples are available.

Here are the SQL pass-through facility specifics for the DB2 under UNIX and PC Hosts interface.

The dbms-name is DB2.
The CONNECT statement is required.
You can connect to only one DB2 database at a time. However, you can use multiple CONNECT statements to connect to multiple DB2 data sources by using the alias argument to distinguish your connections.
The database-connection-arguments for the CONNECT statement are identical to its LIBNAME connection options.
These LIBNAME options are available with the CONNECT statement:

AUTOCOMMIT=

CURSOR_TYPE=

QUERY_TIMEOUT=

READBUFF=

READ_ISOLATION_LEVEL=

Examples

This example connects to the SAMPLE database and sends it two EXECUTE statements to process.

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

This example connects to the SAMPLE database by using an alias (DB1) and performs a query, shown in italic type, on the SASDEMO.CUSTOMERS table.

proc sql;
   connect to db2 as db1 (database=sample);
   select *
      from connection to db1
         (select
* from sasdemo.customers
             where
customer like '1%');
   disconnect from db1;
quit;

Special Catalog Queries

SAS/ACCESS Interface to DB2 under UNIX and PC Hosts supports the following special queries. You can use the queries to call the ODBC-style catalog function application programming interfaces (APIs). Here is the general format of these queries:

DB2::SQLAPI "parameter 1","parameter n"

DB2::: is required to distinguish special queries from regular queries.
SQLAPI: is the specific API that is being called. Neither DB2:: nor SQLAPI are case sensitive.
"parameter n": is a quoted string that is delimited by commas.

Within the quoted string, two characters are universally recognized: the percent sign (%) and the underscore (_). The percent sign matches any sequence of zero or more characters, and the underscore represents any single character. To use either character as a literal value, you can use the backslash character (\) to escape the match characters. For example, this call to SQLTables usually matches table names such as myatest and my_test:

select * from connection to db2 (DB2::SQLTables "test","","my_test");

Use the escape character to search only for the my_test table:

select * from connection to db2 (DB2::SQLTables "test","","my\_test");

SAS/ACCESS Interface to DB2 under UNIX and PC Hosts supports these special queries:

DB2::SQLDataSources: returns a list of database aliases that have been cataloged on the DB2 client.
DB2::SQLDBMSInfo: returns information about the DBMS server and version. It returns one row with two columns that describe the DBMS name (such as DB2/NT) and version (such as 8.2).

Top of Page

	AUTOCOMMIT=
	CURSOR_TYPE=
	QUERY_TIMEOUT=
	READBUFF=
	READ_ISOLATION_LEVEL=