SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-13
Nonaudited Tables
Nonaudited Tables
In both NonStop SQL/MP and NonStop SQL/MX, the HP NonStop Transaction
Management Facility (TMF) product works only on audited tables, so a transaction
does not protect operations on nonaudited tables. The simplest approach is to make all
tables audited.
NonStop SQL/MP is designed to avoid any inconsistencies between the base table and
an index for nonaudited tables. Because of its design, NonStop SQL/MX has a greater
likelihood that such inconsistencies might occur.
Nonaudited tables are not protected by transactions and follow a different locking and
error handling model than audited tables. In NonStop SQL/MX, 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
indexes.
To avoid potential problems, do not create indexes on nonaudited tables. Do not run
DDL or utility operations concurrently with DML operations on nonaudited tables. This
table lists differences in how NonStop SQL/MP and NonStop SQL/MX handle
nonaudited DML operations
NonStop SQL/MX provides a default setting that can warn you at compile time of
nonaudited index maintenance, called IUD_NONAUDITED_INDEX_MAINT. By default,
operations that could lead to an inconsistency between base tables and indexes are
INSERT Changed In NonStop SQL/MX, an ORDER BY clause can
be used following the specification of the insert
source. Also, multiple row-value constructors can
be used following the VALUES keyword. See
INSERT Statement
on page 3-14.
SELECT Changed In NonStop SQL/MX, an SQL value expression
can be used in a select list. Also, derived tables
(or query expressions) can be used in a FROM
clause. See SELECT Statement
on page 3-15.
UPDATE Changed In NonStop SQL/MX, a scalar subquery can be
used as the update value in a SET clause. See
UPDATE Statement
on page 3-16.
Operation NonStop SQL/MP NonStop SQL/MX
Insert/update/delete with a user error
during index maintenance
No inconsistencies Potentially many rows
affected
Insert/update/delete interrupted by
internal program abend or CPU halt
At most one row Potentially many rows
affected
Table 3-4. SQL/MX and SQL/MP DML Statements
SQL/MP DML
Statements
Implemented
in NonStop
SQL/MX? Comments