SET CONNECTION statement

Invocation for SET CONNECTION

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

Authorization for SET CONNECTION

None required.

Description for SET CONNECTION

location-name or host-variable

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

• It must be a character string variable with a length attribute that is not greater than 16. (A C NUL-terminated character string can be up to 17 bytes.)
• It must not be followed by an indicator variable.
• The location name must be left-justified within the host variable and must conform to the rules for forming an ordinary location identifier.
• If the length of the location name is less than the length of the host variable, it must be padded on the right with blanks.

Let S denote the specified location name or the location name contained in the host variable. S must identify an existing SQL connection of the application process. If S identifies the current SQL connection, the state of S and all other connections of the application process are unchanged. The following rules apply when S identifies a dormant SQL connection.

If the SET CONNECTION statement is successful:

• SQL connection S is placed in the current state.
• S is placed in the CURRENT SERVER special register.
• Information about server S is placed in the SQLERRP field of the SQLCA. If the server is an IBM® product, the information has the form pppvvrrm.
  The product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. For Db2 13 for z/OS®, the modification level (0–9 or A–Z) indicates a specific function level. For example:

  • DSN13012 for V13R1M501.
  • DSN13011 for V13R1M500.
  • DSN13010 for V13R1M100.

  For more information, see Product identifier (PRDID) values in Db2 for z/OS.
• Any previously current SQL connection is placed in the dormant state.

If the SET CONNECTION statement is unsuccessful, the connection state of the application process and the states of its SQL connections are unchanged.

Notes for SET CONNECTION

SET CONNECTION after 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 SQL connections do not exist. The SQLRULES(DB2) bind option does not prevent the use of SET CONNECTION, but the statement is unnecessary because CONNECT (Type 2) statements can be used instead. Use the SET CONNECTION statement to conform to the SQL standard.

Status of locks, cursors, and prepared statements: When an SQL 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 SQL connection reflects its last use by the application process.

Host variables: If the SET CONNECTION statement contains host variables, the contents of the host variables are assumed to be in the encoding scheme that was specified in the ENCODING parameter when the package or plan that contains the statement was bound.

Restrictions on array types and array variables: In any SQL statement other than a CALL statement, array types and array variables must not be referenced after a connection at a remote server has been established. This restriction includes an SQL statement that executes at a remote server as a result of a three-part name or alias that resolves to an object at a remote server. An exception is that an array element can be the target of a FETCH, SELECT INTO, SET assignment-statement, or VALUES INTO statement in an SQL routine even when the statement is executed at a remote server.

Example for SET CONNECTION

  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.