An important aspect of ADABAS application programming is the programming of transactions. The SQL statements INSERT, UPDATE, and DELETE do not modify the addressed table contents irreversibly. All SQL statements are combined to form so-called transactions. Only at the end of a transaction does ADABAS decide whether all the effects of the modifications made during the transaction are committed in the database or cancelled. This guarantees that, e.g., after a system failure, the effects of all SQL statements performed during the last transaction are established completely or not at all in the database. Consequently, it can be ensured in the application program that the database is always in a logically consistent state. In contrast to other relational database systems, data definition SQL statements in ADABAS are also subject to the transaction concept. This means that also data definition SQL statements, any combinations of them, as well as SQL statements such as INSERT, UPDATE, and DELETE can be rolled back.
The transaction structure also influences the lock behavior of the database and thus possibly the concurrency of user operations.
Finally, there are database states in which the end of all user transactions has to be awaited (e.g., database shutdown; for details see the CONTROL manual).
Statements for Transaction Control
All SQL statements placed between the statements COMMIT WORK and ROLLBACK WORK form a transaction. COMMIT WORK records the effects of all statements performed during the last transaction in the database and opens a new transaction. This statement is normally used to bracket SQL statements to form a transaction.
ROLLBACK WORK resets the effects of all statements of the last transaction and opens a new transaction. This statement can be useful in error cases and exceptional situations.
Logging on to ADABAS implicitly opens the first transaction. The dynamic order of COMMIT WORK or ROLLBACK WORK statements at execution time is decisive for the bracketing of SQL statements to form a transaction. Their static order within the program text is not significant.
Subtransactions
In SQLMODE ADABAS, there is the option of defining subtransactions, e.g., to atomize the effects of subprograms. The SQL statement SUBTRANS BEGIN opens a subtransaction and SUBTRANS END closes the subtransaction, removing the position defined by SUBTRANS BEGIN from memory. If, instead of SUBTRANS END, the SQL statement SUBTRANS ROLLBACK is used, all modifications to the database made within the subtransaction are cancelled.
Subtransactions may be nested. The SQL statements SUBTRANS END and SUBTRANS ROLLBACK always refer to the last open subtransaction.
SUBTRANS ROLLBACK or ROLLBACK cancel any subtransactions within the last (sub)transaction, even if they were closed with SUBTRANS END.
SQL statements that conclude subtransactions do not influence set locks, i.e., any existing locks are kept.
In SQLMODE ORACLE, SAVEPOINTs can be used to enable subtransactions. Within a transaction, the SQL statement SAVEPOINT can be used to define and name positions in this transaction. All modifications made since the SAVEPOINT with the specified name can be cancelled by means of the SQL statement ROLLBACK TO. Afterwards, any intermediate positions are no longer known.
Restartable Application Programming
After a system failure and subsequent restart of the database, the effects are restored up to the end of the last completed transaction. An application program, when started again, often cannot identify any more the position at which processing was interrupted. Batch programs, however, should be able to recognize after a breakdown and subsequent restart which transaction was executed last, and to continue processing from this point. This feature is called restartability of application programs. For this purpose, ADABAS offers transaction consistency as a help. A restartable application program must store the internal status of the last transaction (variable values, etc.) in one or several self-defined and self-managed status tables. Subsequent to a breakdown, the status table is also put into the status of the last completed transaction, so that after the program has requested its contents, it can find the re-entry point to continue processing.
The status table is generated before the program starts. At the beginning, a restartable program writes the initiating values of the program status variables into host variables. A subsequent SELECT statement checks whether the last program run was aborted. If this is the case, the program status variables contain the status of the last transaction which was successfully concluded after the last SELECT. If the last program run was regularly terminated, the status table contains no longer information for this program and the SELECT statement delivers an SQLCODE not equal to zero. Subsequently, the initiating values will be entered into the status table. During the execution of the program, the program status values must be updated for each transaction by means of the UPDATE statement. At the end of the program, the entry will be deleted from the status table.
Example of Restartability:
