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

Use Neoview Transporter for loading. Avoid the scenarios outlined that cause these
problems. If there are multiple inserters try and pre-partition the data and merge the streams
first, as Neoview Transporter does. Also, if a single rowset inserter is inserting into all
partitions, pre-partitioning the data and using separate inserters is ideal.
Addressing the real
problem
Neoview Release 1.0Introduced in release
Behavior change for rowset inserters scenario expected in R2.5.Deprecated in release
MULTI_COMMIT_SIZE
Transaction control and lockingCategory
Controls the commit size of each transaction for Long-Running Update (DELETE only)
operations that are executed in multiple transactions. The system-level default value for
this attribute is 10000, which means that the DELETE operation attempts to delete 10000
rows per each started transaction.
Description
Value is greater than 1.Any unsigned integer
The default value is ‘10000’.
Values
If transactions are not committed frequently the disk process (ESAM) attempts to escalate
row level locking to a table level lock, so as to avoid keeping track of so many locked rows
and the overhead associated with that. The value at which the disk process starts escalating
the lock is around the default value setting for a single partition.
Decreasing this number causes the commits to happen more frequently. You may consider
doing that to reduce the time it takes to back out the transaction if it aborts due to a failure,
although 10,000 rows inserted as part of rowset inserts are pretty fast.
Increasing this number could result in the disk process reaching its lock escalation limit.
Until it reaches that limit, you would get an improvement in performance due the lower
overhead due to fewer transaction commits. While the limit varies, it is not that much
higher than the chosen default.
You should take into account that if you are using rowset inserts, say via Neoview
Transporter, that the rows you are inserting are distributed across all the partitions. In that
sense this setting is very conservative because 10,000 rows across a large number of partitions
are far from the per partition lock escalation threshold. So, you could be a lot more
aggressive with this setting thereby reducing the commit cost.
Usage
Not applicableProduction usage
If commits happen more frequently there is a performance overhead associated with that.
If the disk process reaches its escalation limit, it can acquire a table lock, preventing other
updaters to concurrently update the table, or in warnings if it is unable to escalate the lock.
Also, increasing the number could result in the transaction reaching some of the other
transaction limits such as transaction timeout and audit pinning (see below).
Impact
AnyLevel
Not applicableConflicts/Synergies
An alternate, if concurrency is not a requirement and the operation can have exclusive
access to the table, is to get a table lock on the table and perform the delete operation in
one single transaction. This would result in the least overhead and faster performance.
However, if the delete operation takes long, you could cause audit trail pinning, which
could abort this transaction resulting in a huge back out operation that would take a long
time. It could also prevent other transactions from starting.
Addressing the real
problem
Neoview Release 2.3Introduced in release
Not applicableDeprecated in release
50 Transaction Control and Locking