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:

• If a static statement is submitted in a pass-through session, it is sent to the federated server for processing.
• If, in a pass-through session, you want to submit an SQL statement to a data source for processing, you must prepare it dynamically in the session and have it executed while the session is still open.
  • If you are submitting a SELECT statement, prepare it with the PREPARE statement, and then use the OPEN, FETCH, and CLOSE statements to access the results of your query.
  • For a supported statement other than SELECT, you have two options:
    • Use the PREPARE statement to prepare the supported statement, and use the EXECUTE statement to execute it.
    • Use the EXECUTE IMMEDIATE statement to prepare and execute the statement.
• If you issue the COMMIT or ROLLBACK command 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 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:

• 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).
• 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.
• 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 Application Development Client 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 Application Development Client 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.