Hi Folks

I see lots of following messages in my db2daig.log

Not enough memory available for a (private) sort heap of size 30000
Trying smaller size...

2004-08-19-04.15.18.502035 Instancermm60 Node:017
PID:13729894(db2agntp (DPR060MM) 17) Appid:*N0.prmm60.040819072836
sort/list_services sqlsAllocateSortMemory Probe:35 DatabasePR060MM

This is a EEE (21 nodes ) V7 fixpack8 on AIX v5

I have SHEAPTHRES=200000
and SORTHEAP=30000 on each node.

I have 300 sort overflows.

How to find out the best value for my sortheap and sortheapthres

Any Clue !!!!! PLz

Figure out how many concurrent sort you have accross all partitoins. Then you'll see take that number * your sortheap to get your sheapthres. If you don't have enough memory, you may want to lower your sortheap, but the way you have it you can have only have 6 full sorts running in memory at the same time using the full amount it can grab. Try doubling your sheapthres and keep adding until it goes away from there. We have 12 partitions, or sortheap is set to 25000, and our sheapthres is set to 600000, we still have sort overflows, but it seems to be the correct balance.



but it look like if your doing a large sort against a table, that is accross all 12 partitions, it allocating sortheap on each partitoin for just that query, that would leave only 200000/12 for each sort, but the parms is set to use more.