How do i configure my database to use optimal IO parallelism - the literature is not clear . Each of the tablespaces is spread across 10 containers and each container is spread across 4 physical disks. Should my prefetchsize be 40 times extent size ?? What needs to be setting for DB2_PARALLEL_IO ?
This is an OLTP system

Parallel I/O and parallel query are not the same thing.

To enable parallel IO for all tablespaces, you do the following:
db2set DB2_PARALLEL_IO=*
This should only be done if your tablespaces are on disk arrays that are on multiple physical drives.

There are a number of parmaters that must be set to enable parallel query, such as degree of parallelism. But for a true OLTP system, I would highly discourage using these, since parallel query in an OLTP environment with index access can sometimes slow things down considerably. Tthis assumes that most of your SQL uses indexes and comes back fairly quickly. THe only exception would be if you have a lot of SQL that does a complete table scan (like a data warehouse application).

Since you said that your tablespaces are spread accross 10 containers, then I wonder if you really have OLTP. If you do, then spreading them accross that many containers is not considered to be best practice for true OLTP.

But if you have a data warehouse with tablespaces that are spread accross 10 containers, and each container is an array of 4 drives, then your prefetch size should be 10 times the extent size (not 40).

Tks Marcus,
It is a mixed system mainly OLTP but some reporting is done too .However 10 containers was the recommended value given by peoplesoft.Is there a way to monitor the effect of changing the prefetchsize on these tablespaces ? How can i measure the improvement in prefetch /IO by altering the prefetchsize or implementing db2_parallel_io.

Prefetch will kill you unless you are doing a table scan. Prefetch starts reading multiple pages ahead of what you actually ask for and puts them in the buffer pool. If you never need the prefetch data (becasue you are reteiving only a small number of rows via an index), then you just flushed a lot of other needed pages out of the buffer pool.

In a mixed system, you need to determine whether you are going to optimize for the OLTP transactions or optimize for the decision support queries (it is difficult to optimize for both unless they each have their own tables). If you only have a few tables that need a complete table scan, then put them in a different tablespace and different buffer pool than everything else.

In order to monitor how much prefetch is helping you for decision support queries that do table scans (this is pretty much the only scenario where it could help) then you would need to run queries that read the entire table when no one else is using the system, otherwise there are too many other variables and applications contending for the same system resources to know which prefectch configuration is the best.

If you really think that Peoplesoft is so smart about how to configure DB2, then ask them what to do. But based on their advice as you have described it (and assuming it really is primarily OLTP), I am not sure I believe anything they say.