DB2 10.5 for Linux, UNIX, and Windows

SET PASSTHRU statement

The SET PASSTHRU statement opens and closes a session for submitting a data source's native SQL directly to that data source.

The statement is not under transaction control.

Invocation

This statement can be issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must provide authorization to:

Pass through to the data source
Satisfy security measures at the data source

Syntax


>>-SET PASSTHRU--+-server-name-+-------------------------------><
                 '-RESET-------'

Description

server-name: Names the data source for which a pass-through session is to be opened. server-name must identify a data source that is described in the catalog.
RESET: Closes a pass-through session.

Notes

The following restrictions apply to Microsoft SQL Server, Sybase, and Oracle data sources:
- User-defined transactions cannot be used for Microsoft SQL Server and Sybase data sources in pass-through mode, because Microsoft SQL Server and Sybase restrict which SQL statements can be specified within a user-defined transaction. Because SQL statements that are processed in pass-through mode are not parsed by the database manager, it is not possible to detect whether the user specified an SQL statement that is permitted within a user-defined transaction.
- The COMPUTE clause is not supported on Microsoft SQL Server and Sybase data sources.
- DDL statements are not subject to transaction semantics on Microsoft SQL Server, Oracle and Sybase data sources. The operation, when complete, is automatically committed by Microsoft SQL Server, Oracle or Sybase. If a rollback occurs, the DDL is not rolled back.

Examples

Example 1: Start a pass-through session to data source BACKEND.

   strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
   EXEC SQL EXECUTE IMMEDIATE :PASS_THRU;

Example 2: Start a pass-through session with a PREPARE statement.

   strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
   EXEC SQL PREPARE STMT FROM :PASS_THRU;
   EXEC SQL EXECUTE STMT;

Example 3: End a pass-through session.

   strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
   EXEC SQL EXECUTE IMMEDIATE :PASS_THRU_RESET;

Example 4: Use the PREPARE and EXECUTE statements to end a pass-through session.

   strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
   EXEC SQL PREPARE STMT FROM :PASS_THRU_RESET;
   EXEC SQL EXECUTE STMT;

Example 5: Open a session to pass through to a data source, create a clustered index for a table at this data source, and close the pass-through session.

   strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
   EXEC SQL EXECUTE IMMEDIATE :PASS_THRU;
   EXEC SQL PREPARE STMT                 pass-through mode
     FROM "CREATE UNIQUE
           CLUSTERED INDEX TABLE_INDEX
           ON USER2.TABLE               table is not an
           WITH IGNORE DUP KEY";         alias
   EXEC SQL EXECUTE STMT;
   strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
   EXEC SQL EXECUTE IMMEDIATE :PASS_THRU_RESET;