DISCONNECT statement

The DISCONNECT statement destroys one or more connections when there is no active unit of work (that is, after a commit or rollback operation).

If a single connection is the target of the DISCONNECT statement, the connection is destroyed only if the database has participated in an existing unit of work, regardless of whether there is an active unit of work. For example, if several other databases have done work, but the target in question has not, it can still be disconnected without destroying the connection.

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.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramDISCONNECTserver-name1host-variableCURRENTALLSQL
Notes:
  • 1 Note that an application server named CURRENT or ALL can only be identified by a host variable.

Description

server-name or host-variable
Identifies the application server by the specified server-name or a host-variable which contains the server-name.

If a host-variable is specified, it must be a character string variable with a length attribute that is not greater than 8, and it must not include an indicator variable. The server-name that is contained within the host-variable must be left-aligned and must not be delimited by quotation marks.

Note that the server-name is a database alias identifying the application server. It must be listed in the application requester's local directory.

The specified database-alias or the database-alias contained in the host variable must identify an existing connection of the application process. If the database-alias does not identify an existing connection, an error (SQLSTATE 08003) is raised.

CURRENT
Identifies the current connection of the application process. The application process must be in the connected state. If not, an error (SQLSTATE 08003) is raised.
ALL
Indicates that all existing connections of the application process are to be destroyed. An error or warning does not occur if no connections exist when the statement is executed. The optional keyword SQL is included to be consistent with the syntax of the RELEASE statement.

Rules

  • Generally, the DISCONNECT statement cannot be executed while within a unit of work. If attempted, an error (SQLSTATE 25000) is raised. The exception to this rule is if a single connection is specified to be disconnected and the database has not participated in an existing unit of work. In this case, it does not matter if there is an active unit of work when the DISCONNECT statement is issued.
  • The DISCONNECT statement cannot be executed at all in the Transaction Processing (TP) Monitor environment (SQLSTATE 25000). It is used when the SYNCPOINT precompiler option is set to TWOPHASE.

Notes

  • If the DISCONNECT statement is successful, each identified connection is destroyed.

    If the DISCONNECT statement is unsuccessful, the connection state of the application process and the states of its connections are unchanged.

  • If DISCONNECT is used to destroy the current connection, the next executed SQL statement should be CONNECT or SET CONNECTION.
  • Type 1 CONNECT semantics do not preclude the use of DISCONNECT. However, though DISCONNECT CURRENT and DISCONNECT ALL can be used, they will not result in a commit operation like a CONNECT RESET statement would do.

    If server-name or host-variable is specified in the DISCONNECT statement, it must identify the current connection because Type 1 CONNECT only supports one connection at a time. Generally, DISCONNECT will fail if within a unit of work with the exception noted in "Rules".

  • Resources are required to create and maintain remote connections. Thus, a remote connection that is not going to be reused should be destroyed as soon as possible.
  • Connections can also be destroyed during a commit operation because the connection option is in effect. The connection option could be AUTOMATIC, CONDITIONAL, or EXPLICIT, which can be set as a precompiler option or through the SET CLIENT API at run time. For information about the specification of the DISCONNECT option, see Distributed relational databases.

Examples

  • Example 1:  The SQL connection to IBMSTHDB is no longer needed by the application. The following statement should be executed after a commit or rollback operation to destroy the connection. 
       EXEC SQL DISCONNECT IBMSTHDB;
  • Example 2:  The current connection is no longer needed by the application. The following statement should be executed after a commit or rollback operation to destroy the connection. 
       EXEC SQL DISCONNECT CURRENT;
  • Example 3:  The existing connections are no longer needed by the application. The following statement should be executed after a commit or rollback operation to destroy all the connections. 
       EXEC SQL DISCONNECT ALL;