Determining the connection status

A CONNECT statement without parameters can be used to determine whether the current connection is updatable or read-only for the current unit of work.

A value of 1 or 2 is returned in SQLERRD(3) in the SQLCA or DB2_CONNECTION_STATUS in the SQL diagnostic area. The value is determined as follows:

  1. Committable updates can be performed on the connection for the unit of work.
    This will occur when one of the following is true:
    • The connection is established using remote unit of work (RUW).
    • If the connection is established using distributed unit of work (DUW) and all of the following are true:
      • No connection exists to an application server that does not support distributed unit of work which can perform committable updates.
      • One of the following is true:
        • The first committable update is performed on a connection that uses a protected connection, is performed on the local database, or is performed on a connection to an ARD program.
        • There are open updatable local files under commitment control. .
        • There are open updatable DDM files that use protected connections.
        • There are two-phase API commitment control resources.
        • No committable updates have been made.
    • If the connection is established using distributed unit of work (DUW) and all of the following are true:
      • No other connections exist to an application server that does not support distributed unit of work which can perform committable updates.
      • The first committable update is performed on this connection or no committable updates have been made.
      • There are no open updatable DDM files that use protected connections.
      • There are no open updatable local files under commitment control.
      • There are no two-phase API commitment control resources.
  2. No committable updates can be performed on the connection for this unit of work.
    This will occur when one of the following is true:
    • If the connection is established using distributed unit of work (DUW) and one of the following are true:
      • A connection exists to an updatable application server that only supports remote unit of work.
      • The first committable update is performed on a connection that uses an unprotected connection.
    • If the connection is established using distributed unit of work (DUW) and one of the following are true:
      • A connection exists to an updatable application server that only supports remote unit of work.
      • The first committable update was not performed on this connection.
      • There are open updatable DDM files that use protected connections.
      • There are open updatable local files under commitment control.
      • There are two-phase API commitment control resources.

The following table summarizes how the connection status is determined based on the connection type value, if there is an updatable connection to an application server that only supports remote unit of work, and where the first committable update occurred.

Table 1. Summary of determining connection status values
Connection method Connection exists to updatable remote unit of work application server Where first committable update occurred 1 SQLERRD(3) or DB2_CONNECTION_STATUS
RUW     1
DUW Yes   2
DUW No no updates 1
DUW No one-phase 2
DUW No this connection 1
DUW No two-phase 1
1The terms in this column are defined as:
  • No updates indicates no committable updates have been performed, no DDM files open for update using a protected connection, no local files are open for update, and no commitment control APIs are registered.
  • One-phase indicates the first committable update was performed using an unprotected connection or DDM files are open for update using unprotected connections.
  • Two-phase indicates a committable update was performed on a two-phase distributed-unit-of-work application server, DDM files are open for update using a protected connection, commitment control APIs are registered, or local files are open for update under commitment control.

If an attempt is made to perform a committable update over a read-only connection, the unit of work will be placed in a rollback required state. If an unit of work is in a rollback required state, the only statement allowed is a ROLLBACK statement; all other statements will result in SQLCODE -918.