Hi Woody,

One thing to bear in mind is setting INTRAPARALLEL to yes doesn't
automatically force DB2 to use the shared sort heap. For example, our
database is a mixed environment. The reason why our SHEAPTHRES is set
so high is because of our concurrent private sort heaps during certain
points of our end of day. (During the day, the concurrent private sort
heaps don't reach anywhere near SHEAPTHRES). We have INTRAPARALLEL set
to yes at all times here, but during the day, we exclusively use
private sorting. During the end of day, some reports use the shared
sort heap and others use private sort heap. Those reports which use
the shared sort heap do so by setting current degree = <degree of
parallelism> before executing the query. You can see if your shared
sort heap has neared your SHEAPTHRES at any times since you restarted
your database by looking at the "Shared Sort heap high water mark"
which you'll find from your "get snapshot for db on <database>"
snapshot monitor.
2 other alternatives are to do:-

1.  "db2mtrk -i -w" and look for the shsorth value (this gives you the
figure in real memory rather than pages).
2. "db2pd  -db <database> -mempools" and look under the LogHWM or
PhyHWM columns where the Poolname = 'shsorth'

If you're wondering if you can find total private sort heap using
db2mtrk and db2pd, as far as I'm aware you can't.

You can see if anything's using the shared sort heap currently by
looking at "Total Shared Sort heap allocated" in the snapshot monitor
mentioned above.

You can see what applications are likely to be using shared sort heap
by typing "db2 list applications show detail" and looking for those
which are executing and were "No. of agents" are greater than 0.

Remember that for shared sort heaps, SHEAPTHRES is a hard limit. If
your shared sorts exceed this parameter, they'll fail. I'm guessing
that you will then see something in the db2diag.log if this happens,
but we've not been in that situation. As mentioned previously, you'll
get errors in your db2diag.log if your total private sort heap
allocation exceeds SHEAPTHRES but this is a soft limit, your next
agent requiring a sort won't fail, it will be just allocated less and
less SORTHEAP memory so it's performance will be impacted.

Hopefully, that's answered all your questions.

Hi Woody, I believe your understanding of the 32-bit DB2 memory model
to be correct. I've also read in the DB2 manuals that the db2 engine
will allocate up to SORTHEAP amount of memory for each sort and not
the whole SORTHEAP straightaway. All I can say is that when my
SHEAPTHRES was being exceeded, the errors in the db2diag.log suggested
that the DB2 engine had tried to allocate an entire SORTHEAP worth of
memory to the next sort, had failed and then tried to allocate a
SORTHEAP/2 worth of memory etc. My current understanding of the manner
in which DB2 sorts works is by examining and graphing data stored in
my monitoring tool over time.

The other thing that I didn't point out is that when you have
INTRAPARALLEL=yes, then, as you say, both private and shared sort
memory can be allocated. SHEAPTHRES should therefore be set to a value
greater than total_shared_sort_heap_allocated (db snapshot) +
Private_sort_heap_allocated (dbm snapshot) at a given point in time.
You obviously need to take regular snapshots and store them somewhere,
in order to determine this. Once you've seen when your max sorting is
taking place, the next logical thing would be to find out what
statements are using the sort memory. This conversation could be a
long one.