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

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 z/OS interface:

• The dbms-name is DB2.
• The CONNECT statement is optional.
• The interface supports connections to multiple databases.
• The maximum length of an SQL statement is 1 megabyte.
• The LIBNAME options that are accepted in the CONNECT statement are indicated in SAS/ACCESS LIBNAME Options.

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 myusr1) by db2;
   disconnect from db2;
quit;

This next example omits the optional CONNECT statement, uses the DB2SSID= value, 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;

Special Catalog Queries

SAS/ACCESS Interface to DB2 under z/OS supports the following special queries. You can use the queries to call functions in ODBC-style function application programming interfaces (APIs). Here is the general format of the special queries:

DBMS::

is required to distinguish special queries from regular queries. DBMS:: is not case sensitive.

SQLAPI

is the specific API that is being called. an SQLAPI name is not 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 PrimaryKeys matches table names such as myatest and my_test:

select * from connection to db2
   (DBMS::PrimaryKeys( '', 'JillSmith', 'my_test');

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

select * from connection to db2
   (DBMS::PrimaryKeys( '', 'JillSmith', 'my\_test');

SAS/ACCESS Interface to DB2 under z/OS supports these special queries.

DBMS::ForeignKeys ('PK-Catalog', 'PK-Schema', 'PK-TableName', 'FK-Catalog', 'FK-Schema', 'FK-TableName'

returns a list of all columns that comprise foreign keys that match the specified arguments. If no arguments are specified, all accessible foreign key columns and information are returned.

DBMS::Indexes ('Catalog', 'TableOwner', 'TableName')

returns the indexes that are specified in the catalog for a given user and table.

DBMS::PrimaryKeys ('Catalog', 'TableOwner', 'TableName')

returns a list of all columns that compose the primary key that matches the specified table. A primary key can be composed of one or more columns. If you do not specify any table name, then this special query fails.

DBMS::RowCount ('Catalog', 'TableOwner', 'TableName')

returns the number of rows for a specified table if statistics have been gathered for that table. Otherwise, this query returns a value of -1. The query also returns a value of -1 if the row describes a view, alias, accelerator-only table, or a temporary table.