SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-260
Considerations for self-referencing inserts
•
AUTOCOMMIT must be ON for the optimizer to choose the DP2 Locks Method,
otherwise an SQL error 8107 is returned. AUTOCOMMIT is OFF for embedded
SQL programs written in C, C++ or COBOL and the DP2 Locks Method cannot be
used. For self-referencing INSERT statements from embedded SQL programs,
SQL error 8107 is returned. To overcome this problem, ensure the optimizer
chooses the Blocking Plan Operator Method by setting the CQD
BLOCK_TO_PREVENT_HALLOWEEN to ON.
•
Multiple SQL statements in a transaction are processed differently. If the first
statement does not use self-referencing updates and locks the table, the
subsequent statements cannot use self-referencing updates as they require row
locks. With the default DP2 Locks Method, DP2 returns SQL error 1192 indicating
a failed INSERT operation and no rows are inserted. You can overcome this
problem by setting the CQD BLOCK_TO_PREVENT_HALLOWEEN to ON.
Note: The optimizer chooses either the DP2 Locks or the Blocking Plan Operator Method
based on this CQD setting.
When the CQD is ON, the optimizer chooses the Blocking Plan Operator Method, and when
this CQD is OFF, the optimizer chooses the DP2 Locks Method. When the CQD is set to
RESET, the CQD value is reset to OFF. The default is OFF.
CONTROL QUERY DEFAULT BLOCK_TO_PREVENT_HALLOWEEN
{ON|OFF|RESET}