How do you decide what EXTENTSIZE and PREFETCHSIZE should be set to? Please provide suggestions for OLTP and DSS systems. I'm still confused about the two parameters.

DB2 9.1 on AIX

You can now set prefetch size to automatic in 9.5 (not sure about 9.1). But normally it should be extent size times number of containers in the tablespace.

Prefetch (which are sort of look-ahead reads to get data into the bufferpool before pages are actually requested) should only be a factor if you have queries that do table scans (hopefully not OLTP applications).

Generally, you will want smaller extent sizes for OLTP and larger for Data Warehouses, but it should be an even multiple of the RAID stripe size (1x, 2x, etc).

I think I understand PREFETCHSIZE, but I'm still unclear about EXTENTSIZE.

EXTENTSIZE - the number of pages written to a tablespace container before writing to the next container. Based on this definition, EXTENTSIZE setting does affect how DB2 is reading data from the disk, only how it's writing it to the disk.

Am I correct? What is the reason behind using smaller EXTENTSIZE for OLTP and larger for Data Warehouse?

With a data warehouse, you are usually reading and writing larger chunks of data at one time. But if the amount of data that is being read is smaller, you still want it spread over multiple containers to improve performance.

I understand about reading data - table scans will benefit from a larger PREFETCHSIZE.

But in a data warehouse, we do not do a lot of writing, do we? I don't see how larger EXTENTSIZE is beneficial.

I guess I'm still confused about what EXTENTSIZE is and what effect this parameter has on queries that mostly do table scans.

You write a lot of data at one time when data is loaded to the data warehouse, and you read a lot of data (usually) when you do queries against a data warehouse. DB2 does the physical access to disk in extent chunks. If the amount of data read or written at one time is significantly less than the extent size, you may be wasting some resources, although I have never seen hard numbers on the actual performance difference.

For OLTP, the exact opposite is true compared to a DW (in terms of of the amount of data for reading and writing). But in both cases (OLTP and DW), you want to spread to data across multiple containers (if you have multiple containers in the tablespace) to reduce spindle contention. But there is a quirk here, since DB2 tries to avoid synchronous I/O to disk, and disk writes are deferred to when a page cleaner comes along later and writes the data from the bufferpool to disk, sometimes long after the SQL insert, update, or delete. The page cleaners try to write as much data at one time for a given tablespace, so it is possible that more data than you realize is being written at one time.

Thanks for clearing my doubts. Very helpful.