What does your statement look like? If it's an UPDATE/DELETE/INSERT/MERGE
do you have a recursive trigger?
Also, depending on your OS you may have a ulimit (quota) that is cutting
you of which needs changing.

Cheers
Serge

Serge Rielau
DB2 Solutions Development
DB2 Universal Database for Linux, Unix, Windows
IBM Toronto Lab
Tel.: (905) 413 3907 Internet: [Email address protected]
Fax: (905) 413 4840 T/L: 969 3907





ighiurea
<[Email Address Removed]
anet.net> To
"[Email address protected]
07/30/2005 08:01 <[Email address protected]
PM cc
Serge Rielau/Toronto/[Email Address Removed]
Subject
Re: Heap sort












I increase the stmheap to 64MB but the users are still getting the error
(statement to long or complex - SQLSTATE 54001) or warning (statement had
sub-optimnal optimization, reason "1",
SQLSTATE 01602).
What 's next any tips, ideas ...??


Isabella

the Serge Rielau wrote:
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address
as normal
**Anything below this line is the original email text


The statement heap has nothing to do with query execution.
This heap is needed to compile a query.
If it is too low you may get a -101 (Statement to long or too
complex) or a
+437 warning
(query may perform suboptimal because the optimizer ran out of space)
during compilation of a query.

Cheers
Serge

"ighiurea " [Email address protected] wrote
on 07/30/2005 10:46:42 AM:


I have another question related to stmpheap and sortheap
values...I
increased the value for stmpheap to 16384(4k pages)=64MB buty
ther users
are still complainig about geetting the erros/waring and the
long
queries are failing ...how mauch shlud I increase the sortheap
values or
shoud I continue increasuing the stmpheap......



thanks,


Which platform are you on.

With Unix/AIX, you can see the values by using command :

db2 get db cfg | grep STMTHEAP
db2 get db cfg | grep SORTHEAP

To update values, use command:

db2 update db cfg using STMTHEAP <value>
db2 update db cfg using SORTHEAP <value>





Divakar


-----Original Message-----
From: PremnathJ
[mailto:db2udbdba-ezmlmshield-x86475952.[Email address
protected]

Sent: Wednesday, July 30, 2003 12:10 AM
To: LazyDBA Discussion
Subject: Heap sort

dba's can someone help me how to view the heap sort size
in db2 and
increase its size.


I need to increase the stmtheap size also.


How can this be done?

Thanks
Prem.


---------------------------------------------------------------------

PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




Confidentiality Notice


The information contained in this electronic message and
any

attachments to this message are intended

for the exclusive use of the addressee(s) and may contain

confidential or privileged information. If

you are not the intended recipient, please notify the
sender at

Wipro or [Email address protected] immediately

and destroy all copies of this message and any
attachments.


---------------------------------------------------------------------

PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html








---------------------------------------------------------------------

PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html










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