You must have set the COMMIT environment variable to ON before using the COMMIT and ROLLBACK commands for FOCUS data sources, and before using the FOCUS Database Server. It is recommended that you set COMMIT at the beginning of the Maintain application's root procedure (preceding the MAINTAIN command), and that you reset it to its initial value when the application finishes. This avoids interfering with any MODIFY applications that your site may run.

SET COMMIT={ON|OFF}

The FOCUS DBMS ensures transaction integrity when multiple users are trying to access the same data source concurrently. If you are processing a transaction and—in the interval between beginning your transaction and completing it—the segments updated by your application have been changed and committed to the data source by another user, Maintain will roll back your transaction. This coordination is performed by the FOCUS Database Server. You can test to see if your transaction was rolled back by checking the value of the FocCurrent transaction variable, and then branch accordingly.

This strategy—in which FOCUS verifies that the records to which you wish to write have not been written to by another user in the interim—is called change verification. It allows many users to share write access to a data source, and grants update privileges for a given record to the first user that attempts the update.

Change verification takes advantage of the fact that two users rarely try to update the same record at the same time. Some DBMSs use strategies that lock out all but one user. Others grant update privileges to the first user that retrieves a record, even if he or she is the last one ready to update it—resulting in a performance bottleneck. In contrast, the FOCUS DBMS strategy of change verification enables the maximum number of users to access the same data concurrently, and makes it possible to write the maximum number of transactions in the shortest time. The FOCUS Database Server and the change verification strategy are designed for high-performance transaction processing.

The FOCUS Database Server's change-verification strategy is an extension of basic transaction processing. Each application user that accesses the FOCUS Database Server is known as a client. To ensure transaction integrity, follow this simple change-verify protocol:

1. As always, use the NEXT or MATCH commands to retrieve the data source records you need for the current transaction. When the application issues these commands, the server sends the application a private "client" copy of the records.
2. When the application issues a data source write command (such as INCLUDE, UPDATE, REVISE, or DELETE) against the retrieved records, it updates its private copy of the records.
3. When the application issues a COMMIT command to indicate the end of the transaction, the application's session sends a log of the transaction back to the server. The server now checks to see if any of the segments that the transaction changed have, in the interim, been changed and committed to the data source by other clients, and if any segments that the transaction added have, in the interim, been added by other clients. (You can customize which segments the FOCUS Database Server checks for changes by setting the PATHCHECK environment variable, as described in Selecting Which Segments Will Be Verified for Changes.)

   The server takes one of the following actions:

   • No conflict. If none of the records have been changed or added in the interim, then the transaction is consistent with the current state of the data source. The server writes the transaction to the data source and sets the application's FocCurrent transaction variable to zero to confirm the update.
   • Conflict. If any records have been changed in the interim, then the transaction might be inconsistent with the current state of the data source. The server ignores the transaction's changes to the data source—rolling back the transaction—and alerts the application by setting FocCurrent to a non-zero number.
4. The application evaluates FocCurrent and branches to the appropriate function.

When you use a FOCUS Database Server, you can customize the change verification process by defining the segments for which the FOCUS Database Server will verify changes. You define this using the PATHCHECK environment variable.

You can choose between:

• All segments in the path. The FOCUS Database Server verifies that all segments in the path extending from the root segment to the target segment have not been changed and committed in the interim by other users. This is the default setting.
• Modified segments only. The FOCUS Database Server determines which segments you are updating or deleting, and verifies that those segments have not been changed and committed in the interim by other users.

You can set PATHCHECK for each FOCUS Database Server, which affects all applications that access FOCUS data sources managed by that FOCUS Database Server. To set it, issue the SET PATHCHECK command in the FOCUS Database Server profile (HLIPROF).

SET PATHCHECK={ON|OFF}

To identify which FOCUS Database Server will manage access to a given FOCUS data source, you must issue a USE command that associates the server with the data source. You can issue the USE command in a FOCUS profile procedure (FOCPROF or PROFILE), or at the beginning of the Maintain application's root procedure preceding the MAINTAIN command. For more information about FOCUS profile procedures, see the FOCUS installation guide for your operating environment.

USE 
datafile ON server_id 
[datafile ON server_id]
.
.
.
END

When a FOCUS Database Server manages access to a FOCUS data source, each logical transaction that accesses that data source works with its own private copy of the data source's records. For more information about how a FOCUS Database Server manages access to a data source, see the Simultaneous Usage Reference Manual for your operating environment. This ensures that the transaction sees a consistent image of the data source that is isolated from changes being attempted by other users.

Non-Maintain procedures—for example, report procedures—are not part of a logical transaction; when control passes from a Maintain procedure to a non-Maintain procedure, the open transaction is suspended for the duration of the non-Maintain procedure. Therefore, if the non-Maintain procedure reports against a FOCUS data source, it accesses the live data source, not the open transaction's private copy. Changes made by the open transaction are not seen by the report, and changes committed by other users since the open transaction began are seen by the report, though not necessarily by the open transaction.

A FOCUS data source being managed by a FOCUS Database Server can be accessed by both Maintain applications and legacy MODIFY applications. Note that while MODIFY allows creating records with duplicate keys, Maintain does not support FOCUS data sources that have duplicate keys.

DB2 ensures transaction integrity by locking data source rows when they are read. The behavior of a lock depends on a transaction's isolation level; the techniques discussed here for Maintain applications all use an isolation level of repeatable read. Repeatable read involves a trade-off: it ensures absolute transaction integrity, but it can prevent other users from accessing a row for long periods of time, creating performance bottlenecks.

Under repeatable read, a row is locked when it is retrieved from the data source, and is released when the transaction that retrieved the row is either committed to the data source or rolled back. A Maintain DB2 transaction is committed or rolled back each time a Maintain application issues a COMMIT or ROLLBACK command. You explicitly code COMMIT and ROLLBACK commands in your Maintain application; in some circumstances the application may also issue these commands implicitly, as described in Designing Transactions That Span Procedures, and in When an Application Ends With an Open Transaction.

We recommend two strategies for writing transactions to DB2 data sources:

• Transaction locking. This locks each row for the duration of the transaction—from the time a row is retrieved until the transaction is committed. In effect, it relies on DB2 to ensure transaction integrity. This is simpler to code, but keeps rows locked for a longer period of time. This is the preferred strategy, unless the duration of the locks interferes excessively with your data source concurrency requirements.
• Change verification. This locks each row while it is being retrieved, then releases the lock, and then relocks the row shortly before writing it to the data source. This technique ensures transaction integrity by verifying, before writing each row, that the row has not been changed by other users in the interim. This is more complex to code, but locks rows for a shorter period of time, increasing data availability.

While these strategies are described for use with DB2 data sources, you can also apply them to transactions against other kinds of data sources, changing DBMS-specific details when necessary.

You can use the transaction locking strategy to manage DB2 row locks in Maintain applications. When using transaction locking, your application locks each row with an isolation level of repeatable read for the duration of the transaction—from the time it retrieves the row until the time it commits or rolls back the transaction.

The following illustration shows the duration of connections, threads, and logical units of work when you use this strategy.

If your applications are small in scope, comprising only a single procedure, the duration of connections, threads, and logical units of work would look like this:

Compared to the change verification strategy, transaction locking is simpler to code, but keeps rows locked for a longer period of time. This may cause other users to experience time outs, in which case DB2 will return a -911 or -904 SQL code. You can mitigate the effect of row locking by:

• Keeping the size of the transaction small, making it less likely that another user will encounter a row locked by your transaction.
• Implementing the change verification strategy described in Using Change Verification to Manage DB2 Row Locks.
• Having user applications check for a locked condition when retrieving rows, and upon encountering a lock, re-issuing the retrieval request a specified number of times in a loop. If the user application exceeds the specified number of attempts, have it display a message to the user indicating that the row is in use, and suggesting that the user try again later.
• Using standard database administration techniques such as report scheduling, tablespace management, and data warehousing.

You can use the change verification strategy to manage DB2 row locks in Maintain applications. When using change verification, your application retrieves all needed rows into a stack, locking them in the process; releases the locks after retrieval; and then performs all updates against the stack (not against the data source). This enables you to work with the data in the stack as long as necessary without preventing other users from accessing the data source. When you are ready to close the transaction, you re-retrieve the original rows from the data source, relocking them in the process. You then compare their current values in the data source to the values they held when you first retrieved them, and write the transaction to the data source if the values are the same—that is, if the rows have not been changed by other users in the interim.

Change verification enables the maximum number of users to access the same data concurrently, and makes it possible to write the maximum number of transactions in the shortest time. It is able to do this because it is an optimistic locking protocol—that is, it is optimized for the most common situation, in which at any moment, at most one user will attempt to update a given row. Compared to the transaction locking strategy, this is more complex to code, but locks rows for less time, increasing data availability.

The following illustration shows the duration of connections, threads, and logical units of work when you use this strategy.