SQL Reference

SET PASSTHRU

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

Refer to Pass-Through Facility Processing for guidelines and restrictions on using pass-through.

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;