SET CONNECTION

Invocation

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

None required.

Description

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 format of product identifier values is pppvvrrm, where ppp is a 3-letter product code (such as DSN for DB2®), vv is the version, rr is the release, and m is the modification level. For example, DSN10015 identifies DB2 10 in new-function mode, the value is ‘DSN10015'. The product code (ppp) is one of the following values:

  • AQT for IBM DB2 Analytics Accelerator for z/OS®
  • ARI for DB2 Server for VSE & VM
  • DSN for DB2 for z/OS
  • JCC for IBM Data Server Driver for JDBC and SQLJ
  • QSQ for DB2 for i
  • SQL for DB2 for Linux, UNIX, and Windows

  Modification (m) values have the following meanings:

  0 - 1

  Modification levels in conversion and enabling-new-function mode from DB2 Version 8 (CM8, CM8*, ENFM8, and ENFM8*)

  2 - 3

  Modification levels in conversion and enabling-new-function mode from DB2 9 (CM9, CM9*, ENFM9, and ENFM9*)

  4

  Not used.

  5 - 9

  Modification levels in new-function mode.

• 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

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.

Example

  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.