SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-113
Considerations for DELETE
Authorization Requirements
DELETE requires authority to read and write to the table or view being deleted from
and authority to read tables or views specified in subqueries used in the search
condition.
Transaction Initiation and Termination
The DELETE statement automatically initiates a transaction if there is no active
transaction and if the statement references an audited table. Otherwise, you can
explicitly initiate a transaction with the BEGIN WORK statement. When a transaction is
started, the SQL statements execute within that transaction until a COMMIT or
ROLLBACK is encountered or an error occurs.
Isolation Levels of Transactions and Access Options of
Statements
The isolation level of an SQL transaction defines the degree to which the operations on
data within that transaction are affected by operations of concurrent transactions.
When you specify access options for the DML statements within a transaction, you
override the isolation level of the containing transaction. Each statement then executes
with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION
statement. See SET TRANSACTION Statement on page 2-218.
The default isolation level of a transaction is determined according to the rules
specified in Isolation Level on page 10-45.
It is important to note that the SET TRANSACTION statement might cause a dynamic
recompilation of the DML statements within the next transaction. Dynamic
recompilation occurs if NonStop SQL/MX detects a change in the transaction mode at
run time compared with the transaction mode at the time of static SQL compilation. To
avoid dynamic recompilation because of a change in the transaction mode, consider
specifying access options for individual DML statements instead of using SET
TRANSACTION.
Audited and Nonaudited Tables
SQL/MX tables can only be audited. You can run NonStop SQL/MX against nonaudited
SQL/MP tables.
The TMF product works only on audited tables, so a transaction does not protect
operations on nonaudited tables. Nonaudited tables follow a different locking and error
handling model than audited tables. Certain situations, such as DML error occurrences
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed