SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-155
Multi Commit Delete
individually for each of these multiple transactions and not for the multi commit delete
as a whole.
Note. The multi-commit-option must only be used for large tables. Using this option on small
tables incurs unnecessary overhead from starting and committing multiple transactions.
This option is best used to delete unwanted data (for example, records older than 90 days)
from the database. Using this option for any other purpose can have point-in-time recovery
ramifications since these multiple transactions are independent transactions from TMF.
Considerations

On successful completion, the multi commit delete statement reports the total
number of rows deleted from all partitions of the table. If there is a failure, it reports
the total number of rows deleted to the point of the failure from all partitions along
with the diagnostic information.

Every multi commit delete statement is treated as a new statement. If there is a
failure, you can fix the error and reissue the statement. The operation scans the
processed rows again.

Multi commit delete works only when the AUTOCOMMIT mode is ON. It does not
work inside an explicit transaction (started with a BEGIN WORK).
Restrictions
The multi commit delete feature has the following restrictions:

It does not support views.

It does not support stream access.

It cannot be part of an embedded DELETE statement.

A host variable cannot be used to specify the granularity of each child transaction.

A host variable cannot be used in the WHERE clause of a multi commit delete
statement.

It does not support Java applications using the JDBC T2 driver.

The following restrictions which apply to an embedded DELETE are also
applicable to a multi commit delete:

An embedded DELETE cannot have a rowset search condition in the predicate
of the DELETE statement; SQL error 3426 is returned.

An embedded DELETE cannot have a subquery in the predicate of the
DELETE statement; SQL error 4139 is returned.

An embedded DELETE on a table, which is the subject table of a trigger,
returns SQL error 11045.