Embedded SQL Programming Guide

Considerations for Distributed Unit of Work (DUOW)

This section describes how your applications can work with remote databases and how they can work with more than one database at a time. Included in the discussion are:

• "Remote Unit of Work"
• "Distributed Unit of Work"

With DB2, you can run remote server functions such as BACKUP, RESTORE, DROP DATABASE, CREATE DATABASE and so on as if they were local applications. For more information on using these functions remotely, see the Administration Guide.

Remote Unit of Work

A unit of work is a single logical transaction. It consists of a sequence of SQL statements in which either all of the operations are successfully performed or the sequence as a whole is considered unsuccessful.

A remote unit of work lets a user or application program read or update data at one location per unit of work. It supports access to one database within a unit of work. While an application program can access several remote databases, it can only access one database within a unit of work.

A remote unit of work has the following characteristics:

• Multiple requests per unit of work are supported.
• Multiple cursors per unit of work are supported.
• Each unit of work can access only one database.
• The application program either commits or rolls back the unit of work. In certain error circumstances, the server may roll back the unit of work.

Distributed Unit of Work

Distributed unit of work (DUOW) allows an application to access more than one database within a unit of work; that is, the application can switch between databases before committing the data. This gives an application programmer the ability to do work involving multiple databases, local and remote, at the same time.

You can use DUOW to read and update multiple DB2 Universal Database databases within a unit of work. If you have installed DB2 Connect for OS/2 or DB2 Connect for AIX (DDCS Version 2.3.1 or later) you can also use DUOW with other DRDA application servers, such as DB2 for MVS/ESA and DB2 for AS/400. Certain restrictions apply when you use DUOW with other application servers, as described in "Distributed Unit of Work with DB2 Connect".

A transaction manager coordinates the commit among multiple databases. If you use a transaction processing (TP) monitor environment such as CICS for AIX, the TP monitor uses its own transaction manager. Otherwise, the transaction manager supplied with DB2 is used. The DB2 Universal Database transaction manager is an XA (extended architecture) compliant resource manager. The TP monitors access data using the XA interface. Also note that the DB2 Universal Database transaction manager is not an XA compliant transaction manager, meaning the transaction manager can only coordinate DB2 databases.

For detailed information about DUOW, see the Administration Guide.

When to Use DUOW

DUOW is most useful when you want to work with two or more databases and maintain data integrity. For example, if each branch of a bank has its own database, a money transfer application could do the following:

• Connect to the sender's database
• Read the sender's account balance and verify that enough money is present.
• Reduce the sender's account balance by the transfer amount.
• Connect to the recipient's database
• Increase the recipient's account balance by the transfer amount.
• Commit the databases.

By doing this within one unit of work, you ensure that either both databases are updated or neither database is updated.

Coding SQL for a DUOW Application

Table 15 illustrates how you code SQL statements for DUOW. The left column shows SQL statements that do not use DUOW; the right column shows similar statements with DUOW.

Table 15. RUOW and DUOW SQL Statements

RUOW Statements	DUOW Statements
CONNECT TO D1 SELECT UPDATE COMMIT CONNECT TO D2 INSERT COMMIT CONNECT TO D1 SELECT COMMIT CONNECT RESET	CONNECT TO D1 SELECT UPDATE CONNECT TO D2 INSERT RELEASE CURRENT SET CONNECTION D1 SELECT RELEASE D1 COMMIT

The SQL statements in the left column access only one database for each unit of work. This is a remote unit of work (RUOW) application.

The SQL statements in the right column access more than one database within a unit of work. This is a distributed unit of work (DUOW) application.

Some SQL statements are coded and interpreted differently in a DUOW application:

• The current unit of work does not need to be committed or rolled back before you connect to another database.

• When you connect to another database, the current connection is not disconnected. Instead, it is put into a dormant state. If the CONNECT statement fails, the current connection is not affected.

• You cannot connect with the USER/USING clause if a current or dormant connection to the database already exists.

• You can use the SET CONNECTION statement to change a dormant connection to the current connection.

  You can also accomplish the same thing by issuing a CONNECT statement to the dormant database. This is not allowed if you set SQLRULES to STD. You can set the value of SQLRULES using a precompiler option or the SET CLIENT command or API. The default value of SQLRULES (DB2) allows you to switch connections using the CONNECT statement.

• In a select, the cursor position is not affected if you switch to another database and then back to the original database.

• The CONNECT RESET statement does not disconnect the current connection and does not implicitly commit the current unit of work. Instead, it is equivalent to explicitly connecting to the default database (if one has been defined). If an implicit connection is not defined, SQLCODE -1024 (SQLSTATE 08003) is returned.

• You can use the RELEASE statement to mark a connection for disconnection at the next COMMIT. The RELEASE CURRENT statement applies to the current connection, the RELEASE connection applies to the named connection, and the RELEASE ALL statement applies to all connections.

  A connection that is marked for release can still be used until it is dropped at the next COMMIT statement. A rollback does not drop the connection; this allows a retry with the connections still in place. Use the DISCONNECT statement (or precompiler option) to drop connections after a commit or rollback.

• The COMMIT statement commits all databases in the unit of work (current or dormant).

• The ROLLBACK statement rolls back all databases in the unit of work, and closes held cursors for all databases whether or not they are accessed in the unit of work.

• All connections (including dormant connections and connections marked for release) are disconnected when the application process terminates.

• Upon any successful connection (including a CONNECT statement with no options, which only queries the current connection) a number will be returned in the SQLERRD(3) and SQLERRD(4) fields of the SQLCA.

  The SQLERRD(3) field returns information on whether the database connected is currently updatable in a unit of work. Its possible values are:

  1

  Updatable.

  2

  Read-only.

  The SQLERRD(4) field returns the following information on the current characteristics of the connection:

  0

  Not applicable. This state is only possible if running from a down level client which uses one phase commit and is an updater.

  1

  One-phase commit.

  2

  One-phase commit (read-only). This state is only applicable to DRDA1 databases in a TP Monitor environment.

  3

  Two-phase commit.

  If you are writing tools or utilities, you may want to issue a message to your users if the connection is read-only.

Precompiling a DUOW Application

The following precompiler options are used when you precompile a DUOW application that does not use an external transaction manager as supplied in a TP Monitor environment. In a TP Monitor environment, these are ignored. Note that when you are precompiling a DUOW application, you should set the CLP connection to a type 1 connection, otherwise you will receive an SQLCODE 30090 (SQLSTATE 25000) when you attempt to precompile your application. For information on setting the connection type, see the Command Reference. To precompile your DUOW application, specify the CONNECT 2 precompiler option:

CONNECT ( 1 | 2)

Specify CONNECT 2 to indicate that this application uses the SQL syntax for DUOW applications, as described in "Coding SQL for a DUOW Application". The default, CONNECT 1, means that the normal (RUOW) rules for SQL syntax apply to the application.

SYNCPOINT ( ONEPHASE | TWOPHASE | NONE)

Specifies whether updates can be performed on one database per unit of work (the default), multiple databases with two-phase commit (TWOPHASE), or multiple databases without two-phase commit (NONE). Note that you require that a TM_DATABASE be defined for a TWOPHASE commit, except if you are using a transaction manager. For information on how these SYNCPOINT options impact the way your program operates, refer to the concepts section of the SQL Reference.

SQLRULES ( DB2 | STD)

Specifies whether DB2 rules or standard (STD) rules based on ISO/ANS SQL92 should be used in DUOW applications. DB2 rules allow you to issue a CONNECT statement to a dormant database; STD rules do not allow this.

DISCONNECT ( EXPLICIT | CONDITIONAL | AUTOMATIC)

Specifies which database connections are disconnected at COMMIT: only databases that are marked for release with a RELEASE statement (EXPLICIT), all databases that have no open WITH HOLD cursors (CONDITIONAL), or all connections (AUTOMATIC).

For a more detailed description of these precompiler options, see the Command Reference.

DUOW precompiler options become effective when the first database connection is made. You can use the SET CLIENT API to supersede connection settings when there are no existing connections (before any connection is established or after all connections are disconnected). You can use the QUERY CLIENT API to query the current connection settings of the application process.

The binder fails if an object referenced in your application program does not exist. There are three possible ways to deal with DUOW applications:

• You can split the application into several files, each of which accesses only one database. You then prep and bind each file against the one database that it accesses.

• You can ensure that each table exists in each database. For example, the branches of a bank might have databases whose tables are identical (except for the data).

• You can use only dynamic SQL.

Specifying Configuration Parameters for a DUOW Application

The following configuration parameters are used for DUOW applications:

TM_DATABASE

Specifies which database will act as a transaction manager for two-phase commit transactions.

RESYNC_INTERVAL

Specifies the number of seconds that the system waits between attempts to try to resynchronize an indoubt transaction. (An indoubt transaction is a transaction that successfully completes the first phase of a two-phase commit but fails during the second phase.)

LOCKTIMEOUT

Specifies the number of seconds before a lock wait will time-out and roll back the current transaction.

TP_MON_NAME

Specifies the name of the TP monitor, if any.

SPM_RESYNC_AGENT_LIMIT

Specifies the number of simultaneous agents that can perform resync operations with the DRDA server using SNA.

SPM_NAME

Identifies the SNA Logical Unit (LU) to be used for two phase commit with a DRDA Server.

SPM_LOG_SIZE

The number of 4 Kbyte pages of each primary and secondary log file used by the SPM to record information on connections, status of current connections, and so on.

For a more detailed description of these configuration parameters, see the Administration Guide.

DUOW Restrictions

The following restrictions apply to DUOW in DB2:

• In a transaction processing (TP) Monitor environment such as CICS/6000, the DISCONNECT statement is not supported. If you use DISCONNECT with a TP monitor, you will receive SQLCODE -30090 (SQLSTATE 25000). Instead of DISCONNECT, use RELEASE followed by COMMIT.

• Dynamic COMMIT and ROLLBACK are not supported in a connect type 2 environment. If you use a COMMIT in this environment, it is rejected with SQLCODE -925 (SQLSTATE 2D521). If you use a ROLLBACK in this environment, it is rejected with SQLCODE -926 (SQLSTATE 2D521).

• The precompiler option DISCONNECT CONDITIONAL cannot be used for connections to Version 1 databases. Connections to Version 1 databases are disconnected on COMMIT even if held-cursors are open.

• Although cursors declared WITH HOLD are supported with DUOW, in order for DISCONNECT to succeed, all cursors declared WITH HOLD must be closed and a COMMIT issued before the DISCONNECT request.

• When the services of TP Monitor Environments are used for transaction management, the DUOW options are implicitly CONNECT Type 2, SYNCPOINT TWOPHASE, SQLRULES DB2, DISCONNECT EXPLICIT. Changing these options with precompilation or the SET CLIENT API is not necessary and will be ignored.

• Your application receives an SQLCODE -30090 (SQLSTATE 25000) if it uses the following APIs in a DUOW (CONNECT Type 2), as these APIs are not supported in a DUOW:

       BACKUP DATABASE 
       BIND 
       EXPORT 
       IMPORT 
       LOAD 
       MIGRATE DATABASE 
       PRECOMPILE PROGRAM 
       RESTART DATABASE 
       RESTORE DATABASE 
       REORGANIZE TABLE 
       ROLLFORWARD DATABASE
  

• Stored procedures are supported within a DUOW. However, a stored procedure that issues a COMMIT and ROLLBACK statement in a DUOW (CONNECT Type 2) receives an SQLCODE -30090 (SQLSTATE 25000) as these statements are not supported in a DUOW.

[ DB2 List of Books | Search the DB2 Books ]