DB2 10.5 for Linux, UNIX, and Windows

dft_prefetch_sz - Default prefetch size configuration parameter

This parameter sets the default prefetch size of table spaces.

Configuration type: Database
Parameter type: Configurable Online
Propagation class: Immediate
Default [range]: Automatic [0 - 32 767]
Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.
Unit of measure: Pages

When a table space is created, PREFETCHSIZE can optionally be specified with a value of AUTOMATIC or n, where n represents the number of pages the database manager will read if prefetching is being performed. If you do not specify the prefetch size on invocation of the CREATE TABLESPACE statement, the database manager uses the current value of the dft_prefetch_sz parameter.

If a table space is created with the prefetch size set to AUTOMATIC, the DB2 database manager will automatically calculate and update the prefetch size of the table space.

This calculation is performed:

When the database starts
When a table space is first created with AUTOMATIC prefetch size
When the number of containers for a table space changes through execution of an ALTER TABLESPACE statement
When the prefetch size for a table space is updated to be AUTOMATIC through execution of an ALTER TABLESPACE statement

The AUTOMATIC state of the prefetch size can be turned on or off as soon as the prefetch size is updated manually through invocation of the ALTER TABLESPACE statement.

Recommendation: Using system monitoring tools, you can determine if your CPU is idle while the system is waiting for I/O. Increasing the value of this parameter can help if the table spaces being used do not have a prefetch size defined for them.

This parameter provides the default for the entire database, and it might not be suitable for all table spaces within the database. For example, a value of 32 might be suitable for a table space with an extent size of 32 pages, but not suitable for a table space with an extent size of 25 pages. Ideally, you should explicitly set the prefetch size for each table space.

To help minimize I/O for table spaces defined with the default extent size (dft_extent_sz), you should set this parameter as a factor or whole multiple of the value of the dft_extent_sz parameter. For example, if the dft_extent_sz parameter is 32, you could set dft_prefetch_sz to 16 (a fraction of 32) or to 64 (a whole multiple of 32). If the prefetch size is a multiple of the extent size, the database manager might perform I/O in parallel, if the following conditions are true:

The extents being prefetched are on different physical devices
Multiple I/O servers are configured (num_ioservers).