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 »
Recent Comments