Change Verify Protocol: AUTOCOMMIT ON CRTFORM

The data adapter AUTOCOMMIT ON CRTFORM facility provides application developers with an automated Change Verify Protocol to use as an alternative to the standard RDBMS method of concurrency and integrity in MODIFY CRTFORM procedures.

In this section: The FOCURRENT Variable Rejected Transactions and T. Fields How to: Invoke the Change Verify Protocol Example: Testing FOCURRENT

Note:

• This protocol is not supported for Teradata, which implicitly commits each SQL statement individually.
• Maintain does not support AUTOCOMMIT ON CRTFORM.

Without Change Verify Protocol, the data adapter relies solely on the RDBMS to manage the concurrent update and retrieval activities of its applications. Using the SQL COMMIT WORK statement, application developers can define transactions, or Logical Units of Work (LUWs), consisting of one or more database actions. Within the LUW, the RDBMS guarantees database integrity; database objects manipulated by the LUW will not change in an unpredictable manner before the termination of the LUW. Furthermore, if any failure occurs within the boundaries of the LUW, the RDBMS will undo, or ROLLBACK, any outstanding updates within the LUW.

The RDBMS uses a locking mechanism to prevent other concurrent transactions from interfering with the LUW. The locking mechanism allocates and isolates database resources for the LUW. However, this approach suffers from at least one basic drawback: terminal I/O locks data for an indeterminate amount of time. The lock remains allocated to the LUW until the user chooses to react to the screen display. Data source transaction throughput in many cases becomes more a function of RDBMS lock management than of RDBMS transaction processing performance.

AUTOCOMMIT ON CRTFORM automatically invokes the Change Verify Protocol through the following series of steps:

1. Before displaying a CRTFORM, the data adapter issues an SQL COMMIT WORK statement to release all locks held on the underlying table. The COMMIT releases all locks for the record displayed on the terminal.

2. If the application requests an update to the displayed record (UPDATE, DELETE, or INCLUDE), the data adapter retrieves the row from the table again. The data adapter compares the held and newly-retrieved images of the transaction record to determine whether a conflict exists with a transaction from another user. If no conflict exists, FOCUS processes the update as expected. If another user changed the record in the interim, FOCUS rejects the update. The application should test the value of the FOCURRENT variable to redirect the logic flow in the FOCEXEC (see The FOCURRENT Variable).

3. Many applications retrieve a record and perform VALIDATE tests on that record. If the record satisfies those tests, the MODIFY branches to a case that matches the record again and (potentially) updates it. In this situation, AUTOCOMMIT ON CRTFORM retrieves and compares the displayed and current versions of the record. The second MATCH subcommand and the update request each sponsor the Change Verify Protocol action. If no conflict exists, FOCUS submits the update as expected.

Note:

In this scenario, the MODIFY application itself must be coded to check FOCURRENT (see The FOCURRENT Variable); the second MATCH does not automatically perform the check. Any maintenance action issued subsequent to the second MATCH subcommand still performs an automatic check.

The Change Verify Protocol does not have the unit-of-work capabilities of the RDBMS protocol, but it never holds locks on CRTFORM-displayed records. By eliminating locks from an application's run-time path, it can substantially increase rates of transaction throughput and decrease terminal response times for interactive applications.

You can only issue the AUTOCOMMIT ON CRTFORM command in a MODIFY CRTFORM procedure. Place it in CASE AT START, not in the TOP case. You cannot switch AUTOCOMMIT modes within the MODIFY procedure.

The AUTOCOMMIT facility only works on single-record transactions. (For example, MODIFY MATCH and NEXT commands retrieve single records.) It is not designed for set processing with FOCUS multiple-record operations (REPEAT and HOLD, for example). For multiple-record processing, the Change Verify Protocol applies only to the last record.

Syntax: How to Invoke the Change Verify Protocol

SQL SET AUTOCOMMIT {OFF|ON CRTFORM}

where:

OFF

Is the default; it retains the native RDBMS locking protocol.

ON CRTFORM

Invokes the Change Verify Protocol.

To ensure data integrity in conjunction with AUTOCOMMIT ON CRTFORM, you must use an RDBMS isolation level of Repeatable Read (RR) for DB2 and DB2 for VM, Cursor Stability for IDMS SQL, and Write for Oracle. For a discussion of isolation levels, see Isolation Levels and Locks.

The FOCURRENT Variable

A MODIFY procedure that invokes the Change Verify Protocol can test the value of the FOCURRENT variable to determine whether there is a conflict with another transaction. FOCUS stores a zero in FOCURRENT if there is no conflict, and the transaction is accepted. A non-zero value indicates a conflict; the transaction is rejected, an error message is displayed, and you can redirect the MODIFY activity.

The possible values for FOCURRENT are:

0	Transaction accepted
1	Invalid, record input will create duplicate
2	Invalid, record has been deleted
3	Invalid, record has been changed

Your MODIFY application should test FOCURRENT and branch according to its value. For example, a typical procedure using AUTOCOMMIT ON CRTFORM submits a transaction, tests FOCURRENT, and, if FOCURRENT is non-zero, resubmits the transaction.

Note:

• The FOCURRENT variable is not supported for tables that do not have primary keys; its value is always 0. The AUTOCOMMIT ON CRTFORM option is not available for unkeyed tables.
• The FOCURRENT variable is not supported for Teradata.

Rejected Transactions and T. Fields

FOCUS treats transactions rejected because of a conflict as if they failed a VALIDATE test. Transactions that use CRTFORM turnaround fields (T. fields) may require special handling in this case. If, within the logic of your application, you wish to re-retrieve a VALIDATE-rejected record from the data source to display its current image, you must issue the MODIFY command DEACTIVATE INVALID. If you do not, the turnaround fields display the rejected values you attempted to enter. Decisions based on these values may be logically incorrect.

Note: Maintain does not support field activation or deactivation.

Example: Testing FOCURRENT

For example, the following MODIFY request updates the CURRENT_SALARY field and contains a FOCURRENT test:

MODIFY FILE EMPINFO
-*
CRTFORM LINE 1
" EMP_ID  <EMP_ID "
GOTO EMPLOYEE
-*
CASE EMPLOYEE
  MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH CRTFORM LINE 3
    " EMP_ID   <D.EMP_ID  "
    " SALARY   <T.CURRENT_SALARY> "
  ON MATCH UPDATE CURRENT_SALARY
  ON MATCH IF FOCURRENT NE 0 THEN GOTO UNDO; <= Check FOCURRENT and direct
ENDCASE                                          process for appropriate
-*                                               action.
CASE UNDO
  SQL ROLLBACK WORK
  DEACTIVATE INVALID
  GOTO EMPLOYEE
ENDCASE
-*
CASE AT START
  SQL SET AUTOCOMMIT ON CRTFORM  <== Releases record after display
ENDCASE
-*
DATA
END

In the following example, the CHANGE case (or second MATCH subcommand) applies the Change Verify Protocol action; the example also contains two VALIDATE tests:

MODIFY FILE EMPINFO
CRTFORM LINE 1
  " EMP_ID <EMP_ID "
GOTO VALIDATE
-*
CASE VALIDATE
  MATCH EMP_ID
  ON NOMATCH REJECT
  ON MATCH CRTFORM LINE 3
  " EMP_ID  <D.EMP_ID  "
  " SALARY  <T.CURRENT_SALARY> "
  " BONUS   <T.BONUS_PLAN>  "
  ON MATCH VALIDATE
    SALTEST= (CURRENT_SALARY GE 0) AND (CURRENT_SALARY LE 100000);
    BONTEST = (BONUS_PLAN GE 0) AND (BONUS_PLAN LE 100);
    ON INVALID TYPE "VALUES OUT OF RANGE "
    ON INVALID GOTO UNDO
  ON MATCH GOTO CHANGE
ENDCASE
-*
CASE CHANGE
  MATCH EMP_ID
    ON NOMATCH REJECT
    ON MATCH IF FOCURRENT NE 0 THEN GOTO UNDO;  <==   Check FOCURRENT and
    ON MATCH UPDATE CURRENT_SALARY BONUS_PLAN         direct process for
    ON MATCH IF FOCURRENT NE 0 THEN GOTO UNDO;  <==   appropriate action.
    ON MATCH SQL COMMIT WORK
ENDCASE
-*
CASE UNDO
  SQL ROLLBACK WORK
  DEACTIVATE INVALID        <== FOCUS does not return to old
  COMPUTE EMP_ID =EMP_ID;       screen with invalid data,
  GOTO VALIDATE                 but verifies data, then shows
ENDCASE                         refreshed data on the screen.
-*
CASE AT START
  SQL SET AUTOCOMMIT ON CRTFORM  <== Releases record after display
ENDCASE
-*
DATA
END

Note: If the application (and not the data adapter) sponsors the second MATCH, the MODIFY application itself must check FOCURRENT (see The FOCURRENT Variable); the second MATCH does not automatically perform the check. Any maintenance action issued subsequent to the second MATCH subcommand still initiates an automatic check.