SQL Reference

Application-Directed Distributed Unit of Work

The application-directed distributed unit of work facility also provides for the remote preparation and execution of SQL statements in the same fashion as remote unit of work. An application process at computer system A can connect to an application server at computer system B by issuing a CONNECT or SET CONNECTION statement. The application process can then execute any number of static and dynamic SQL statements that reference objects at B before ending the unit of work. All objects referenced in a single SQL statement must be managed by the same application server. However, unlike remote unit of work, any number of application servers can participate in the same unit of work. A commit or rollback operation ends the unit of work.

Application-Directed Distributed Unit of Work Connection Management

An application-directed distributed unit of work uses a Type 2 connection. A Type 2 connection connects an application process to the identified application server and establishes the rules for application-directed distributed unit of work.

Overview of Application Process and Connection States

At any time a type 2 application process:

• Is always connectable

• Is in the connected state or unconnected state.

• Has a set of zero or more connections.

Each connection of an application process is uniquely identified by the database alias of the application server of the connection.

At any time an individual connection has one of the following sets of connection states:

• current and held
• current and release-pending
• dormant and held
• dormant and release-pending

Initial States and State Transitions

A type 2 application process is initially in the unconnected state and does not have any connections.

A connection initially is in the current and held state.

The following diagram shows the state transitions:

Figure 6. Application-Directed Distributed Unit of Work Connection and Application Process Connection State Transitions

Application Process Connection States

A different application server can be established by the explicit or implicit execution of a CONNECT statement. (8) The following rules apply:

• An application process cannot have more than one connection to the same application server at the same time. Note that DB2 CLI does not have this restriction. (DB2 CLI has its own connection type. DB2 CLI also can support a Type 2 connection as discussed, in which case this restriction does apply.) See Administration Guide for information on support of multiple connections to the same DB2 Universal Database at the same time.

• When an application process executes a SET CONNECTION statement, the specified location name must be an existing connection in the set of connections of the application process.

• When an application process executes a CONNECT statement, and the SQLRULES(STD) option is in effect the specified server name must not be an existing connection in the set of connections of the application process. See Options that Govern Distributed Unit of Work Semantics for a description of the SQLRULES option.

If an application process has a current connection, the application process is in the connected state. The CURRENT SERVER special register contains the name of the application server of the current connection. The application process can execute SQL statements that refer to objects managed by that application server.

An application process in the unconnected state enters the connected state when it successfully executes a CONNECT or SET CONNECTION statement. If there is no connection in the application but SQL statements are issued, an implicit connect will be made provided the DB2DBDFT environment variable has been defined with a default database.

If an application process does not have a current connection, the application process is in the unconnected state. The only SQL statements that can be executed are CONNECT, DISCONNECT ALL, DISCONNECT specifying a database, SET CONNECTION, RELEASE, COMMIT and ROLLBACK.

An application process in the connected state enters the unconnected state when its current connection is intentionally ended or the execution of an SQL statement is unsuccessful because of a failure that causes a rollback operation at the application server and loss of the connection. Connections are intentionally ended either by the successful execution of a DISCONNECT statement or by the successful execution of a commit operation when the connection is in the release-pending state. Different options specified in the DISCONNECT precompiler option affect intentionally ending a connection. If set to AUTOMATIC, then all connections are ended. If set to CONDITIONAL, then all connections that do not have open WITH HOLD cursors are ended.

States of a Connection

If an application process executes a CONNECT statement and the server name is known to the application requester and is not in the set of existing connections of the application process, then:

• the current connection is placed into the dormant state, and

• the server name is added to the set of connections, and

• the new connection is placed into both the current state and the held state.

If the server name is already in the set of existing connections of the application process and the application is precompiled with the option SQLRULES(STD), an error (SQLSTATE 08002) is raised.

• Held and Release-pending States: The RELEASE statement controls whether a connection is in the held or release-pending state. A release-pending state means that a disconnect is to occur for the connection at the next successful commit operation (a rollback has no effect on connections). A held state means that a connection is not to be disconnected at the next operation. All connections are initially in the held state and may be moved into the release-pending state using the RELEASE statement. Once in the release-pending state, a connection cannot be moved back to the held state. A connection will remain in a release-pending state across unit of work boundaries if a ROLLBACK statement is issued or if an unsuccessful commit operation results in a rollback operation.

  Even if a connection is not explicitly marked for release, it may still be disconnected by a commit operation if the commit operation satisfies the conditions of the DISCONNECT precompiler option.

• Current and Dormant States: Regardless of whether a connection is in the held state or the release-pending state, a connection can also be in the current state or dormant state. A current state means that the connection is the one used for SQL statements that are executed while in this state. A dormant state means that the connection is not current. The only SQL statements which can flow on a dormant connection are COMMIT and ROLLBACK; or DISCONNECT and RELEASE, which can specify either ALL (for all connections) or a specific database name. The SET CONNECTION and CONNECT statements change the connection for the named server into the current state while any existing connections are either placed or remain in the dormant state. At any point in time, only one connection can be in the current state. When a dormant connection becomes current in the same unit of work, the state of all locks, cursors, and prepared statements will remain the same and reflect their last use when the connection was current.

When a Connection is Ended

When a connection is ended, all resources that were acquired by the application process through the connection and all resources that were used to create and maintain the connection are deallocated. For example, if the application process executes a RELEASE statement, any open cursors will be closed when the connection is ended during the next commit operation.

A connection can also be ended because of a communications failure. The application process is placed in the unconnected state if the connection ended was the current one.

All connections of an application process are ended when the process ends.

(8) Note that a Type 2 implicit connection is more restrictive than a Type 1. See CONNECT (Type 2) for details.

[ DB2 List of Books | Search the DB2 Books ]