SQL Reference

Pass-Through Facility Processing

A facility called pass-through can be used 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 using 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:

• In a pass-through session, a statement is sent to the data source for processing if the statement is dynamically prepared and executed during the session through either (1) an EXECUTE IMMEDIATE statement or (2) a PREPARE statement and an EXECUTE statement.

• If a static statement is submitted in a pass-through session, it is sent to the federated server for processing.

Considerations and Restrictions

There are a number of considerations and restrictions that apply to pass-through. Some of them are of a general nature; others concern Oracle data sources only.

Using Pass-Through with All Data Sources

The following information applies to all data sources:

• If a PREPARE statement is used to dynamically prepare a statement in a pass-through session, the latter statement cannot be processed unless it is referenced in an EXECUTE statement in the same session.

• If a COMMIT or ROLLBACK command is issued during a pass-through session, this command will complete the current unit of work (UOW).

• Users and applications can use pass-through to write to data sources; for example, to insert, update, and delete table rows. Note that a cursor cannot be opened directly against a data source object in a pass-through session (SQLSTATE 25000).

• 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, each unit of work within each session must be concluded with a COMMIT or ROLLBACK statement. The sessions can then be ended in one operation with the SET PASSTHRU statement and its RESET option.

• It is not possible 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 SQLSTATE 25000. 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 a SELECT statement is issued from raw data types, the RAWTOHEX function should be invoked to receive the hexadecimal values. When an INSERT into raw data types is performed, the hexadecimal representation should be provided.

[ DB2 List of Books | Search the DB2 Books ]