DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL

Aurora Emanuela Dellanno

DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL

Hey y'all,

 

there is some talk of changing our current values to "encourage" in-memory sort - we currently have the default-calculated values of 1000 for each of the ZPARMs, I have looked to see if anyone had discussed either to this effect.

I have read Rob Catterall's blog entry regarding this, but I was more interested in real life experiences.

 

Any thoughts?

 

Thanks.

 

Aurora

alain pary

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Aurora Emanuela Dellanno)

Hello, 

same for us , MAXSORT_IN_MEMORY= 1000 .

Alain

Terry Purcell

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Aurora Emanuela Dellanno)

I also welcome real-life experiences. In general I find that customers are not deviating much from Db2 zparm defaults unless there is strong evidence (documentation, presentations etc) to suggest the benefit.

MAXSORT_IN_MEMORY will control the size of a sort result that can be taken in-memory - more so than SRTPOOL.

However, for distributed access - the ODBC/JDBC defaults for WITH HOLD cursors can disable in-memory all together. So we have started advocating customers change these:

  • ODBC - Can be changed by setting CURSORHOLD=0 in db2cli.ini file
  • JDBC - Can be changed by setting resultSetHoldability=2

And of course, this question is on Db2 11. Db2 12 further enhances in-memory sorting (provided ODBC/JDBC is not adding WITH HOLD).

Regards

Terry Purcell

Aurora Emanuela Dellanno

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Aurora Emanuela Dellanno)

Thanks, Alain and Terry.

 

any further input gratefully received.

 

Aurora

Andy Smith

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Aurora Emanuela Dellanno)

Aurora

For SRTPOOL tuning, we have previously used IFCID 096 (QW0096KL and QW0096WR) and used the following formula to determine a suitable value for SRTPOOL:

SRTPOOL = 32’000 * (16 + QW0096KL  + QW0096WR)

What we found is that 5 MB was sufficient for most sorts, with just a few outliers up in the 70 MB range. 

So we left SRTPOOL at 5 MB.

What we haven't done since v11 is look at MAXSORT_IN_MEMORY and so this is still at the 1 MB default.

I need to understand more about the relationship between SRTPOOL, MAXSORT_IN_MEMORY and IFCID 096, and whether or not having MAXSORT_IN_MEMORY at 1 MB effectively caps SRTPOOL at 1MB too.

Can anybody advise here please?

Thanks

Andy

Terry Purcell

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Andy Smith)

Andy,

What MAXSORT_IN_MEMORY signifies is the size at which the sort will be permitted to remain in memory. It doesn't dictate or cap SRTPOOL size. If MAXSORT_IN_MEMORY is 1MB and your sort result is 2MB - SRTPOOL will still use 2MB (since it's less than your 5MB SRTPOOL), but the sort result will be written to workfile because it is larger than 1MB. And your application will fetch the result from that workfile. If your sort result was < 1MB - the final sort can be kept in memory and your application will fetch the result from that memory. So you can avoid workfile for that sort.

Increasing MAXSORT_IN_MEMORY doesn't result in extra memory being used (because it uses that SRTPOOL memory). It may however keep that memory longer - since the memory is then kept until that cursor closes. If it doesn't fit in memory and is written to WF - that memory is freed immediately and the WF is kept until cursor close.

In V11 it is for final sort only. For V12 it extends to many of the intermediate sorts. V12 has many other sort enhancements - including increasing the limit on 32,000 nodes to 128,000 for parallel child tasks or 512,000 nodes for parent or non-parallel. So V12 allows you to increase SRTPOOL and potentially allow larger sorts to occur in one pass. Increasing MAXSORT_IN_MEMORY in V12 should see more sorts avoid WF also - provided they aren't WITH HOLD (see my earlier response).

Regards

Terry Purcell

Aurora Emanuela Dellanno

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Aurora Emanuela Dellanno)

thanks, Andy and Terry. I'll get busy with ye ole SMF records.

 

thanks everyone!

 

Aurora

Andy Smith

RE: DB2 z/OS 11 NFM: MAXSORT_IN_MEMORY and SRTPOOL
(in response to Terry Purcell)

Thanks for explaining this Terry.  I think I will increase MAXSORT_IN_MEMORY from 1MB to 5MB (to match our SRTPOOL).  I don't see any issue (in our environment) with it keeping the memory longer.