Neoview Control Query Default (CQD) Reference Guide (R2.5)

BLOCK_TO_PREVENT_HALLOWEEN
Runtime controlsCategory
A self-referencing insert is one which inserts into a target table and also scans from the
same target table as part of the query that produces rows to be inserted. Inconsistent results
are produced by the insert statement if the statement scans rows which have been inserted
by the same statement. This is sometimes called the “Halloween problem.” Neoview
prevents the Halloween problem using one of two methods: 1) the blocking method uses
a SORT operation to ensure all rows have been scanned before any are inserted, or 2) the
disk process (ESAM) locks method tracks the rows which have already been inserted and
the SCAN operator skips these rows.
The compiler chooses the blocking method in cases in which static analysis of the plan
indicates that the disk process locks method cannot be used. However, the compiler does
not evaluate one condition that would prevent the use of the disk process locks method:
the AUTOCOMMIT setting in which the statement is executed. Instead the compiler assumes
that the statement is executed with the default setting for AUTOCOMMIT, ‘ON’. If
AUTOCOMMIT is set to ‘OFF’ and self-referencing insert statement which uses the disk
process locks method is executed, then a runtime error (SQLCODE 8107) is raised.
This CQD is used to force the compiler to use the blocking method to prevent error 8107.
Description
The compiler is free to choose which method to use to prevent
the Halloween problem.
‘OFF’
The compiler is forced to use the blocking method.‘ON’
The default value is ‘ON’.
Values
Change this default to ‘ON’ if error 8107 is raised for a self-referencing insert statement
which is executed in a session with AUTOCOMMIT set to ‘OFF’.
Usage
Not applicableProduction usage
Using the ‘ON’ value in conditions that require it allows successful completion of the insert
statement. Using the ‘ON’ value when not required can decrease performance of some
self-referencing insert statements.
Impact
If self-referencing insert statements which execute with AUTOCOMMIT ‘OFF’ can be
restricted to a service level, then this default should be set to ‘ON’ only for that service
level. Otherwise the setting should be made for the system.
Level
Not applicableConflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 2.2Introduced in release
Not applicableDeprecated in release
UPD_ORDERED
Influencing Query PlansCategory
Controls whether rows should be inserted, updated, or deleted in clustering key order.Description
54 Transaction Control and Locking