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).