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.
See Application-Directed Distributed Unit of Work for concepts and additional details.
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
The invocation is the same as Invocation.
Authorization
The authorization is the same as Authorization.
Syntax
The syntax is the same as Syntax. The selection between Type 1 and Type 2 is determined by precompiler options. See Options that Govern Distributed Unit of Work Semantics for an overview of these options. Further details are provided in the Command Reference and Administrative API Reference manuals.
Description
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.
See Options that Govern Distributed Unit of Work Semantics for information about the specification of SQLRULES.
If the CONNECT TO statement is successful:
If the CONNECT TO statement is unsuccessful:
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 userid and password.
Availability of a default database is determined by installation options, environment variables, and authentication settings. See the Quick Beginnings for information on setting implicit connect on installation and environment variables, and the Administration Guide for information on authentication settings.
Rules
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 raised. 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 on page *** illustrates these rules.
Notes
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 below:
Use of CONNECT TO:
| 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 also implicitly commits the current unit of work. | CONNECT RESET does not commit 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. |
Examples
Example 1: This example illustrates the use of multiple source programs (shown in the boxes), some preprocessed with different connection options (shown above 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 1 exec sql CONNECT TO LONDON; exec sql SELECT col1 INTO :hv1 FROM tbl3; ...
1 Note: 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:
If the application executes PGM1 then PGM3:
This is OK because the SET CLIENT API is run before the second CONNECT statement.
If the application executes PGM1 then PGM4:
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 |