The Extent Size specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container and is defined at table space creation time (and cannot be easily modified after). Smaller tables are handled more efficiently with smaller extents.
Rule of thumb is based on the average size of a table in the table space:
Less than 25 MB, use an Extent Size of 8
Between 25 and 250 MB, use an Extent Size of 16
Between 250 MB and 2 GB, use an Extent Size of 32
Greater than 2 GB, use an Extent Size of 64
Use larger values for OLAP databases and tables that are mostly scanned (query only) or have a very high growth rate.
If the table space resides on a disk array, set the extent size to the stripe size (that is, data written to one disk of the array).
Prefetch size can be changed easily using ALTER TABLESPACE. The optimal setting seems to be: Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size
If the table space resides on a disk array, set it as: PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array).
DB2 v8 Documentation:
Concepts ==> Administration ==> Database design ==> Physical ==> Table Space Design
Reference ==> SQL ==> SQL Statements ==> CREATE TABLESPACE
Reference ==> SQL ==> SQL Statements ==> ALTER TABLESPACE