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

Key Information

For general information about this feature, see 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.

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 mytest 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).