Application Development Guide

Using Pass-Through to Query Data Sources Directly

You can use a facility called pass-through to query a data source in the SQL that is native to that data source. This section:

• States what kind of SQL statements a federated server and its associated data sources process in pass-through sessions.

• Lists considerations and restrictions to be aware of when you use pass-through.

SQL Processing in Pass-Through Sessions

The following rules specify whether an SQL statement is processed by DB2 or by a data source:

• DB2 processes all static SQL statements that are submitted in a pass-through session.

• The data source processes dynamic SQL statements that undergo a PREPARE within a pass-through session.

• DB2 processes dynamic SQL statements that do not undergo a PREPARE within a pass-through session.

• If you issue the COMMIT or ROLLBACK command during a pass-through session, this command will complete the current unit of work (UOW). For example, in the following UOW, a nickname is created and then a pass-through session is opened to retrieve a table's data. A COMMIT is entered during this session, but it applies to the entire UOW. (In this example, the name of the federated database is DJDB and the name of the table for which a nickname is being created is T1.)

  CONNECT TO DJDB
  CREATE NICKNAME NICKY FOR T1
  SET PASSTHRU DJDB
  SELECT * FROM T2
  COMMIT
  SET PASSTHRU RESET
  

Considerations and Restrictions

There are a number of considerations and restrictions to bear in mind when you use pass-through. Some of them are of a general nature; others apply to Oracle data sources only.

Using Pass-Through with All Data Sources

The following information applies to all data sources:

• You can use pass-through to write to data sources; for example, to insert, update, and delete table rows. But note that you cannot use WHERE CURRENT OF conditions in UPDATE and DELETE statements within a pass-through session.

• An application can have several SET PASSTHRU statements in effect at the same time to different data sources. Although the application might have issued multiple SET PASSTHRU statements, the pass-through sessions are not truly nested. The federated server will not pass through one data source to access another. Rather, the server accesses each data source directly.

• If multiple pass-through sessions are open at the same time, be sure to issue a COMMIT each time you want to conclude a unit of work in each session. Then, when you need to terminate the sessions, you can do so with a single SET PASSTHRU RESET statement.

• Host variables defined in SQL statements within a pass-through session must take the form :Hn where H is uppercase and n is a unique whole number. The values of n must be numbered consecutively beginning with zero.

• You cannot pass through to more than one data source at a time.

• Pass-through does not support stored procedure calls.

Using Pass-Through with Oracle Data Sources

The following information applies to Oracle data sources:

• The following restriction applies when a remote client issues a SELECT statement from a command line processor (CLP) in pass-through mode: If the client code is a DB2 SDK prior to DB2 Universal Database Version 5, the SELECT will elicit an SQLCODE -30090 with reason code 11. To avoid this error, remote clients must use a DB2 SDK that is at Version 5 or greater.

• Any DDL statement issued against an Oracle server is performed at parse time and is not subject to transaction semantics. The operation, when complete, is automatically committed by Oracle. If a rollback occurs, the DDL is not rolled back.

• When you issue a SELECT statement from raw data types, use the RAWTOHEX function to receive the hexadecimal values. When you perform an INSERT into raw data types, provide the hexadecimal representation.

[ DB2 List of Books | Search the DB2 Books ]