Sort pool size

[login to unmask email]

Sort pool size
Hi all

I 'm trying to size the srtpool zparm correctly and decided to use the manual.
The manual gives a formula for srtpool size, but doesn't indicate what happens
when this storage limit is reached. What happens??

Tom



[login to unmask email]

Re: Sort pool size
(in response to ttaylor@CHUBB.COM)
If application code incurs a sort and the "run" (a string of sorted results
rows) does not complete within the SORTPOOL size, the run will be written to
DASD (a 4K work dataset) and later merged with the ensuing runs that complete
the sort. If I recall correctly, multiple SORTPOOL entities exist (one for each
concurrent application sort operation).

If you're not storage constrained, make SORTPOOL and a dedicated 4K work dataset
bufferpool large; use VPSEQT=98, DWQT=80, and VDWQT=60 as intial work bufferpool
parms.
The values for DWQT and VDWQT are to discourage what may be unecessary writes.
In a DB2 PM Statistics report, check the "workfile pages not written" value
against the "workfile pages to destruct" to see how much sorting is staying in
storage.

A rule of thumb that has worked fairly well for me is to make the work
bufferpool one to one and a half times the size of the tablespace bufferpool(s)
when a lot of cursor activity is expected.

We also have one large active database that uses a hiperpool to backup the work
bufferpool;
the jury is still out as to whether or not the hiperpool is that beneficial .

Good luck, George