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.

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 databases by using the alias argument to distinguish your connections.
• The CONNECT statement database-connection-arguments 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 myusr1)
           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::

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