SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
T-8
Rules for SQLCI
Rules for SQLCI
In SQLCI, you do not have to define your own TMF transactions or directly secure
locks on data. By setting the AUTOWORK session option, you can specify
system-defined transactions or user-defined transactions.

AUTOWORK ON
If you set AUTOWORK to ON, all data in SQL operations is automatically accessed
within a TMF transaction. By default, SQL obtains locks to guarantee a specific
level of data consistency. You can control the level of data consistency by
overriding the defaults provided by the locking mechanism.
SQL defines a transaction or a series of transactions for each DDL statement and
for each DML statement. A TMF transaction does not affect DML operations on
nonaudited objects, however.
The COPY, PURGE, and SECURE utilities also execute within a TMF transaction
or series of transactions when they operate on audited SQL objects.

AUTOWORK OFF
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