RDBMS Transaction Control Within MODIFY

The adapter supports the Logical Unit of Work (LUW) concept defined by the RDBMS. An LUW consists of one or more FOCUS maintenance actions (UPDATE, INCLUDE, or DELETE) that process as a single unit. The maintenance operations within the LUW can operate on the same or separate tables.

DB2, IDMS/SQL, and Oracle define a transaction as all actions taken since the application first accessed the RDBMS, last issued a COMMIT WORK, or last issued a ROLLBACK WORK.

Within a logical unit of work, the RDBMS either executes all statements completely, or else it executes none of them. If the RDBMS detects no errors in any of the statements within the LUW:

In response to unsuccessful execution of any statement in the transaction, the adapter:

The RDBMS and the adapter provide a level of automatic transaction management but, in many cases, this level of management alone is not sufficient. In MODIFY, the Adapters for DB2, IDMS/SQL, and Oracle support explicit control of RDBMS transactions with the commands SQL COMMIT WORK and SQL ROLLBACK WORK. The Adapter for Teradata supports explicit transaction control with the commands SQLDBC BEGIN TRANSACTION, SQLDBC END TRANSACTION, and SQL ROLLBACK WORK. (The Adapter for Teradata also supports the SQL COMMIT WORK command). In Maintain, the equivalent commands are COMMIT and ROLLBACK.

Note: SQL COMMIT WORK and SQL ROLLBACK WORK are native SQL commands—commands that the adapter passes directly to the RDBMS for immediate execution. (You can issue SQL commands in MODIFY, but not in Maintain.) Do not confuse these commands with the FOCUS COMMIT WORK and ROLLBACK WORK commands that apply to FOCUS data sources only. The adapter ignores COMMIT WORK and ROLLBACK WORK without the SQL qualifier.

With the default AUTOCOMMIT setting (see Controlling Connection Scope), unless you specify SQL COMMIT WORK and/or SQL ROLLBACK WORK in your MODIFY procedure (or COMMIT and/or ROLLBACK in your Maintain procedure), all FOCUS maintenance actions until the END command constitute a single LUW. If the procedure completes successfully, the adapter automatically transmits a COMMIT WORK command to the RDBMS, and the changes become permanent. If the procedure terminates abnormally, the adapter issues a ROLLBACK WORK to the RDBMS, and the database remains untouched. Since locks are not released until the end of the program, a long MODIFY or Maintain procedure that relies on the default, end-of-program COMMIT WORK can interfere with concurrent access to data. In addition, you may lose all updates in the event of a system failure.

Transaction Termination (COMMIT WORK)

The native SQL COMMIT WORK statement signals the successful completion of a transaction at the request of the procedure. Execution of a COMMIT statement makes changes to the tables permanent. The syntax in a MODIFY request is:

SQL COMMIT WORK

You can issue a COMMIT WORK as an ON MATCH, ON NOMATCH, ON NEXT, or ON NONEXT condition, after an update operation (INCLUDE, UPDATE, DELETE), or within cases of a Case Logic request.

Note: In Maintain, you must use the Maintain facility COMMIT command to transmit an SQL COMMIT WORK to the RDBMS.

Top of page

Example: Using COMMIT WORK in a MODIFY Procedure

A COMMIT WORK example using Case Logic follows:

CASE PROCESS
   CRTFORM
   MATCH field1 ... 
     ON MATCH insert, update, delete, ...
   GOTO EXACT
ENDCASE
CASE EXACT
   SQL COMMIT WORK 
   GOTO TOP 
ENDCASE

The PROCESS case handles the MATCH, ON MATCH, ON NOMATCH processing. Then it transfers to CASE EXACT, which commits the data, instructing the RDBMS to write the entire Logical Unit of Work to the data source.

Teradata Transaction Termination: BEGIN/END TRANSACTION

To indicate an explicit transaction or logical unit of work, specify the following syntax as ON MATCH, ON NOMATCH, ON NEXT, or ON NONEXT conditions, or include them in cases of Case Logic requests (the semicolon is optional):

SQLDBC BEGIN TRANSACTION[;]

and

SQLDBC END TRANSACTION[;]

The BEGIN TRANSACTION statement indicates the logical starting point of the LUW. The END TRANSACTION statement indicates the end of the LUW and that processing is completed. Teradata releases data locks when it encounters the END TRASACTION statement or the ROLLBACK WORK statement discussed in RDBMS Transaction Termination (ROLLBACK WORK).

Top of page

Example: Teradata Transaction Control Using BEGIN/END TRANSACTION

This Case Logic request illustrates one explicit transaction representing one LUW. It treats the operations in CASE PROCESS (MATCH, UPDATE, or INCLUDE) as a single LUW. Teradata locks the rows until the update is complete and the END TRANSACTION statement is processed.

MODIFY FILE ...
 TRACE
   .
   .
   .
CASE STARTIT
  SQLDBC BEGIN TRANSACTION;
  GOTO PROCESS
ENDCASE

CASE PROCESS
  CRTFORM ...
  MATCH keyfields 
  ON MATCH UPDATE ...
  ON MATCH GOTO ENDIT
  ON NOMATCH INCLUDE
  ON NOMATCH GOTO ENDIT
ENDCASE

CASE ENDIT
  SQLDBC END TRANSACTION;
  GOTO STARTIT
ENDCASE

This example illustrates multiple update transaction control. It also illustrates the use of the ROLLBACK WORK command:

MATCH tab1_keyfields 
  ON MATCH SQLDBC BEGIN TRANSACTION;
  ON MATCH DELETE
  ON MATCH CONTINUE
  ON NOMATCH SQLDBC ROLLBACK WORK;
  ON NOMATCH REJECT
  ON NOMATCH GOTO TOP

MATCH tab2_keyfields 
  ON MATCH UPDATE anyfield 
  ON MATCH SQLDBC END TRANSACTION;
  ON NOMATCH SQLDBC ROLLBACK WORK;
  ON NOMATCH REJECT

RDBMS Transaction Termination (ROLLBACK WORK)

The native SQL ROLLBACK WORK statement signals the unsuccessful completion of a transaction at the request of the procedure. Execution of a ROLLBACK statement backs out all changes made to the tables since the last COMMIT statement, or for Teradata, since the last BEGIN TRANSACTION statement.

The syntax in a MODIFY request is:

SQL ROLLBACK WORK

You can design a MODIFY procedure to issue a ROLLBACK WORK statement if you detect an error. For example, if a FOCUS VALIDATE test finds an unacceptable input value, you may choose to exit the transaction, backing out all changes since the last COMMIT. You can issue ROLLBACK WORK as an ON MATCH, ON NOMATCH, ON NEXT, or ON NONEXT condition, or within cases of a Case Logic request.

For Teradata, the END TRANSACTION statement is not required when the ROLLBACK WORK statement is used. The END TRANSACTION statement is implied and, if it is encountered, produces a message that you can ignore.

Note: In Maintain, you must use the Maintain facility ROLLBACK command to transmit an SQL ROLLBACK WORK to the RDBMS.

The adapter automatically executes an SQL ROLLBACK WORK statement when you exit from a transaction early. For example, if you exit a CRTFORM without specifying some action, the adapter automatically issues a ROLLBACK WORK statement on your behalf.

The RDBMS automatically issues a ROLLBACK WORK statement in case of system failure or when it detects a fatal data error, such as a reference to a column or table that does not exist.

Top of page

Example: Using ROLLBACK WORK in a MODIFY Procedure

The following is an example of the ROLLBACK WORK statement using Case Logic:

ON NOMATCH CRTFORM ...
ON NOMATCH VALIDATE ...
    ON INVALID GOTO ROLLCASE
      .
      .
      .
CASE ROLLCASE
    SQL ROLLBACK WORK
    GOTO TOP
ENDCASE

Code the ROLLBACK WORK statement before a REJECT command. FOCUS ignores any action following the rejection of a transaction, except for GOTO or PERFORM.

For example:

ON MATCH SQL ROLLBACK WORK
ON MATCH REJECT

Top of page

Example: RDBMS Transaction Control

Each time an employee's salary changes, the following example updates the salary in the EMPINFO table and posts a historical pay record for the new salary in the related PAYINFO table. To ensure that both updates complete or neither one does, the MODIFY procedure places both actions prior to a COMMIT WORK statement. If the descendant table is not processed, ROLLBACK WORK discards the whole logical transaction.

MODIFY FILE EMPPAY
COMPUTE DAT_INC=&YMD;
CRTFORM LINE 1
"</2 <25 MODIFY FOR SALARY CHANGE </2 "
"<20 ENTER THE EMPLOYEE ID <EMP_ID "
MATCH EMP_ID
  ON MATCH CRTFORM LINE 7
  "<D.FIRST_NAME <D.LAST_NAME "
  "CURRENT SALARY <T.CURRENT_SALARY> "
  "JOBCODE <T.CURR_JOBCODE> "
  "PLEASE CHANGE THE SALARY AND JOB CODE"
  ON MATCH COMPUTE
      OLDSAL/D12.2 = D.CURRENT_SALARY;
  ON MATCH UPDATE CURRENT_SALARY CURR_JOBCODE
  ON NOMATCH REJECT

MATCH DAT_INC
  ON NOMATCH COMPUTE
    SALARY=CURRENT_SALARY;
    JOBCODE=CURR_JOBCODE;
    PCT_INC = (SALARY - OLDSAL)/OLDSAL;
  ON NOMATCH INCLUDE
  ON NOMATCH SQL COMMIT WORK
  ON MATCH SQL ROLLBACK WORK
  ON MATCH REJECT
DATA
END

Using the Return Code Variable: FOCERROR

The RDBMS produces a return code or SQLCODE that reflects the success or failure of SQL statements. FOCUS stores this return code in the variable FOCERROR. You can test FOCERROR in a MODIFY or Maintain procedure and take the appropriate action if you encounter a non-fatal error. A return code of 0 indicates successful completion of the last SQL command issued (either a native SQL command, such as SQL DELETE or SQL COMMIT WORK, or an SQL command generated by MODIFY or Maintain).

Fatal error conditions, such as an inactive RDBMS machine, automatically terminate the procedure. Non-fatal errors, such as an attempt to include a duplicate value for a unique index, allow the procedure to continue.

You can test the FOCERROR variable in FOCUS VALIDATE or IF commands to determine whether to continue or terminate processing. For example, if FOCERROR is -803 for DB2, 2801 for Teradata, 1058 for IDMS SQL, or 1481 for Oracle, the INCLUDE or UPDATE operation failed in an attempt to include a value for a unique index. This condition might indicate the need to ROLLBACK the transaction or re-prompt the user for new input values.

For a list of common SQL return codes, see SQL Codes and Adapter Messages. For a complete list, see the Messages and Codes manual for your RDBMS.

Using the Adapter SET ERRORRUN Command

With SET ERRORRUN ON, MODIFY processing continues even when a serious error occurs, allowing applications to handle their own errors in the event that an RDBMS error is part of the normal application flow. Code this command explicitly within the MODIFY program, preferably in CASE AT START where it executes once.

Note: Maintain does not support the SET ERRORRUN command.

The syntax is

CASE AT START
    SQL SET ERRORRUN {OFF|ON}
ENDCASE

where:

OFF: Stops MODIFY processing when the RDBMS detects a fatal error (for example, when it cannot find the table name). OFF is the default.
ON: Enables MODIFY processing to continue despite fatal errors. Test the value of FOCERROR to determine the desired action after an RDBMS call.

When SET ERRORRUN is ON, the MODIFY procedure reports the error but continues execution. The MODIFY code can then test the value of FOCERROR to determine the cause of the error and take appropriate action. Be careful in evaluating the contents of FOCERROR, as failure to respond to a negative SQLCODE or non-zero return code can cause unpredictable errors in subsequent RDBMS or MODIFY processing.

SET ERRORRUN returns to its default setting of OFF at the end of the MODIFY procedure.