Q and A

Asked and Answered

I thought that the DB2 v.7 for z/OS ZPARMS SRTPOOL and MAXRBLK referred to the global respective sort spaces, but I recently read somewhere that they refer to SORT and RID SORT pools for each individual user. Mine are each set to 8,000 (4K, pages). If what I read is true, I'm worried that I'm crowding below the line (number of users times 8,000k - and possibly * 2 if a RID sort is also involved). Can you clarify?

Robert Catterall responds:

One of the parameters (MAXRBLK) is global in nature; the other (SRTPOOL) isn't. Check out this section of the DB2 for z/OS and OS/390 Version 7 Installation Guide pointed to by this URL.

There, you will see the following text (emphasis on the key word added):

The DB2 sort work area (in-memory) has the following storage boundaries for each concurrent sort operation:
* Minimum sort storage = 240KB
* Maximum sort storage = 64MB

A maximum sort pool size set to 32MB is on the high side, but you might have some big sort operations. Note that this storage is allocated above the 16MB line, so it won't lead to "crowding" below the line. Also, the number of users isn't so important — it's the number of users concurrently executing queries that involve sorts that's important.

You might consider reducing the value of SRTPOOL now that you know it's related to space used for individual SQL-related sort operations.

I wouldn't worry about having a buffer for every workfile tablespace page in a pool buffer pool dedicated to DSNDB07 — that would probably be overkill. A pool of a few thousand buffers is probably sufficient. You might want more if the system's primary workload is of a business intelligence nature (versus batch or OLTP).

MAXRBLK is global in nature. If you look at the section of the DB2 Installation Guide pointed to by this URL.

You will find this text (emphasis added on the key word):

Estimate the storage required for the RID pool with the following formula: Number of concurrent RID processing activities * average number of RIDs * 2 * A (bytes per RID).

See a complete archive of reader/author Q&As .