Federated pass-through considerations and restrictions

This topic explains the considerations and restrictions you need to be aware of when you use a pass-through session.

The following considerations and restrictions apply 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 and result in a SQLSTATE 56098 error. Statements prepared outside of a pass-through session, but executed within a pass-through session, are handled as a SET PASSTHRU statement.
  • An application can issue multiple SET PASSTHRU statements, however only the last session is active. When a new SET PASSTHRU statement is invoked, it terminates the previous SET PASSTHRU statement. You cannot pass through to more than one data source in the same pass-through session.
  • If multiple pass-through sessions are used in an application, be sure to issue a COMMIT before you open another pass-through session. This will conclude the unit of work for the current session.
  • Parameter markers are not supported in pass-through sessions. Use host variables instead of parameter markers.
  • You can use the WITH HOLD semantics on a cursor defined in a pass-through session. However, you might receive an error if you try to use the WITH HOLD semantics following a COMMIT and the data source does not support the WITH HOLD semantics.
  • 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.
  • Pass-through does not support LOBs.
  • Pass-through does not support stored procedure calls.
  • Pass-through does not support the SELECT INTO statement.
  • Pass-through does not support SQL or external user-defined functions.
  • You cannot execute dynamic SQL COMMIT or ROLLBACK statements during a pass-through session.
  • When performing update or delete operations during a pass-through session, you cannot use the WHERE CURRENT OF CURSOR condition.
  • In pass-through mode, dynamic SQL statements are executed remotely, whereas static SQL statements are sent to the federated server for processing. If you want to submit an SQL statement to a data source for processing, you must prepare it dynamically in the pass-through session and it must be executed while the session is still open.
  • Static SET PASSTHRU statements in SQL-bodied stored procedures are blocked when stored procedures are created, and error SQL0104N is issued. To get into and out of pass-through mode, use the EXECUTE IMMEDIATE statement.
    Example:
    create procedure stp()
dynamic result sets 1
language sql
modifies sql data
begin
declare stmt varchar(100);

DECLARE cur1 CURSOR WITH RETURN TO CALLER
FOR SELECT * FROM t1 ;

set stmt = 'set passthru mvs7';
execute immediate stmt;

set stmt = 'insert into t1 values (20, ''passthru_insert'')';
execute immediate stmt;
commit;

insert into t1 values (20, 'stp_insert');
commit;

OPEN cur1;

end
DB20000I The SQL command completed successfully.

call stp()


Result set 1
-------------------------------
10 local
20 stp_insert

2 record(s) selected.

Return Status = 0


select * from t1

C1 C2
-------------------------------
10 remote
20 passthru_insert

2 record(s) selected.


set passthru reset
DB20000I The SQL command completed successfully.

select * from t1

C1 C2
-------------------------------
10 local
20 stp_insert

2 record(s) selected.
  • The return from a stored procedure or compound SQL statement does not terminate pass-through mode automatically. To exit from pass-through mode, the SET PASSTHRU RESET statement must be called explicitly, as shown in the example above.