When you create a table space, the extent size you set affects the amount of information stored in the buffer and therefore the performance of data access. The type of table access will affect how data is stored in the buffer pool from your tables, but if you are querying large amounts of data, prefetching that data can significantly boost your application's performance. Prefetching is the process by which pages of data pages are read and stored in memory in advance of their use by a query. Since these reads can be done while your database is performing other operations, perfecting significantly reduces your response time or latency of the query.
The buffer space is set using either the CREATE TABLESPACE or ALTER TABLESPACE command using the EXTENTSIZE and PREFETCHSIZE parameters. EXTENTSIZE sets the number of PAGESIZE pages that are written to a container; while the PREFECTSIZE is the number of pages read from the table space in a prefetch which is performed prior to the query. Set the PREFECTSIZE to size of the number of table space containers times the EXTENTSIZE. For 8 containers of 8 pages you would set it at 64 pages of data. The num_ioservers is the parameter that determines the number of threads of control that populate the buffer with prefetched information.
Other factors are also important in improving buffer performance. You use the BUFFERPOOL parameter to assign a buffer pool to tables in a table space. When this parameter isn't specified DB2 uses the default value found in IBMDEFAULTBP. The page size for the buffer pool must be the same as the page size for the table space. The OVERHEAD parameter is used to control the I/O controllers disk seek and latency time as expressed in milliseconds, providing control over I/O during a query. Use the TRANSFERRATE parameter to set the time used to read a page into the buffer, which is also a number in milliseconds. Both OVERHEAD and TRANSFERRATE essential set the cost of the I/O that is part of query optimization. You'll find a discussion of both of these parameters in Chapter 4 of the "DB2 Universal Administration Guide: Performance" book.
About the Author
Barrie Sosinsky (barries@sosinsky-group) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.