SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-21
Data Manipulation Language (DML) Statements
Accessing Nonaudited SQL/MP Tables
In both NonStop SQL/MP and NonStop SQL/MX, TMF works only on audited tables.
Therefore, 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. NonStop SQL/MX, on the other hand, is designed for
performance reasons to pipeline index maintenance activity with base table activity.
This design increases the likelihood that such inconsistencies might occur for
nonaudited tables. This table lists differences in how NonStop SQL/MP and NonStop
SQL/MX handle nonaudited DML operations
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.
NonStop SQL/MX provides a default setting, IUD_NONAUDITED_INDEX_MAINT, that
can warn you at compile time of nonaudited index maintenance. By default, operations
that could lead to an inconsistency between base tables and indexes are disabled. For
more information, see the SYSTEM_DEFAULTS table in the
SQL/MX Reference
Manual.
Data Manipulation Language (DML) Statements
SQL/MP DML statements are supported in NonStop SQL/MX. However, a few options
and clauses have been dropped because of SQL:1999 ANSI changes. Table 10-6
lists
the SQL/MP DML statements that are supported in NonStop SQL/MX and the syntax
differences:
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