MX Database and Application Migration Guide

Converting SQL/MP Applications to SQL/MX

Applications

HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001

4-21

Data Manipulation Language (DML) Statements

Accessing Non-audited 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 non-audited 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 non-audited tables. On the other hand, NonStop SQL/MX is designed for

performance reasons to pipeline index maintenance activity with base table activity.

This design increases the probability that such inconsistencies might occur for non-

audited tables. The following table lists differences in how NonStop SQL/MP and

NonStop SQL/MX handle non-audited DML operations:

Non-audited 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 non-audited table or between a non-audited table and its

indexes.

To avoid potential problems, do not create indexes on non-audited tables. Do not run

DDL or utility operations concurrently with DML operations on non-audited tables.

NonStop SQL/MX provides a default setting, IUD_NONAUDITED_INDEX_MAINT, that

can warn you at compile time of non-audited 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, few options

and clauses have been dropped because of SQL:1999 ANSI changes. Table 4-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