The search for freespace for Insert, is a very complex
subject. In old days it used to subtly change at almost every
release or at least the wording changed in the Diagnosis manual.
Was also hard to understand the description there, when DB2
actually checked a page and when it just checked the Spacemap.
John Campbell's (IBM Distinguished Engineer) presentations and
papers are the authoritative guide to this.
I will quote just one small part of one of his
"So, the advantage of segmented table spaces is that four
bits of information per data page
in the space map with the classic partitioned and simple linear
table spaces, there's only
two bits per data page in the space map. So, these four bits
instead of two bits provides
more granular information, and it can reduce the number of
situations, where you can get
a false lead. A false lead being when the space map page
indicates a space in the data
page, and then when DB2 goes to the respective data page,
there's actually insufficient
space to absorb the new row.
But there are pros and cons on this. On one hand, having four
bits per data page, means
that there's better chance of avoiding these false leads. The
bad news is clearly there's
more updates to be done on the space map pages when you
have-high volume insert
processing, or high-volume update processing."
I am not going to try to go through the exhaustive space search
algorithm. I would mainly have to be quoting John anyway for fear
of getting it a bit wrong.
TRACKMOD NO is important to cut the level of Spacemap
update, especially in Data Sharing.
Append and MC00 are measures to prevent long space search.
We also have to bear in mind that ability to Insert to
page requires a conditional lock (for page level
locking), or in Data Sharing, also a Page p-Lock (Global
member extended time Latch is how I think of it) when row level
locking is used.
I recently tuned a very expensive Insert process, not so much
from contention, and not even massive Insert rates. Somewhat
exhaustive space search was costly and I believe that Conditional
locks were taken on pages only to find there was insufficient space
in the page (despite Spacemap false leads due to lack of
granularity of the spacemap bits). The table needed a Reorg
badly quite clearly and we decided to use Append, with MC just in
case. After the change, Lock counts dropped and performance
improved dramatically. Note that the Spacemap pages do not get
logical locks, only latches, or Page p-locks (the
Global latch). Was a very old table I think,so don't remember
if was a UTS or not, or whether Segmented (most likely) or
partitioned (unlikely). That was not quite the main issue for me at
the time. Row length varied a lot, but problem most likely was a
for a new frequent extra long row length.
V12 has added the extra subtlety of Insert Algorithm
2, to make sure concurrent inserters on same DS member are
trying to append to different pages instead of conditional lock
fighting for pages to Insert to, because MC is only separating the
cross member concurrent processes.
In the modern day we UTS, either partitioned by growth or range
partitioned, so IBM consider having segments to be better then
traditional partitioned spaces, I guess.
If an old partitioned space is having Insert performance
problems, would I convert it to UTS with segments? Not very likely.
I think there are other measures first, like tuning freespace,
freespace for update, MAXROWS, running Reorg, Append, MC, etc.