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

13 Transaction Control and Locking
This section describes these CQDs that are used for transaction control and locking:
• “INSERT_VSBB” (page 49)
• “MULTI_COMMIT_SIZE” (page 50)
• “NOT_ATOMIC_FAILURE_LIMIT” (page 51)
• “OPI_ERROR73_RETRIES” (page 51)
• “TABLELOCK” (page 52)
• “TIMEOUT” (page 53)
• “BLOCK_TO_PREVENT_HALLOWEEN” (page 54)
• “UPD_ORDERED” (page 54)
INSERT_VSBB
Transaction control and lockingCategory
Provides a method of inserting rows into a table whereby rows are buffered into a "virtual"
buffer because the entire buffer of rows is not inserted as is, but processed in the disk
process (ESAM) and inserted into the appropriate position in the table, based on the
clustering key values of each row.
Description
For simple inserts‘OFF’
Database determines the method‘SYSTEM’
To use VSBB‘USER’
The default value is ‘SYSTEM’.
Values
The most common usage of this CQD is to turn it to OFF in order to get better performance
for rowset inserters (for pre-R2.5 systems), or when there are multiple inserters inserting
rows into the same partition.
Usage
Do not use this CQD in production, unless one of the cases mentioned in Usage is
encountered.
Production usage
When the system chooses VSBB in certain scenarios, this can have a very negative impact
on performance and concurrency. This may show up as file system error 73 (The disk file
or record is locked) being encountered when loading for example.
If VSBB is chosen with Neoview Transporter loads, it is efficient. Neoview Transporter
pre-partitions the rows where a single process is inserting into a single partition. In this
case, the buffers get filled up efficiently and VSBB provides a benefit of a complete buffer
of rows being shipped for insertion instead of partially filled buffers.
However, if the inserter has not pre-partitioned the rows, then a rowset insert ends up
distributing only a small number of rows from the rowset into each buffer destined for a
specific partition. In this case large buffers are shipped relatively empty, resulting in worse
performance. This is addressed in R2.5. But until then, it is recommended that VSBB be
turned OFF in such situations.
Another situation where VSBB has a very negative impact is where there are multiple
processes inserting rows into the same partition. For example, when multiple simultaneous
loads are being done to the same table. This problem is worse when these inserters do not
commit the transaction often. In this case locking issues are encountered due to VSBB and
the system also tries to escalate the lock to a table lock, thereby impacting performance.
Impact
Set this CQD at a query level. But, the ideal way to set this would be at the service levels
that are used to influence the sort of inserters this CQD is trying to address.
Level
If VSBB is on and the transaction sizes are large for multiple inserters, this can accentuate
the locking and table escalation issues. To avoid the lock escalation issues, chances are that
this could be used with TABLELOCK OFF.
Conflicts/Synergies
INSERT_VSBB 49