SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-165
Considerations for DELETE
WHERE CURRENT OF {cursor-name | ext-cursor-name}
specifies the name of a cursor (or extended cursor) positioned at the row to delete.
If you specify cursor-name for an audited table or view, the DELETE must
execute within a transaction that also includes the FETCH for the row. For more
information about cursor names and extended cursor names, see DECLARE
CURSOR Declaration on page 3-22 and ALLOCATE CURSOR Statement on
page 3-3.
For more information on searched and positioned DELETE statements in
embedded SQL programs, see the SQL/MX Programming Manual for C and
COBOL.
EVERY num ROWS
specifies the number of rows to be deleted for each independent transaction for a
multi commit delete operation. num must be an unsigned integer greater than
zero. The default value for num is 500.
Considerations for DELETE
In a searched DELETE, rows are deleted in sequence. If an error occurs and you are
not using DP2’s Savepoint feature, NonStop SQL/MX returns an error message and
stops deleting from the table. NonStop SQL/MX automatically rolls back the transaction
to undo the deleted data from the audited table.
If the default INSERT_VSBB is set to USER, NonStop SQL/MX does not use
statement atomicity. Unless you are deleting only a few records, you should not disable
INSERT_VSBB to use statement atomicity, because performance is affected. Perform
UPDATE STATISTICS on the tables so that row estimates are correct.
To see what rollback mode NonStop SQL/MX is choosing, you can prepare the query,
and then use the EXPLAIN statement:
explain options 'f' my_query;
Token “x” means that the transaction will be rolled back. Token “s” means that
NonStop SQL/MX will choose DP2 savepoints. See EXPLAIN Statement on
page 2-207 for details. For details about these defaults, see INSERT_VSBB
on
page 10-75 and UPD_SAVEPOINT_ON_ERROR on page 10-78.
Multi Commit Delete
DELETE operations can be long running on a very large data set (the number of rows
affected in a single table in a single transaction). This causes the locks to escalate to
file locks resulting in a loss of concurrency.
The multi commit delete feature executes these DELETE statements in multiple
statements. Each of these multiple statements is executed in a separate transaction,
thus avoiding lock escalation. These multiple transactions are independent
transactions from TMF. Therefore, any point-in-time recovery by TMF is done
C/COBOL