The SRTPOOL subsystem parameter on macro DSN6SPRM defines the amount of sort work area an SQL SELECT statement can use. The number defined on this ZPARM is per SQL task. Bigger is always better if you are talking about only DB2 SQL performance. However, you must keep in mind that whatever value picked is used per SQL statement. That means that if you use the maximum value of 128000 KB (like I often try to do when doing a benchmark but of course, I’m not sure what I do is considered real world. It is definitely not best practices; be careful with the SRTPOL number you choose.), and 10 SQL requiring sort run simultaneously, each of those 10 SQL task has the potential of using 128000 KB of sort pool work area. A little common sense needs to be applied here. You are trading SQL performance for the possibility of a storage shortage. That’s why is is so important to make sure you always keep in mind that the sort pool value you are specifying with SRTPOOL is per concurrent SQL user…… Continue reading »
Just in case you haven’t seen the word sort enough yet today, here it comes again. I was sitting here on my day off trying to figure out which of the 9 work related task that are now behind schedule that I should be working on. Like all good IT people, a vacation day just means I get to wear my sweatpants around the house, I take a lot more coffee breaks, and Anthony seems to visit me far more often. Unfortunately, I decided not to work on anything I should be doing, and blog and Twitter instead. But enough of this rubbish and let’s talk RDS Sort… again. This evening installment is actually a post that should have started the entire series…>>>> Continue reading »
The final topic of discussion is the table spaces that are defined in support of the sort work files. Let’s start with what is probably the number one question in my opinion. Should a sort work file be defined with secondary extents? I have always recommended, as have many others that I know, that you should NOT specify a secondary extent for any of the table spaces defined to DSNDB07 (or what ever your sort work database it’s called if you are a data sharing shop). Secondaries will work with sorts but why have sort do extra work processing multiple extents. It can’t possibly be good for performance…>>>> Continue reading »
Part 3 of my little dissertation about RDS sort has arrived. This has been one very busy weekend publishing blog stuff that has sort in it. Today we talk about the buffer pools defined in support of sort…>>>> Continue reading »
I know this has taken months to publish. However, here is Part 2 in my RDS sort series. Hopefully Part 3 & 4 will make it a lot quicker.
In today’s installment I’ll be looking at SRTPOOL, the sort work area. I will be interested to hear from you all about how helpful this post is, if you need any other details. Buffer pools and the actual VSAM page sets that back the buffer pools will be the next to pieces…>>>> Continue reading »
It’s always nice to have your opinions validated. In my case, it’s the non use of secondary extents on DB2′s sort workfiles. I have always recommended, as have many others that I know, that you should NOT specify a secondary extent for any of the table spaces defined to DSNDB07 (or what ever your sort work database it’s called if you are a data sharing shop). Secondaries will work for sorts but why have sort processing multiple extents. It can’t possibly be good for performance…>>>> Continue reading »