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

SET CONNECTION is not allowed in a trigger program.

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

Authorization

None required.

Syntax

>>-SET CONNECTION----+-server-name---+-------------------------><
                     '-host-variable-'
 

Description

server-name  or  host-variable

Identifies the connection by the specified server name or the server name contained in the host variable. If a host variable is specified:

• It must be a character-string variable.
• It must not be followed by an indicator variable.
• The server name must be left-justified within the host 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 host variable, it must be padded on the right with blanks.

Let S denote the specified server name or the server name contained in the host 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:

• Connection S is placed in the current state.
• S is placed in the CURRENT SERVER special register.
• Information about application server S is placed in the SQLERRP field of the SQLCA. If the application server is an IBM relational database product, the information has the form pppvvrrm, where:
  • ppp identifies the product as follows:

    ARI for DB2 for VSE and VM

    DSN for DB2 UDB for OS/390

    QSQ for DB2 UDB for AS/400

    SQL for all other DB2 products

  • vv is a two-digit version identifier such as '04'
  • rr is a two-digit release identifier such as '01'
  • m is a one-digit modification level such as '0'

  For example, if the application server is Version 4 of DB2 UDB for OS/390, the value of SQLERRP is 'DSN04010'.

• Additional information about the connection is placed in the SQLERRD(4) field of the SQLCA. SQLERRD(4) will contain values indicating whether the application server allows commitable updates to be performed. Following is a list of values and their meanings for the SQLERRD(4) field of the SQLCA on the CONNECT :
  • 1 - commitable updates can be performed and either the connection uses an unprotected conversation, is a connection established to an application requester driver program using a CONNECT (Type 1) statement, or is a local connection established using a CONNECT (Type 1) statement.
  • 2 - No commitable updates can be performed; conversation is unprotected.
  • 3 - It is unknown if commitable updates can be performed; conversation is protected.
  • 4 - It is unknown if commitable updates can be performed; conversation is unprotected.
  • 5 - It is unknown if commitable updates can be performed and the connection is either a local connection established using a CONNECT (Type 2) statement or a connection to an application requester driver program established using a CONNECT (Type 2) statement.
• Additional information about the connection is placed in the SQLERRMC field of the SQLCA. Refer to Appendix B, "SQL Communication Area" for a description of the information in the SQLERRMC field.
• Any previously current connection is placed in the dormant state.

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

Notes

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.

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.