NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
T-8
Rules for Host Programs
If you set AUTOWORK to OFF, you can define your own TMF transaction for
statements that can execute within a user-defined transaction. You must define a
transaction for DML operations on audited tables.
SQLCI automatically defines a transaction or series of transactions for a DDL
statement unless a user-defined transaction is in effect.
Rules for Host Programs
•
In a host program, you do not have to define a TMF transaction for a DDL
statement, but if the statement can execute within a user-defined transaction, you
can do so if necessary. (SQL automatically starts a system-defined transaction or
series of transactions for a DDL statement on an audited table that occurs outside a
user-defined transaction, ending the final transaction for the statement when the
DDL statement finishes.)
In a host program, however, you must define a TMF transaction for a DML
statement that operates on an audited object.
•
Typically, only one TMF transaction at a time is in progress for an application.
•
Either the requester or the server can start the transaction. Any program in a
program unit can start the transaction.
•
A context-free server should close its cursors and free locks on nonaudited tables
before replying to the requester.
•
If a host program executes a DDL statement on audited objects within a user-defined
TMF transaction, the DDL operation is performed as part of the user-defined TMF
transaction. If the operation terminates successfully, the host program determines
whether to commit or abort the transaction. In contrast, if the DDL operation
terminates with an error and the operation was partially performed, the system
automatically aborts the user-defined TMF transaction.
•
DDL statements that operate on nonaudited tables cannot execute when a user-
defined TMF transaction is in progress.
•
A DML statement access option should provide transaction consistency and
concurrency appropriate for your application and the environment in which it runs.
•
When your program updates both audited and nonaudited tables in a TMF
transaction, remember that only the audited tables are protected against
inconsistency by TMF.
For example, suppose a transaction updates two audited tables, A and C, and an
unaudited table, B. Within the transaction, your program updates tables A and B,
detects an error when attempting to update table C, and aborts the transaction. TMF
undoes the changes to table A, but the changes to table B remain, making the
database inconsistent.