SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-21
CONTROL TABLE Directive
Cursor Operations (Embedded SQL)
If you are performing cursor UPDATE or DELETE operations and have the
AUTOCOMMIT option turned on, the transaction is committed and the cursor closed at
the end of the first UPDATE WHERE CURRENT OF or DELETE WHERE CURRENT
OF. You cannot perform another fetch without reopening the cursor. You should turn off
the AUTOCOMMIT feature for cursor operations. When you complete your cursor
operations, you can turn the AUTOCOMMIT feature on again.
CONTROL TABLE Directive
In NonStop SQL/MP, you can use the CONTROL TABLE directive for many purposes
but mainly to specify performance-related options for DML accesses to a table or view.
In NonStop SQL/MP, the CONTROL TABLE directive affects decisions the SQL
compiler makes about how to execute DML statements. In NonStop SQL/MX, because
the compiler and executor have been redesigned, many directives are no longer
necessary.
In NonStop SQL/MP, you can enter multiple options on a single CONTROL TABLE
statement. In NonStop SQL/MX, you can enter only one option on a single CONTROL
TABLE statement.
Table 3-5 lists which SQL/MP controls are not implemented in NonStop SQL/MX or are
implemented differently:
BEGIN WORK;
UPDATE ...
UPDATE ...
COMMIT WORK;
--Explicit transaction started
--AUTOCOMMIT OFF
--Explicit transaction committed
--AUTOCOMMIT STILL ON
INSERT ...;
INSERT ...;
.
.
.
--Implicit transaction started
--Row committed
--Implicit transaction started
--Row committed