Determining the connection type
When a remote SQL connection is established, it uses either an unprotected or a protected network connection.
With regards to committable updates, this SQL connection may be read-only, updatable, or unknown whether it is updatable when the connection is established. A committable update is any insert, delete, update, or DDL statement that is run under commitment control. If the connection is read-only, changes using COMMIT(*NONE) can still be run. After a CONNECT or SET CONNECTION, SQLERRD(4) of the SQLCA and DB2_CONNECTION_TYPE of the SQL diagnostic area indicate the type of connection.
DB2_CONNECTION_TYPE specific values are:
- The connection is to the local relational database and the connection is protected.
- The connection is to a remote relational database and the connection is unprotected.
- The connection is to a remote relational database and the connection is protected.
- The connection is to an application requester driver program and the connection is protected.
SQLERRD(4) specific values are:
- The connection is to a remote relational database and the connection is unprotected. Committable updates can be performed on the connection. This will occur when any of the following are true:
- The connection is established using remote unit of work (RUW).
- If the connection is established using distributed unit of work (DUW) then all the following are true:
If running with commitment control, SQL will register a one-phase updatable DRDA® resource for remote connections or a two-phase updatable DRDA resource for local and ARD connections.
- The connection is not local.
- The application server does not support distributed unit of work. For example, a DB2® UDB for iSeries™ application server with OS/400® V3R1 or earlier.
- The commitment control level of the program issuing the connect is not *NONE.
- Either no connections to other application servers (including local) exist that can perform committable updates or all connections are read-only connections to application servers that do not support distributed unit of work.
- There are no open updatable local files under commitment control for the commitment definition.
- There are no open updatable DDM files that use a different connection under commitment control for the commitment definition.
- There are no API commitment control resources for the commitment definition.
- There are no protected connections registered for the commitment definition.
- The connection is to a remote relational database and the connection is unprotected. The connection is read-only. This will occur only when the following are true:
If running with commitment control, SQL will register a one-phase DRDA read-only resource.
- The connection is not local.
- The application server does not support distributed unit of work
- At least one of the following is true:
- The commitment control level of the program issuing the connect is *NONE.
- Another connection exists to an application server that does not support distributed unit-of-work and that application server can perform committable updates
- Another connection exists to an application server that supports distributed unit-of-work (including local).
- There are open updatable local files under commitment control for the commitment definition.
- There are open updatable DDM files that use a different connection under commitment control for the commitment definition.
- There are no one-phase API commitment control resources for the commitment definition.
- There are protected connections registered for the commitment definition.
- The connection is to a remote relational database and the connection is protected. It is unknown if committable updates can be performed. This will occur when all of the following are true:
If running with commitment control, SQL will register a two-phase DRDA undetermined resource.
- The connection is not local.
- The commitment control level of the program issuing the connect is not *NONE.
- The application server supports both distributed unit of work and two-phase commit protocol (protected connections).
- The connection is to a remote relational database and the connection is unprotected. It is unknown if committable updates can be performed. This will occur only when all of the following are true:
If running with commitment control, SQL will register a one-phase DRDA undetermined resource.
- The connection is not local.
- The application server supports distributed unit of work
- Either the application server does not support two-phase commit protocols (protected connections) or the commitment control level of the program issuing the connect is *NONE.
- The connection is to the local database or an application requester driver (ARD) program and the connection is protected. It is unknown if committable updates can be performed. If running with commitment control, SQL will register a two-phase DRDA undetermined resource.
The following table summarizes the type of connection that will result for remote distributed unit of work connections. SQLERRD(4) is set on successful CONNECT and SET CONNECTION statements.
Table 1. Summary of connection type Connect under commitment control Application server supports two-phase commit Application server supports distributed unit of work Other updatable one-phase resource registered SQLERRD(4) No No No No 2 No No No Yes 2 No No Yes No 4 No No Yes Yes 4 No Yes No No 2 No Yes No Yes 2 No Yes Yes No 4 No Yes Yes Yes 4 Yes No No No 1 Yes No No Yes 2 Yes No Yes No 4 Yes No Yes Yes 4 Yes Yes No No N/A 1 Yes Yes No Yes N/A 1 Yes Yes Yes No 3 Yes Yes Yes Yes 3 1DRDA does not allow protected connections to be used to application servers that support only remote unit of work (DRDA1). This includes all DB2 for i5/OS® TCP/IP connections.
Parent topic:
Distributed support
Related concepts
Commitment control
Related reference
Accessing remote databases with interactive SQL