Hi,
I've got a question. We just increased our SORTHEAP from 4096 to 25600 and
our SHEAPTHRESH from 80000 to 262144.
Now we saw a SQL955 error occuring but no reason code attached to it.
According to the help, this error occurs if there is insufficient private
process memory (there are no errors in /var/adm/messages
regarding running out of memory and no messages in db2diag.log).
Or if there is insufficient shared memory in the database-wide shared memory
area.
Now the SHEAPTHRESH is supposed to be a soft limit for private sorts and as
I understand we should not get SQL0955 back for private sorts since it will
just mean that our sort will overflow.
This leaves that this is for shared sorts. But from what I understand,the
shared sort is a hard limit but is seperate from the private sort
correct?
Meaning that from the moment we're using close to 262144 for private memory
that new private sorts will get less sortheap and thus higher
change to overflow.
But even if we would be using 262144 for private heap, we could then still
use 262144 for shared sorts correct?
If this is so, then how come we did not get SQL955 errors before we
increased the values?
Also, the statement it failed on was a small statement. The OPT level for
this statement is 5 and Parallelism is set to NO.
The estimated bufferpool numbers for the SORT in the explain is 2.
So this looks to me to have a very high chance to be a private sort and if
so, why do we get the SQL0955 on this?
Thank you. 4 3011
I also need to add that INTRA_PARALLEL is set to YES but DFT_DEGREE is set
to 1 annd
the application is for this statement using DEGREE 1.
"Erik Hendrix" <he**********@h otmail.com> wrote in message
news:19******** *************** *******@news.te ranews.com... Hi,
I've got a question. We just increased our SORTHEAP from 4096 to 25600 and our SHEAPTHRESH from 80000 to 262144.
Now we saw a SQL955 error occuring but no reason code attached to it. According to the help, this error occurs if there is insufficient private process memory (there are no errors in /var/adm/messages regarding running out of memory and no messages in db2diag.log). Or if there is insufficient shared memory in the database-wide shared
memory area. Now the SHEAPTHRESH is supposed to be a soft limit for private sorts and
as I understand we should not get SQL0955 back for private sorts since it
will just mean that our sort will overflow. This leaves that this is for shared sorts. But from what I understand,the shared sort is a hard limit but is seperate from the private sort correct? Meaning that from the moment we're using close to 262144 for private
memory that new private sorts will get less sortheap and thus higher change to overflow. But even if we would be using 262144 for private heap, we could then still use 262144 for shared sorts correct? If this is so, then how come we did not get SQL955 errors before we increased the values?
Also, the statement it failed on was a small statement. The OPT level for this statement is 5 and Parallelism is set to NO. The estimated bufferpool numbers for the SORT in the explain is 2. So this looks to me to have a very high chance to be a private sort and if so, why do we get the SQL0955 on this?
Thank you.
Erik,
Did you increase you DBHEAP in line with the increase in SORTHEAP ?
Phil
Erik Hendrix wrote: I also need to add that INTRA_PARALLEL is set to YES but DFT_DEGREE is set to 1 annd the application is for this statement using DEGREE 1.
"Erik Hendrix" <he**********@h otmail.com> wrote in message news:19******** *************** *******@news.te ranews.com... Hi,
I've got a question. We just increased our SORTHEAP from 4096 to 25600 and our SHEAPTHRESH from 80000 to 262144.
Now we saw a SQL955 error occuring but no reason code attached to it. According to the help, this error occurs if there is insufficient private process memory (there are no errors in /var/adm/messages regarding running out of memory and no messages in db2diag.log). Or if there is insufficient shared memory in the database-wide shared memory area. Now the SHEAPTHRESH is supposed to be a soft limit for private sorts and as I understand we should not get SQL0955 back for private sorts since it will just mean that our sort will overflow. This leaves that this is for shared sorts. But from what I understand,the shared sort is a hard limit but is seperate from the private sort correct? Meaning that from the moment we're using close to 262144 for private memory that new private sorts will get less sortheap and thus higher change to overflow. But even if we would be using 262144 for private heap, we could then still use 262144 for shared sorts correct? If this is so, then how come we did not get SQL955 errors before we increased the values?
Also, the statement it failed on was a small statement. The OPT level for this statement is 5 and Parallelism is set to NO. The estimated bufferpool numbers for the SORT in the explain is 2. So this looks to me to have a very high chance to be a private sort and if so, why do we get the SQL0955 on this?
Thank you.
No, we did not. But we have no messages in the db2diag.log file either.
"Philip Nelson" <te*****@scotdb .com> wrote in message
news:dX******** **************@ news.easynews.c om... Erik,
Did you increase you DBHEAP in line with the increase in SORTHEAP ?
Phil
Erik Hendrix wrote:
I also need to add that INTRA_PARALLEL is set to YES but DFT_DEGREE is
set to 1 annd the application is for this statement using DEGREE 1.
"Erik Hendrix" <he**********@h otmail.com> wrote in message news:19******** *************** *******@news.te ranews.com... Hi,
I've got a question. We just increased our SORTHEAP from 4096 to 25600 and our SHEAPTHRESH from 80000 to 262144.
Now we saw a SQL955 error occuring but no reason code attached to it. According to the help, this error occurs if there is insufficient
private process memory (there are no errors in /var/adm/messages regarding running out of memory and no messages in db2diag.log). Or if there is insufficient shared memory in the database-wide shared memory area. Now the SHEAPTHRESH is supposed to be a soft limit for private sorts
and as I understand we should not get SQL0955 back for private sorts since it will just mean that our sort will overflow. This leaves that this is for shared sorts. But from what I
understand,the shared sort is a hard limit but is seperate from the private sort correct? Meaning that from the moment we're using close to 262144 for private memory that new private sorts will get less sortheap and thus higher change to overflow. But even if we would be using 262144 for private heap, we could then still use 262144 for shared sorts correct? If this is so, then how come we did not get SQL955 errors before we increased the values?
Also, the statement it failed on was a small statement. The OPT level for this statement is 5 and Parallelism is set to NO. The estimated bufferpool numbers for the SORT in the explain is 2. So this looks to me to have a very high chance to be a private sort and if so, why do we get the SQL0955 on this?
Thank you.
Check you current query optimization level. It is probably defaulting to 7.
Try setting it to 5 for the troublesome query. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mohammed Mazid |
last post by:
Can anyone please help me on how to move to the next and previous
question?
Here is a snippet of my code:
Private Sub cmdNext_Click()
End Sub
Private Sub cmdPrevious_Click()
showrecord
|
by: Mohammed Mazid |
last post by:
Can anyone please help me on how to move to the next and previous
question?
Here is a snippet of my code:
Private Sub cmdNext_Click()
End Sub
Private Sub cmdPrevious_Click()
showrecord
|
by: John Wilson |
last post by:
Hello,
I have the following code which populates as table data from a SQL
Server 2000 stored proc (RSByDemoID2). Below that is the view and
stored procedure which takes @DemoID as input to match to the
event_id. For Q? and Comments I am viewing/updating in a different
table than I am question and how_to_answer. The stored proc is
populated by a view that I'm using to get all these values from two
tables. My quandry is, I am getting the...
|
by: perseus |
last post by:
I think that everyone who told me that my question is irrelevant,
in particular Mr. David White,
is being absolutely ridiculous. Obviously, most of you up here
behave like the owners of the C++ language. A C++ interface
installation IS ABOUT THE C++ LANGUAGE! The language
does not possess the ability to handle even simple file directory
manipulation.
Those wise people that created it did not take care of it. So, BOOST
is a portable...
|
by: Li Kao |
last post by:
After incrementally increasing SORTHEAP (and the commensurate increase
in SHEAPTHRES) and *slightly* reducing the bufferpool size, I find
that my query is no longer taking advantage of async IO for reads. I
discovered this message in the db2diag.log:
====================================
PID:80850(db2agntp (LIKAO) 0) Appid:*LOCAL.db2inst1.031121211302
sort/list_services sqlsOptimizeNumMergeRuns Probe:10
Database:LIKAO
| |
by: ChrisHadley |
last post by:
My simple sortheap configuration question is:
the sortheap parameter specifies the amount of memory used by agents
for sorts. Is this value the total for all agents or for each?
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 3000
SQL statement heap (4KB) (STMTHEAP) = 3000
|
by: p175 |
last post by:
Hi folks,
I'm after some guidelines on general memory allocations for DB2 8.2.2
ESE on Win2k Server FP4 with 4gb physical memory.
Seeing as everything is 32bit and win2k server does not support /3gb
switch, we are restricted memory wise for allocating memory to
db2syscs.exe to around 1.75gb so I understand, what are the general
rules of thumb for assigning memory ?
|
by: Ekqvist Marko |
last post by:
Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?
table before
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 answer to the question 06f
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |