SET CONNECTION

The SET CONNECTION statement establishes the current server of the activation group by identifying one of its existing connections.

Invocation

This statement can only be embedded within an application program or issued interactively. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™ or REXX.

SET CONNECTION is not allowed in a trigger. SET CONNECTION is not allowed in an external procedure if the external procedure is called on a remote application server.

Authorization

Start of change If a global variable is referenced in a statement, the privileges held by the authorization ID of the statement must include at least one of the following:End of change

Start of changeEnd of change

Syntax

Read syntax diagramSkip visual syntax diagram
>>-SET CONNECTION--+-server-name-+-----------------------------><
                   '-variable----'   

Description

server-name or variable
Identifies the connection by the specified server name or the server name contained in the variable. Start of changeIt can be a global variable if it is qualified with schema name.End of change If a variable is specified:
  • It must be a character-string variable with a length attribute that is not greater than 18.
  • It must not be followed by an indicator variable.
  • The server name must be left-justified within the variable and must conform to the rules for forming an ordinary identifier.
  • If the length of the server name is less than the length of the variable, it must be padded on the right with blanks.

Let S denote the specified server name or the server name contained in the variable. S must identify an existing connection of the application process. If S identifies the current connection, the state of S and all other connections of the application process are unchanged, but information about S is placed in the SQLERRP field of the SQLCA. The following rules apply when S identifies a dormant connection.

If the SET CONNECTION statement is successful:

If the SET CONNECTION statement is unsuccessful, the connection state of the activation group and the states of its connections are unchanged.

Notes

SET CONNECTION for CONNECT (Type 1): The use of CONNECT (Type 1) statements does not prevent the use of SET CONNECTION, but the statement either fails or does nothing because dormant connections do not exist.

Status after connection is restored: When a connection is used, made dormant, and then restored to the current state in the same unit of work, the status of locks, cursors, and prepared statements for that connection reflects its last use by the activation group.

Local connections: A SET CONNECTION to a local connection will fail if the current independent auxiliary Storage pool (IASP) name space does not match the local connection's relational database.

Example

Execute SQL statements at TOROLAB1, execute SQL statements at TOROLAB2, and then execute more SQL statements at TOROLAB1.

   EXEC SQL CONNECT TO TOROLAB1;

   (Execute statements referencing objects at TOROLAB1)

   EXEC SQL CONNECT TO TOROLAB2;  

   (Execute statements referencing objects at TOROLAB2)

   EXEC SQL SET CONNECTION TOROLAB1;

   (Execute statements referencing objects at TOROLAB1)

The first CONNECT statement creates the TOROLAB1 connection, the second CONNECT statement places it in the dormant state, and the SET CONNECTION statement returns it to the current state.