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.
- 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
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.
- The extents being prefetched are on different physical devices
- Multiple I/O servers are configured (num_ioservers).