Application Development Guide

Considerations for Multisite Updates

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"
• "Multisite Update"

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, refer to 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.

Multisite Update

Multisite update, also known as Distributed Unit of Work (DUOW) and Two-Phase commit, is a function that enables your applications to update data in multiple remote database servers with guaranteed integrity. A good example of a multisite update is a banking transaction that involves transfer of money from one account to another in a different database server. In such a transaction it is critical that updates that implement debit operation on one account do not get committed unless updates required to process credit to the other account are committed as well. The multisite update considerations apply when data representing these accounts is managed by two different database servers.

You can use multisite update to read and update multiple DB2 Universal Database databases within a unit of work. If you have installed DB2 Connect or use the DB2 Connect capability provided with DB2 Universal Database Enterprise Edition you can also use multisite update with host or AS/400 database servers, such as DB2 Universal Database for OS/390 and DB2 Universal Database for AS/400. Certain restrictions apply when you use multisite update with other database servers, as described in Multisite Update with DB2 Connect.

A transaction manager coordinates the commit among multiple databases. If you use a transaction processing (TP) monitor environment such as TxSeries CICS, the TP monitor uses its own transaction manager. Otherwise, the transaction manager supplied with DB2 is used. DB2 Universal Database for OS/2, UNIX, and Windows 32-bit operating systems is an XA (extended architecture) compliant resource manager. Host and AS/400 database servers that you access with DB2 Connect are XA compliant resource managers. 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 multisite update, refer to the Administration Guide.

When to Use Multisite Update

Multisite Update 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 Multisite Update Application

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

Table 26. RUOW and Multisite Update SQL Statements

RUOW Statements	Multisite Update 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 multisite update application.

Some SQL statements are coded and interpreted differently in a multisite update 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 host or AS/400 database servers that you access with DB2 Connect without starting the DB2 Connect sync point manager.

  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 Multisite Update Application

When you precompile a multisite update 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 more information on setting the connection type, refer to the Command Reference. The following precompiler options are used when you precompile an application which uses multisite updates:

CONNECT (1 | 2)

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

SYNCPOINT (ONEPHASE | TWOPHASE | NONE)

If you specify SYNCPOINT TWOPHASE and DB2 coordinates the transaction, DB2 requires a database to maintain the transaction state information. When you deploy your application, you must define this database by configuring the database manager configuration parameter TM_DATABASE. For more information on the TM_DATABASE database manager configuration parameter, refer to the Administration Guide. 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/ANSI SQL92 should be used in multisite update 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, refer to the Command Reference.

Multisite update 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 multisite update 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 Multisite Update Application

For information on performing multisite updates coordinated by an XA transaction manager with connections to a host or AS/400 database, refer to the DB2 Connect User's Guide.

The following configuration parameters affect applications which perform multisite updates. With the exception of LOCKTIMEOUT, the configuration parameters are database manager configuration parameters. LOCKTIMEOUT is a database configuration parameter.

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 for a given database. The application must issue an explicit ROLLBACK to roll back all databases that participate in the multisite update. LOCKTIMEOUT is a database configuration parameter.

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 host or AS/400 server using SNA.

SPM_NAME

• If SPM is being used with a TCP/IP 2PC connection then the SPM_NAME must be an unique identifier within the network. When you create a DB2 instance, DB2 derives the default value of SPM_NAME from the TCP/IP hostname. You may modify this value if it is not acceptable in your environment. For TCP/IP connectivity with host database servers, the default value should be acceptable. For SNA connections to host or AS/400 database servers, this value must match an SNA LU profile defined within your SNA product.
• If SPM is being used with an SNA 2PC connection, the SPM name must be set to the LU_NAME that is used for 2PC.
• If SPM is being used for both TCP/IP and SNA then the LU_NAME that is used for 2PC must be used.

Note:

Multisite updates in an environment with host or AS/400 database servers may require SPM. For more information, refer to the DB2 Connect User's Guide.

SPM_LOG_SIZE

The number of 4 kilobyte 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, refer to the Administration Guide.

Multisite Update Restrictions

The following restrictions apply to multisite update in DB2:

• In a transaction processing (TP) Monitor environment such as TxSeries CICS, 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 multisite update, 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 multisite update 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 multisite update (CONNECT Type 2), as these APIs are not supported in a multisite update:

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

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