Performance parameters in db2

[arunrocks]

Hi
Everyone looks at some 5 parameters if they are going to deal with
db performance, like the sortheap, stmt heap, appl heap etc. I just
have one small doubt(big it may be).. If I give a value of say 64000 -
4k pages to sortheap, does it mean I am giving 256 megs for the db as
a whole to use for sorting or am I giving 256 Megs for each sort
operation? I have this doubt from the first time I read about the
parameters and ppl have given me both answers.
Thanks
Arun

[The Boss]

ar*******@gmail.com wrote:

Hi
Everyone looks at some 5 parameters if they are going to deal with
db performance, like the sortheap, stmt heap, appl heap etc. I just
have one small doubt(big it may be).. If I give a value of say 64000 -
4k pages to sortheap, does it mean I am giving 256 megs for the db as
a whole to use for sorting or am I giving 256 Megs for each sort
operation? I have this doubt from the first time I read about the
parameters and ppl have given me both answers.
Thanks
Arun

Hi Arun,

check the Info Center, both on "sortheap":
http://publib.boulder.ibm.com/infoce...n/r0000259.htm
and on "sheapthres":
http://publib.boulder.ibm.com/infoce...n/r0000260.htm
and maybe also on "sheapthres_shr":
http://publib.boulder.ibm.com/infoce...n/r0006014.htm

HTH.

--
Jeroen

[swami]

Hi,

Sortheap is a database level parameter.
so it is not for each operation. it is for db as a whole.
On Apr 7, 2:42 am, "The Boss" <[e-mail]:

[e-mail] wrote:

Hi
Everyone looks at some 5 parameters if they are going to deal with
db performance, like the sortheap, stmt heap, appl heap etc. I just
have one small doubt(big it may be).. If I give a value of say 64000 -
4k pages to sortheap, does it mean I am giving 256 megs for the db as
a whole to use for sorting or am I giving 256 Megs for each sort
operation? I have this doubt from the first time I read about the
parameters and ppl have given me both answers.
Thanks
Arun

Hi Arun,

check the Info Center, both on "sortheap":http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2...
and on "sheapthres":http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2...
and maybe also on "sheapthres_shr":http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2...

HTH.

--
Jeroen

[Ian]

swami wrote:

Hi,

Sortheap is a database level parameter.
so it is not for each operation. it is for db as a whole.

You are incorrect. Please read the documentation that Jeroen referenced
below.

The fact that it is defined in the database configuration only means
that the setting applies each sorts that take place in the database.

[arunrocks]

On Apr 7, 9:04 am, Ian <[e-mail]:

swami wrote:

Hi,

Sortheap is a database level parameter.
so it is not for each operation. it is for db as a whole.

You are incorrect. Please read the documentation that Jeroen referenced
below.

The fact that it is defined in the database configuration only means
that the setting applies each sorts that take place in the database.

Hah.. I thought I was asking dumb question.. It is not

[james_dey]

On 6 Apr, 21:12, [e-mail] wrote:

Hi
Everyone looks at some 5 parameters if they are going to deal with
db performance, like the sortheap, stmt heap, appl heap etc. I just
have one small doubt(big it may be).. If I give a value of say 64000 -
4k pages to sortheap, does it mean I am giving 256 megs for the db as
a whole to use for sorting or am I giving 256 Megs for each sort
operation? I have this doubt from the first time I read about the
parameters and ppl have given me both answers.
Thanks
Arun

Arun, the manual usually says that each private sort will allocate up
to 256Mb. i.e. if you have 4 concurrent private sorts, then it can use
up to 1Gb of memory. From what I can tell, by monitoring sort memory,
in most situations, the sort will use all (not up to) the SORTHEAP
parameter.