The 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.

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 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 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 adapter retrieves the row from the table again. The 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 the run-time path of an application, 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.

SQL SET AUTOCOMMIT {OFF|ON CRTFORM}

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

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