DB2 Version 10.1 for Linux, UNIX, and Windows

CONNECT (type 2) statement

The CONNECT (Type 2) statement connects an application process to the identified application server and establishes the rules for application-directed distributed unit of work. This server is then the current server for the process.

Most aspects of a CONNECT (Type 1) statement also apply to a CONNECT (Type 2) statement. Rather than repeating that material here, this section describes only those elements of Type 2 that differ from Type 1.

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. When invoked using the command line processor, additional options can be specified. For more information, refer to "Using command line SQL statements and XQuery statements".

Authorization

CONNECT processing goes through two levels of access control. Both levels must be satisfied for the connection to be successful.

The first level of access control is authentication, where the user ID associated with the connection must be successfully authenticated according to the authentication method set up for the server. At successful authentication, a DB2® authorization ID is derived from the connection user ID according to the authentication plug-in in effect for the server. This DB2 authorization ID must then pass the second level of access control for the connection, that is, authorization. To do so, this authorization ID must hold at least one of the following authorities:

CONNECT authority
SECADM authority
DBADM authority
SYSADM authority
SYSCTRL authority
SYSMAINT authority
SYSMON authority

Note: For a partitioned database, the user and group definitions must be identical across all database partitions.

Syntax

The selection between Type 1 and Type 2 is determined by precompiler options. For an overview of these options, see "Connecting to distributed relational databases".


>>-CONNECT------------------------------------------------------>

>--+------------------------------------------------------------------+-><
   +-TO--+-server-name---+--+----------------+--+-------------------+-+   
   |     '-host-variable-'  '-| lock-block |-'  '-| authorization |-' |   
   +-RESET------------------------------------------------------------+   
   |                   (1)                                            |   
   '-| authorization |------------------------------------------------'   

authorization

|--USER--+-authorization-name-+--USING--+-password------+------->
         '-host-variable------'         '-host-variable-'   

>--+-------------------------------------------+----------------|
   '-NEW--+-password------+--CONFIRM--password-'   
          '-host-variable-'                        

lock-block

   .-IN SHARE MODE---------------------------.   
|--+-----------------------------------------+------------------|
   '-IN EXCLUSIVE MODE--+------------------+-'   
                        '-ON SINGLE MEMBER-'

Notes:

This form is only valid if implicit connect is enabled.

Description

TO server-name/host-variable

The rules for coding the name of the server are the same as for Type 1.

If the SQLRULES(STD) option is in effect, the server-name must not identify an existing connection of the application process, otherwise an error (SQLSTATE 08002) is raised.

If the SQLRULES(DB2) option is in effect and the server-name identifies an existing connection of the application process, that connection is made current and the old connection is placed into the dormant state. That is, the effect of the CONNECT statement in this situation is the same as that of a SET CONNECTION statement.

For information about the specification of SQLRULES, see "Options that Govern Distributed Unit of Work Semantics".

Successful Connection

If the CONNECT statement is successful:

A connection to the application server is either created (or made non-dormant) and placed into the current and held states.
If the CONNECT TO is directed to a different server than the current server, then the current connection is placed into the dormant state.
The CURRENT SERVER special register and the SQLCA are updated in the same way as for CONNECT (Type 1).

Unsuccessful Connection

If the CONNECT statement is unsuccessful:

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 at the application requester or server that detected the error.

CONNECT (with no operand), IN SHARE/EXCLUSIVE MODE, USER, and USING

If a connection exists, Type 2 behaves like a Type 1. The authorization ID and database alias are placed in the SQLERRMC field of the SQLCA. If a connection does not exist, no attempt to make an implicit connection is made and the SQLERRP and SQLERRMC fields return a blank. (Applications can check if a current connection exists by checking these fields.)

A CONNECT with no operand that includes USER and USING can still connect an application process to a database using the DB2DBDFT environment variable. This method is equivalent to a Type 2 CONNECT RESET, but permits the use of a user ID and password.

RESET

Equivalent to an explicit connect to the default database if it is available. If a default database is not available, the connection state of the application process and the states of its connections are unchanged.

Availability of a default database is determined by installation options, environment variables, and authentication settings.

Rules

As outlined in "Options that Govern Distributed Unit of Work Semantics", a set of connection options governs the semantics of connection management. Default values are assigned to every preprocessed source file. An application can consist of multiple source files precompiled with different connection options.
Unless a SET CLIENT command or API has been executed first, the connection options used when preprocessing the source file containing the first SQL statement executed at run time become the effective connection options.

If a CONNECT statement from a source file preprocessed with different connection options is subsequently executed without the execution of any intervening SET CLIENT command or API, an error (SQLSTATE 08001) is returned. Note that once a SET CLIENT command or API has been executed, the connection options used when preprocessing all source files in the application are ignored.

Example 1 in the "Examples" section of this statement illustrates these rules.
Although the CONNECT statement can be used to establish or switch connections, CONNECT with the USER/USING clause will only be accepted when there is no current or dormant connection to the named server. The connection must be released before issuing a connection to the same server with the USER/USING clause, otherwise it will be rejected (SQLSTATE 51022). Release the connection by issuing a DISCONNECT statement or a RELEASE statement followed by a COMMIT statement.

Comparing Type 1 and Type 2 CONNECT Statements

The semantics of the CONNECT statement are determined by the CONNECT precompiler option or the SET CLIENT API (see "Options that Govern Distributed Unit of Work Semantics"). CONNECT Type 1 or CONNECT Type 2 can be specified and the CONNECT statements in those programs are known as Type 1 and Type 2 CONNECT statements, respectively. Their semantics are described in the following tables:

Use of CONNECT:

Type 1	Type 2
Each unit of work can only establish connection to one application server.	Each unit of work can establish connection to multiple application servers.
The current unit of work must be committed or rolled back before allowing a connection to another application server.	The current unit of work need not be committed or rolled back before connecting to another application server.
The CONNECT statement establishes the current connection. Subsequent SQL requests are forwarded to this connection until changed by another CONNECT.	Same as Type 1 CONNECT if establishing the first connection. If switching to a dormant connection and SQLRULES is set to STD, then the SET CONNECTION statement must be used instead.
Connecting to the current connection is valid and does not change the current connection.	Same as Type 1 CONNECT if the SQLRULES precompiler option is set to DB2. If SQLRULES is set to STD, then the SET CONNECTION statement must be used instead.
Connecting to another application server disconnects the current connection. The new connection becomes the current connection. Only one connection is maintained in a unit of work.	Connecting to another application server puts the current connection into the dormant state. The new connection becomes the current connection. Multiple connections can be maintained in a unit of work. If the CONNECT is for an application server on a dormant connection, it becomes the current connection. Connecting to a dormant connection using CONNECT is only allowed if SQLRULES(DB2) was specified. If SQLRULES(STD) was specified, then the SET CONNECTION statement must be used instead.
SET CONNECTION statement is supported for Type 1 connections, but the only valid target is the current connection.	SET CONNECTION statement is supported for Type 2 connections to change the state of a connection from dormant to current.

Use of CONNECT...USER...USING:

Type 1	Type 2
Connecting with the USER...USING clauses disconnects the current connection and establishes a new connection with the given authorization name and password.	Connecting with the USER/USING clause will only be accepted when there is no current or dormant connection to the same named server.

Use of Implicit CONNECT, CONNECT RESET, and Disconnecting:

Type 1	Type 2
CONNECT RESET can be used to disconnect the current connection.	CONNECT RESET is equivalent to connecting to the default application server explicitly if one has been defined in the system. Connections can be disconnected by the application at a successful COMMIT. Prior to the commit, use the RELEASE statement to mark a connection as release-pending. All such connections will be disconnected at the next COMMIT. An alternative is to use the precompiler options DISCONNECT(EXPLICIT), DISCONNECT(CONDITIONAL), DISCONNECT(AUTOMATIC), or the DISCONNECT statement instead of the RELEASE statement.
After using CONNECT RESET to disconnect the current connection, if the next SQL statement is not a CONNECT statement, then it will perform an implicit connect to the default application server if one has been defined in the system.	CONNECT RESET is equivalent to an explicit connect to the default application server if one has been defined in the system.
It is an error to issue consecutive CONNECT RESETs.	It is an error to issue consecutive CONNECT RESETs ONLY if SQLRULES(STD) was specified because this option disallows the use of CONNECT to existing connection.
CONNECT RESET implicitly rolls back the current unit of work.	CONNECT RESET implicitly rolls back the current unit of work.
If an existing connection is disconnected by the system for whatever reasons, then subsequent non-CONNECT SQL statements to this database will receive an SQLSTATE of 08003.	If an existing connection is disconnected by the system, COMMIT, ROLLBACK, and SET CONNECTION statements are still permitted.
The unit of work will be implicitly committed when the application process terminates successfully.	Same as Type 1.
All connections (only one) are disconnected when the application process terminates.	All connections (current, dormant, and those marked for release pending) are disconnected when the application process terminates.

CONNECT Failures:

Type 1	Type 2
Regardless of whether there is a current connection when a CONNECT fails (with an error other than server-name not defined in the local directory), the application process is placed in the unconnected state. Subsequent non-CONNECT statements receive an SQLSTATE of 08003.	If there is a current connection when a CONNECT fails, the current connection is unaffected. If there was no current connection when the CONNECT fails, then the program is then in an unconnected state. Subsequent non-CONNECT statements receive an SQLSTATE of 08003.

Notes

Implicit connect is supported for the first SQL statement in an application with Type 2 connections. In order to execute SQL statements on the default database, first the CONNECT RESET or the CONNECT USER/USING statement must be used to establish the connection. The CONNECT statement with no operands will display information about the current connection if there is one, but will not connect to the default database if there is no current connection.
The authorization-name SYSTEM cannot be explicitly specified in the CONNECT statement. However, on Windows operating systems, local applications running under the Local System Account can implicitly connect to the database, such that the user ID is SYSTEM.
When connecting to Windows Server explicitly, the authorization-name or user host-variable can be specified using the Microsoft Windows Security Account Manager (SAM)-compatible name.
Termination of a connection: When a connection is terminated and a transaction has not yet been committed or rolled back, see "Use of Implicit CONNECT, CONNECT RESET, and Disconnecting" section for details on what happens to such transactions. To ensure consistent behavior, code an explicit COMMIT statement or ROLLBACK statement instead of depending on the behavior of the CONNECT statement.
Syntax alternatives: The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
- DBPARTITIONNUM or NODE can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.

Examples

Example 1: This example illustrates the use of multiple source programs (shown in the boxes), some preprocessed with different connection options (shown in the statement preceding the code), and one of which contains a SET CLIENT API call.
PGM1: CONNECT(2) SQLRULES(DB2) DISCONNECT(CONDITIONAL)
```
   ...
   exec sql CONNECT TO OTTAWA;
   exec sql SELECT col1 INTO :hv1
   FROM tbl1;
   ...
```
PGM2: CONNECT(2) SQLRULES(STD) DISCONNECT(AUTOMATIC)
```
   ...
   exec sql CONNECT TO QUEBEC;
   exec sql SELECT col1 INTO :hv1
   FROM tbl2;
   ...
```
PGM3: CONNECT(2) SQLRULES(STD) DISCONNECT(EXPLICIT)
```
   ...
   SET CLIENT CONNECT 2  SQLRULES DB2  DISCONNECT EXPLICIT ¹
   exec sql CONNECT TO LONDON;
   exec sql SELECT col1 INTO :hv1 
   FROM tbl3;
   ...
```
Note:
1. Not the actual syntax of the SET CLIENT API
PGM4: CONNECT(2) SQLRULES(DB2) DISCONNECT(CONDITIONAL)
```
   ...
   exec sql CONNECT TO REGINA;
   exec sql SELECT col1 INTO :hv1 
   FROM tbl4;
   ...
```
If the application executes PGM1 then PGM2:
- connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
- connect to QUEBEC fails with SQLSTATE 08001 because both SQLRULES and DISCONNECT are different.
If the application executes PGM1 then PGM3:
- connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
- connect to LONDON runs: connect=2, sqlrules=DB2, disconnect=EXPLICIT
This is OK because the SET CLIENT API is run before the second CONNECT statement.
If the application executes PGM1 then PGM4:
- connect to OTTAWA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
- connect to REGINA runs: connect=2, sqlrules=DB2, disconnect=CONDITIONAL
This is OK because the preprocessor options for PGM1 are the same as those for PGM4.

Example 2: This example shows the interrelationships of the CONNECT (Type 2), SET CONNECTION, RELEASE, and DISCONNECT statements. S0, S1, S2, and S3 represent four servers.

Sequence	Statement	Current Server	Dormant Connections	Release Pending
0	No statement	None	None	None
1	SELECT * FROM TBLA	S0 (default)	None	None
2	CONNECT TO S1 SELECT * FROM TBLB	S1 S1	S0 S0	None None
3	CONNECT TO S2 UPDATE TBLC SET ...	S2 S2	S0, S1 S0, S1	None None
4	CONNECT TO S3 SELECT * FROM TBLD	S3 S3	S0, S1, S2 S0, S1, S2	None None
5	SET CONNECTION S2	S2	S0, S1, S3	None
6	RELEASE S3	S2	S0, S1	S3
7	COMMIT	S2	S0, S1	None
8	SELECT * FROM TBLE	S2	S0, S1	None
9	DISCONNECT S1 SELECT * FROM TBLF	S2 S2	S0 S0	None None