SET CONNECTION statement

The SET CONNECTION statement changes the state of a connection from dormant to current, making the specified location the current server.

This statement is not under transaction control.

Invocation

Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramSET CONNECTION server-namehost-variable

Description

server-name or host-variable
Identifies the application server by the specified server-name or a host-variable which contains the server-name.

If a host-variable is specified, it must be a character string variable with a length attribute that is not greater than 8, and it must not include an indicator variable. The server-name that is contained within the host-variable must be left-aligned and must not be delimited by quotation marks.

Note that the server-name is a database alias identifying the application server. It must be listed in the application requester's local directory.

The server-name or the host-variable must identify an existing connection of the application process. If they do not identify an existing connection, an error (SQLSTATE 08003) is raised.

If SET CONNECTION is to the current connection, the states of all connections of the application process are unchanged.

Successful Connection
If the SET CONNECTION statement executes successfully:
  • No connection is made. The CURRENT SERVER special register is updated with the specified server-name.
  • The previously current connection, if any, is placed into the dormant state (assuming a different server-name is specified).
  • The CURRENT SERVER special register and the SQLCA are updated in the same way as documented under CONNECT (Type 1).
Unsuccessful Connection
If the SET CONNECTION statement fails:
  • No matter what the reason for failure, the connection state of the application process and the states of its connections are unchanged.
  • As with an unsuccessful Type 1 CONNECT, the SQLERRP field of the SQLCA is set to the name of the module that detected the error.

Notes

  • The use of type 1 CONNECT statements does not preclude the use of SET CONNECTION, but the statement will always fail (SQLSTATE 08003), unless the SET CONNECTION statement specifies the current connection, because dormant connections cannot exist.
  • The SQLRULES(DB2) connection option (see Options that Govern Distributed Unit of Work Semantics) does not preclude the use of SET CONNECTION, but the statement is unnecessary, because type 2 CONNECT statements can be used instead.
  • When a connection is used, made dormant, and then restored to the current state in the same unit of work, that connection reflects its last use by the application process with regard to the status of locks, cursors, and prepared statements.

Example

Execute SQL statements at IBMSTHDB, execute SQL statements at IBMTOKDB, and then execute more SQL statements at IBMSTHDB. 
   EXEC SQL CONNECT TO IBMSTHDB;
   /* Execute statements referencing objects at IBMSTHDB */
   EXEC SQL CONNECT TO IBMTOKDB;
   /* Execute statements referencing objects at IBMTOKDB */
   EXEC SQL SET CONNECTION IBMSTHDB;
   /* Execute statements referencing objects at IBMSTHDB */

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