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