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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-234
Considerations for UPDATE
In an SQL/MP entry-sequenced table, a value that updates a variable-length character
column must be the same length as the value it replaces.
Audited and Nonaudited Tables
SQL/MX tables must be audited. You can run NonStop SQL/MX against nonaudited
SQL/MP tables.
The Transaction Management Facility (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 or utility operations with DML operations can
lead to inconsistent data within a nonaudited table or between a nonaudited table and
its indices.
To avoid problems, do not run DDL or utility operations concurrently with DML
operations on nonaudited tables. When you try to delete data in a nonaudited table
with an index, NonStop SQL/MX returns an error.
SET ON ROLLBACK Considerations
The SET ON ROLLBACK expression is evaluated when each row is processed during
execution of the UPDATE statement. The results of the evaluation are applied when
and if the transaction is rolled back. This has two important implications:
•
If the SET ON ROLLBACK expression generates an error (for example, a divide by
zero or overflow error), the error is returned to the application when the UPDATE
operation executes, regardless of whether the operation is rolled back.
•
If an UPDATE operation is applied to a set of rows and an error is generated while
executing the UPDATE operation, and the transaction is rolled back, the actions of
the SET ON ROLLBACK clause apply only to the rows that were processed by the
UPDATE operation before the error was generated.
SET ON ROLLBACK Restrictions
The table must be audited. The columns used in the SET ON ROLLBACK clause:
•
Must be declared as NOT NULL.
•
Cannot be part of a referential integrity constraint or be part of a secondary index.
•
Cannot use the VARCHAR data type.
•
Cannot be used in the primary key, clustering key, or partitioning key.
Embedded SELECT UPDATE Behavior
When you use a SELECT UPDATE statement to perform a searched UPDATE in an
embedded statement, and more than one row satisfies the selection criteria, UPDATE
may give unexpected results.
Pub/Sub
Pub/Sub