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

Read syntax diagramSkip visual syntax diagramSET PASSTHRUserver-nameRESET

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;