You can improve the performance of applications that use
heavy insert processing by controlling how space is allocated and
pre-formatted.
About this task
When inserting records, Db2 pre-formats
space within a page set as needed. The allocation amount, which is
either by cylinder or track, determines
the amount of space that is pre-formatted at any one time.
Because
less space is pre-formatted at one time for the track allocation amount,
a mass insert can take longer when the allocation amount is track
than the same insert when the allocation amount is cylinder. However,
smart secondary space allocation minimizes the difference between
track and cylinder allocation.
Cylinder allocation can reduce
the time required to do SQL mass inserts and to perform LOGONLY recovery.
It does not affect the time required to recover a table space from
an image copy or to run the REBUILD utility.
Use the following approaches to control space allocation
and pre-formatting:
- Specify your space allocation amounts to ensure allocation
by cylinder.
The allocation amount depends on device type
and the values that you specify for PRIQTY and SECQTY when you define
table spaces and indexes. If you use record allocation for more than
a cylinder, cylinder allocation is used.
The default SECQTY is 10%
of the PRIQTY, or 3 times the page size, whichever is larger. This
default quantity is an efficient use of storage allocation. Choosing
a SECQTY value that is too small in relation to the PRIQTY value results
in track allocation.
- Consider using the PREFORMAT option of the LOAD and REORG
utilities. Use this approach when Db2 pre-formatting
delays affect the performance or execution-time consistency of applications
that do heavy insert processing and the table size can be predicted
for a business processing cycle.
If you preformat during
LOAD or REORG, Db2 does
not have to preformat new pages during execution. When the pre-formatted
space is used and when Db2 has to
extend the table space, normal data set extending and pre-formatting
occurs. Consider pre-formatting only if pre-formatting is causing
a measurable delay with the insert processing or causing inconsistent
elapsed times for insert applications.
What to do next
Quantify the
results of pre-formatting in your environment by assessing the performance
both before and after using pre-formatting.