If the DB2 parameters are OK (I am not the best judge in that field, but they seem OK?) you might want to share the query / queries with us. Maybe they can be rewritten. It depends on the kind of problem you have:
- is it an existing application that 'all of a sudden' runs into problems?
- is it an existing application that has gradually developed your problem?
- is it a new application, running against existing data (of huge volumes) ?

Rob.


-----Oorspronkelijk bericht-----
Van: Maxim Ness [mailto:db2udbdba-ezmlmshield-x7147995.[Email address protected]
Verzonden: wo 26-10-2005 21:24
Aan: LazyDBA Discussion
Onderwerp: sort overflow, emergency

Hello DBA's,

Environment : DB2 UDB 1.6.1, windows 2000 adv server.

I am getting a huge amount of sort overflows for one application we are
running on DB2. The ratio is about 2 statements/ 1 overflow. Instance has
INTRA_PARALLEL ON, 1 GB of memory assigned to sheapthres, 40 mb assigned
to sheapthres_shr, 1.5 mg for SORTHEAP.

When i take a snapshot for applications i see that my custom program is
using shared memory to perform sorts. If sheapthres_shr is set to the hard
limit of 40 mg and it ran out of free space will i get an error in
db2diag.log ? Also in the snapshot for application how db2 reports shared
memory, in bytes or in db2 pages ?

What else can i do to eliminate the sort overflows ? Please help this is a
huge production issue.

Thank you in advance !





DB2 & UDB email list listserv db2-l LazyDBA home page