You are right that with private sorts SHEAPTHRES is a soft limit. It means that is there is no enough memory for the SORTHEAP to serve the request, the database manager will use smaller memory chunks to give to your sort. However, the SORTHEAP in your case is way too small, that is why you get this error. Both of these parameters set the maximum: one as a sum for all agents, the other specify the memory pages that can be allocated by a single coordinating agent. The memory chunks that the engine gets can be smaller with the private sorts, if it cannot allocated the requested size, but if the SORTHEAP is too small (and it is a maximum) then you will get an error.
Ellen Klebaner-Reys Data Management Services Inovant - a Visa Solutions Company [EMAIL PROTECTED]/650-432-1746 m/s: 3125-1D -----Original Message----- From: Raghu Raman [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 6:45 AM To: [EMAIL PROTECTED] Subject: RE: [DB2EUG] Shared sort & private sort Hi all In our database the INTRA_PARALLEL parameter in dbm cfg is set to NO. So from both of Bruce/Reys explanation it looks like the database doesn't do shared sorts at all. Assuming if all the sorts are private, according to the document the limit of the value in SORTHEAP and SHEAPTHRES must be soft limit. But we get an error message SQL0955C Sort memory cannot be allocated to process the statement. This messages occurs once every 10/15 days. The SORTHEAP is set to 256 (4K) pages and SHEAPTHRES is set to 20000 (4K) pages. The physical memory in the harddisk is 8GB. The max number of connection in the Websphere connection pool is 30. So what could be the reason for that ? The document says reduce the SORTHEAP which we don't consider as appropriate since its only 256 pages. Do we need to set any AIX parameter. We tried to increase the ULIMIT value, but still we get this error message. Any help would be appretiated. -Ragu -- On Fri, 17 May 2002 18:03:11 Bruce Allen wrote: >Hi Ragu, >And, shared sorts occur when data comes from more than 1 agent, so only >when INTRA_PARALLEL is on. My understanding is that you can still get >private sorts when INTRA_PARALLEL is on. > >DB2 will decide when to use private or shared sort (see above). > >I suggest setting the parms for shared sorts, as certainly SHEAPTHRES is a >hard limit for shared sorts. > >HTH, >Bruce Allen > > >-----Original Message----- >From: Reys, Ellen [SMTP:[EMAIL PROTECTED]] >Sent: Friday, 17 May 2002 7:33 AM >To: 'Raghu Raman' >Cc: '[EMAIL PROTECTED]' >Subject: RE: [DB2EUG] Shared sort & private sort > >If your INTRA_PARALLEL parameter in dbm cfg is set to NO, then your sorts >are private. Otherwise, they are shared. I am not sure, what else >determines >whether you have private or shared sorts. > >Ellen Klebaner-Reys >Data Management Services >Inovant - a Visa Solutions Company >[EMAIL PROTECTED]/650-432-1746 m/s: 3125-1D > > > >-----Original Message----- >From: Raghu Raman [mailto:[EMAIL PROTECTED]] >Sent: Thursday, May 16, 2002 1:55 PM >To: [EMAIL PROTECTED] >Subject: [DB2EUG] Shared sort & private sort > > >Hi all, > >In DB2 what does it mean by shared sort and private sort. We have a >Websphere application connecting to DB2 6.1 FP7 on an AIX machine. > >How to determine what type of sort my application is using. Also in the >manual it says SORTHEAP and SHEAPTHRES parameter varies depending whether >its a shared or private sort. So we need to determine when an application >will use shared sort and when it will use private sort. > >Any help would be appretiated. > >Thnx. > >Regards, >Ragu > > >________________________________________________________ >Outgrown your current e-mail service? >Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS. >http://login.mail.lycos.com/brandPage.shtml?pageId=plus >- >::: When replying to the list, please use 'Reply-All' and make sure >::: a copy goes to the list ([EMAIL PROTECTED]). >*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] >*** For more information, check http://www.db2eug.uni.cc >- >::: When replying to the list, please use 'Reply-All' and make sure >::: a copy goes to the list ([EMAIL PROTECTED]). >*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] >*** For more information, check http://www.db2eug.uni.cc > > ________________________________________________________ Outgrown your current e-mail service? Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS. http://login.mail.lycos.com/brandPage.shtml?pageId=plus - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
