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
>>-SET CONNECTION--+-server-name---+---------------------------><
'-host-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.