By Paul C. Zikopoulos
The Version 8.2.2 update of the IBM DB2 Universal Database product (DB2 UDB) includes a new capability that allows database administrators (DBAs) to use a single page size for their entire database.
Page Sizes in DB2 UDB
DB2 UDB supports four different page sizes: 4 KB, 8 KB, 16 KB, and 32 KB. Many IBM clients like to take advantage of the flexibility in size by which the database manager can store data on disk and retrieve that data into its buffer pools. Depending on the type of application, using a different page size can prove very beneficial. For example, OLTP-like applications would typically benefit from smaller pages sizes since operations tend to be small and quick-running transactions (like a new order entry into a table that tracks orders). At the same time, workloads based on business intelligence (BI) may benefit from larger pages sizes because more data can be stored on a page that is often scanned or sorted, and so on. Getting more data in a single unit of I/O if that data is likely related, can prove very beneficial. In addition to this, clients can choose to leverage a larger page size to support tables that exceed 64 GB in size, as an alternative to partitioning the table.
So Why Use a Uniform Page Size?
With the potential benefits outlined in the previous section, some database administrators (DBAs) may wonder why they would want to use a uniform page size in their databases. To explain the potential benefits of doing so, it is helpful to describe how the DB2 UDB product behaves--before and after V8.2.2.
Before DB2 UDB V8.2.2, when you created a database, three separate table spaces were automatically created by default: SYSCATSPACE, TEMPSPACE1, and USERSPACE1. Before DB2 UDB V8.2.2, the SYSCATSPACE table space was hard-coded internally such that it was always created with a 4 KB page size -- a page size that you cannot change. In addition to this, DB2 UDB always required a system temporary table space with a 4 KB page size too. For these reasons, a default buffer pool (called IBMDEFAULTBP) was created that supported a 4 KB page each and every time a new database was created.
This meant that if a DBA wanted to leverage a different page size to obtain performance and flexibility benefits for their database, they were forced to deal with a mixed-page environment, which, for some DBAs, can involve administrative complexities.
For example, let's assume you are a DBA who wants to use an 8 KB page size for your entire database because it is a good balance for your mixed-workload environment. To support the 8 KB uniform page size before DB2 UDB V8.2.2, the DBA would have to drop the default USERSPACE1 table space and create a new table space with the preferred 8 KB page size. This would also result in the creation of a buffer pool. However, this approach only solves the page size associated with where newly created tables will be placed. There is no way to change the SYSCATSPACE table space since it is hard-coded into the database creation statements. In addition to this, DB2 UDB required that at least one 4 KB temporary table space must always exist in a DB2 UDB environment. So, before DB2 UDB V8.2.2, a DBA who wanted to have a uniform page size for their database environment was forced to manage a minimum of two pages sizes.
Uniform Page Sizes in DB2 UDB V8.2.2
You could say that before DB2 UDB V8.2.2, a benefit for one DBA could represent a complexity for another -- and vice-versa. DB2 UDB V8.2.2 adds the ability to create a database with an initial page size other than 4 KB (you can choose any page size that DB2 UDB supports). This gives DBAs the ability to choose any page size they want, and still maintain a single buffer pool and uniform page size across the database. Quite simply, there is no longer a requirement to always have a temporary table space with a 4 KB page size, nor is there an automatic hard-coding of the CREATE TABLESPACE statement in the CREATE DATABASE statement that results in a 4 KB system table space.
When you create a database with a uniform page size, only one buffer pool, matching this page size, is created. For example, if you create a new database with an 8 KB page size, the default buffer pool for all the table spaces will be 8 KB.
The ability to create a database with a uniform page size is only available through the CREATE DATABASE CLP command and the corresponding SQLCREA() API -- the Control Center and its associated wizards do not give you this option in DB2 UDB V8.2.2 (although this may change in a future release).
You can tell if a new database in DB2 UDB V8.2.2 was created with a uniform page size by inspecting the database configuration file using the DB2 GET DB CFG FOR <database_name> command, as shown in the following figure:
You can see that a database called EIGHT was created with a uniform page size that is 8 KB -- as indicated by the Data page size = 8192 field shown.
One Final Consideration
If you plan to use this feature, you should be aware of one consideration. If your environment is characterized by queries that require a large number of small rows in temporary tables, or if you have large tables with small index keys, you may want to consider maintaining a 4 KB-based table space.
The record identifier (RID) used in all versions of DB2 UDB for a while -- including V8.2.2 -- is 4 bytes (3 byte page number + 1 byte slot). Because of this slot size, there is a limit of 255 rows that can fit onto a data page in DB2 UDB, regardless of the row size or page size. Temporary tables with smaller row sizes do not waste a lot of space with a 4 KB page size.
Finally, a uniform page size can in fact increase the performance of your application. If you're not taking advantage of varying page sizes, then having a uniform paging environment can save the overhead of memory fragmentation when data is assembled from different sizes memory pools and returns to the client application. So, you may want to 'play' around to see the trade-offs of a mixed page and uniform page environment. The answer is 'it depends' - and I'll cover that in a future article.
Wrapping it Up
The DB2 UDB V8.2.2 update is full of database management features inspired by our close partnership with SAP. Features such as a uniform page size (and more) have found their way into the DB2 UDB product based on the feedback IBM receives from the SAP laboratories and SAP customers. Not using SAP? Don't worry -- you don't have to use SAP to leverage the benefits in the latest update. After all, we all care about scalability, availability, and manageability, don't we?
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: firstname.lastname@example.org.
DB2, DB2 Universal Database, and IBM are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2005. All rights reserved.
The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.