To enable prefetching, the Database Manager starts separate threads of control, known as I/O servers, to perform page reading. As a result, the query processing is divided into two parallel activities: data processing (CPU) and data page I/O. The I/O servers wait for prefetch requests from the CPU processing activity. These prefetch requests contain a description of the I/O needed to satisfy the anticipated data needs. The reason for prefetching determines when and how the Database Manager generates the prefetch requests. (See Understanding Sequential Prefetching and Understanding List Prefetching for more information.)
The following figure illustrates how I/O servers are used to prefetch data into a buffer pool.
Figure 93. Prefetching Data using I/O Servers
![]() |
The following steps are illustrated in Figure 93:
Configuring enough I/O servers with the num_ioservers configuration parameter can greatly enhance the performance of queries for which prefetching of data can be used. Having some extra I/O servers configured will not hurt performance because extra I/O servers are not used and their memory pages will get paged out. Each I/O server process is numbered and the Database Manager will always use the lowest numbered process that is available and, as a result, some of the upper numbered processes may never be used.
To determine the number of I/O servers that you should configure, consider the following:
To maximize the opportunity for parallel I/O, set num_ioservers to at least the number of physical disks in the database.
For situations in which multiple containers exist for a table space, the Database Manager can initiate parallel I/O. Parallel I/O refers to the ability of the Database Manager to use multiple I/O servers to process the I/O requirements of a single query. Each I/O server is assigned the I/O workload for a separate container, allowing several containers to be read in parallel. Performing I/O in parallel can result in significant improvements to I/O throughput.
While a separate I/O server will handle the workload for each container, the actual number of I/O servers that can perform I/O in parallel will be limited to the number of physical devices over which the requested data is spread. This also means you need as many I/O servers as the number of physical devices.
How parallel I/O is initiated and used is dependent on the reason for performing the I/O:
For sequential prefetch, parallel I/O is initiated when the prefetch size is a multiple of the extent size for a table space. Each prefetch request is then broken into multiple, smaller, requests along the extent boundaries. These smaller requests are then assigned to different I/O servers.
For list prefetch, each list of pages is divided into smaller lists according to the container in which the data pages are stored. These smaller lists are then assigned to different I/O servers.
For backing up or restoring data, the number of parallel I/O requests are equal to the backup buffer size divided by the extent size up to a maximum value equal to the number of containers.
For restoring data, the parallel I/O requests are initiated and split in a manner that is the same as that used for sequential prefetch. Instead of restoring the data into the buffer pool, the data is moved directly from the restore buffer to disk.
When loading data you can specify the level of I/O parallelism with the LOAD command's DISK_PARALLELISM option. (If it is not specified, a default is used based on the cumulative number of table space containers for all table spaces associated with the table.)
For optimal performance of parallel I/O, ensure that:
If one or more containers are smaller than the others, they will reduce the potential for optimized parallel prefetch. For example:
Prefetching must occur at higher rates to use I/O capacity effectively. The prefetch size should be higher for prefetching to occur at higher rates. The prefetch size should be a multiple of the extent size and the number of table space containers. Ideally, containers should be configured to reside on separate physical drives.
The number of physical drives required could depend on the speed and capacity of the drives and the I/O bus, and on the speed of the processors.
SMS table spaces are expanded on demand. This expansion is done a single page at a time by default. However, in certain work loads (for example, when doing a bulk insert) you can increase performance by using the db2empfa tool to tell DB2 to expand the table space in groups of pages or extents. The db2empfa tool is located in the bin subdirectory of the sqllib directory. Running it causes the multipage_alloc database configuration parameter to be set to YES. For more information on this tool, refer to the Command Reference.
Another way to make the best use of your available memory is discussed in Extending Memory.