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:

• If a SQL statement is submitted to a data source for processing in a pass-through session, it must be prepared dynamically in the session and executed while the session is still open. There are several ways to do this:
  • If a SELECT statement is submitted, use the PREPARE statement to prepare it, then use the OPEN, FETCH, and CLOSE statements to access the results of the query.
  • For supported statements other than SELECT, either:
    • Use the PREPARE statement to prepare the supported statement; then use the EXECUTE statement to have it executed.
    • Use the EXECUTE IMMEDIATE statement to prepare it and have it executed.
• If a static statement is submitted in a pass-through session, it is sent to the federated server for processing.
• If a COMMIT or ROLLBACK command is issued during a pass-through session, this command will complete the current unit of work (UOW).

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:

• Statements prepared within a pass-through session must be executed within the same pass-through session. Statements prepared within a pass-through session, but executed outside of the same pass-through session will fail (SQLSTATE 56098).
• 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.
• Pass-through does not support the SELECT INTO statement.

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.