Passthrough mode limitations

  • Passthrough works on only one level of a hierarchy   In a multi-tier SQL Remote system, it is important that passthrough statements work immediately below the current level. In a multi-tier system, passthrough statements must be entered at each consolidated database, for the level beneath it.

  • Calling procedures   When a stored procedure is called in passthrough mode using a CALL or EXEC statement:

    • The procedure must exist in the consolidated database that calls the passthrough command, even if the procedure is not executed on the consolidated database. See PASSTHROUGH statement [SQL Remote].

    • The procedure must also exist on the remote database. The CALL or EXEC statement is replicated, but none of the statements inside the procedure is replicated. It is assumed that the procedure on the replicated database has the correct effect.

  • Control statements   Control statements such as IF and LOOP and any cursor operations, are not replicated in passthrough mode. Any statements within the loop or control structure are replicated. See Control statements.

  • Cursor operations   Operations on cursors are not replicated.

  • SQL SET OPTION statements   Static embedded SQL SET OPTION statements are not replicated. However, dynamic SQL statements are replicated. See Static and dynamic SQL.

    For example, the following statement is not replicated in passthrough mode:

    EXEC SQL SET OPTION ...

    However, the following dynamic SQL statement is replicated:

    EXEC SQL EXECUTE IMMEDIATE "SET OPTION ... "

  • Batch statements   Batch statements (a group of statements surrounded with a BEGIN and END) are not replicated in passthrough mode. If you try to use batch statements in passthrough mode, an error occurs.

 See also