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.
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.
- 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.
- 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.
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.
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.