Troubleshooting
Problem
The transactions acting on the same table might experience performance impacts while trying to insert or update a LOB in a table.
Symptom
It is critical that DB2 does not free up previously deleted LOB space until you are certain that the deleted LOB space can be safely freed and will not be required later on. One example might be if one of the working transactions rolls back or the transaction fails in some way rather than being committed. There is a complex internal mechanism that manages this aspect for the objects in the LOB tablespace. Managing this can become very complex when there is more than one simultaneous use of the table. If there are a large number of pending delete LOB segments in the table, searching through these pending delete items and trying to find a free LOB segment to use for a LOB insert or update might take longer.
Cause
Performance might be affected when more pending delete LOB segments in the table and the LOB segments cannot be freed yet by the LOB manager. The pending delete list can become long when there are many deletes either within one transaction or within several close proximity transactions. Now, because LOBs can be very large, it is imperative that freed space be reused as soon as possible to avoid the LOB tablespace from growing rapidly and unexpectedly. This means that all possible attempts to determine if pending deleted LOB space is free and available for reuse, must be made when trying to find a suitable LOB space for an insert or update. As a result, insert or update operations can take longer to complete unless a freed space is found early on.
Things that can cause pending deleted LOB items to remain pending and not be marked as free:
1) Long running queries on the table.
2) Long table transactions (few commits).
3) Open cursors with hold that persist a long time.
Resolving The Problem
Work around
. Applications and users working on tables that contain LOBs should commit often and keep the length of transactions to a minimum.
. Reduce the use of 'with hold cursors' or be sure to close these cursors periodically.
. Consider inlining LOBs to the base table to reduce the number of LOBs that must be stored in the LOB tablespace.
. Consider an application design change which might help the simultaneous applications work better in harmony. For example, incorporate the use of a TEMP table to pre-read the table and allow the inserts and updates to the main table to run without interference.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21666038