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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-150
Considerations for INSERT
If the default INSERT_VSBB is set to USER, NonStop SQL/MX does not use
statement atomicity. Unless you are inserting only a few records, you should not
disable INSERT_VSBB to use statement atomicity, because performance is affected.
Perform UPDATE STATISTICS on the tables so that row estimates are correct.
To see what rollback mode NonStop SQL/MX is choosing, you can prepare the query,
and then perform DISPLAY_OPTIONS:
display_explain options 'f' my_query;
Token “x” means that the transaction will be rolled back. Token “s” means that
NonStop SQL/MX will choose DP2 savepoints. See DISPLAY_EXPLAIN Command on
page 4-13 for details. For details about these defaults, see INSERT_VSBB on
page 10-61 and UPD_SAVEPOINT_ON_ERROR on page 10-64.
Authorization Requirements
INSERT requires authority to read and write to the table or view receiving the data and
authority to read tables or views specified in the query expression (or any of its
subqueries) in the INSERT statement.
Transaction Initiation and Termination
The INSERT statement automatically initiates a transaction if there is no active
transaction and if the statement will affect an audited table. Otherwise, you can
explicitly initiate a transaction with the BEGIN WORK statement. After a transaction is
started, the SQL statements execute within that transaction until a COMMIT or
ROLLBACK is encountered or an error occurs.
Isolation Levels of Transactions and Access Options of
Statements
The isolation level of an SQL/MX transaction defines the degree to which the
operations on data within that transaction are affected by operations of concurrent
transactions. When you specify access options for the DML statements within a
transaction, you override the isolation level of the containing transaction. Each
statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION
statement. See SET TRANSACTION Statement on page 2-218. The default isolation
level of a transaction is determined according to the rules specified in Isolation Level
on page 10-45.
It is important to note that the SET TRANSACTION statement might cause a dynamic
recompilation of the DML statements within the next transaction. Dynamic
recompilation occurs if NonStop SQL/MX detects a change in the transaction mode at
run time compared with the transaction mode at the time of static SQL compilation. To
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed