SQL0955C Sort memory cannot be allocated to process the statement: HELP!

[Patrick Lynch]

I am getting this error occasionally ever since we changed some memory
configuration parameters.

ODBC Error Code = 57011 ()
[IBM][CLI Driver][DB2/NT] SQL0955C Sort memory cannot be allocated to
process the statement. Reason code = "". SQLSTATE=57011

System: Quad Xeon HP with 1.5 Gig RAM, W2K, DB2 7.1 FP3. I have been
trying to get the system to use more(!) memory, as the db2syscs.exe
never takes more than 350 mb, and it appears I've done something to
the sort memory. What parameters on the instance or db would cause
this? Also does anyone know of a documnet that clearly explains the
memory settings of DB 7, how the these settings effect each other, and
recommendations for running a database for a Internet application?
TIA.

P.S. I ran the performance Wizard on the db FYI.

[Pierre Saint-Jacques]

This s probably due to either of:
sortheap - db cfg parm. determines max. amount of space a stmt. that
does sort can request to hold the sorted result set (temporary or final).
sheapthresh - dbm cfg parm. controls total amount of sortheap space that
can be allocated accross all db's of the instance.
SQL0955C
Explanation: Insufficient virtual memory is available to the
database agent for sort processing, as indicated by the reason
code:

1 Insufficient private process memory.

2 Insufficient shared memory in the database-wide shared memory
area designated for sort processing.

The statement cannot be processed but other SQL statements may be
processed.

User Response: One or more of the following:

o Decrease the value of the sort heap parameter (sortheap) in
the corresponding database configuration file.

o For reason code 1, increase the private virtual memory
available, if possible. For example, on UNIX systems you can
use the ulimit command to increase the maximum size of the
data area for a process.

o For reason code 2, increase the size of the database-wide
shared memory area designated for sort processing by
increasing the value of the (sheapthres) database manager
configuration parameter.

sqlcode: -955

sqlstate: 57011

So what you're probably encountering could be:
1) A stmt. is trying to get DB2 to allocate the sortheap for its private
memeoty and it can't
2) 1) From the Web appls. there are too many stmts. concurrently
requesting probably the max. sortheap for their sorts and the grand
total pushes this to sheapthresh value.
For good info. see following

>DB2 UDB V7.1 Performance Tuning Guide
>
http://www.redbooks.ibm.com/redbooks/SG246012.html

HTH, Pierre

[Haider Rizvi]

See the Chapter 27 of Admin Guide starting with the section "How DB2
Uses Memory". For specific OS issues, read the Quick Beginning Guide
for Windows.

Typically the largest use of memory should be divided up between the
bufferpool and the sort areas.

Regards,
--
Haider

[Bob Harbus]

I would also check the DB2 diagnostic log file for messages related to sort
heap ... with respect to SHEAPTHRESH this may or may not be a hard limit
depending on the value of INTRA_PARALLEL for your configuration. If it is not
a hard limit (INTRA_PARALLEL=OFF) then you should likely be seeing messages
in the DB2 diagnostic log file telling you that your sort heap size could
not be allocated and therefore DB2 is trying a smaller size. If it is a hard
limit, you will not be able to allocate any sort heap once the SHEAPTHRESH is
reached.

Remember also that on HP-UX the shared memory limit is much smaller than on
other platforms so if you are running with INTRA_PARALLEL=ON then you may be
using much more memory than you think ...

--

Bob Harbus - Senior Consultant
DB2 UDB Data Management Consulting Services - IBM Toronto