DB2 10.5 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 database authorization ID is derived from the connection user ID according to the authentication plug-in in effect for the server. This database 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".

Read syntax diagramSkip visual syntax diagram
>>-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:
  1. 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

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

Examples