Database Partitioning, Table Partitioning, and Mdc for DB2 9

270 Pages·2007·2.71 MB·English

by  Whei-Jen Chen

Front cover
Database Partitioning,
Table Partitioning, and
MDC for DB2 9
Differentiating database partitioning,
table partitioning, and MDC
Examining implementation
examples
Discussing best
practices
Whei-Jen Chen
Alain Fisher
Aman Lalla
Andrew D McLauchlan
Doug Agnew
ibm.com/redbooks

International Technical Support Organization
Database Partitioning, Table Partitioning, and MDC
for DB2 9
August 2007
SG24-7467-00
Note: Before using this information and the product it supports, read the information in
“Notices” on pagevii.
First Edition (August 2007)
This edition applies to DB2 Enterprise Server Edition Version 9 for Linux, UNIX, and Windows.
© Copyright International Business Machines Corporation 2007. All rights reserved.
Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP
Schedule Contract with IBM Corp.
Contents
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .viii
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
The team that wrote this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
Acknowledgement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Become a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Chapter 1. Introduction to partitioning technologies. . . . . . . . . . . . . . . . . . 1
1.1 Databases and partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.1.1 Database concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2 Table partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.2.1 Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1.3 Multi-dimensional clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.3.1 Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Chapter 2. Benefits and considerations of database partitioning, table
partitioning, and MDC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
2.1 Database partitioning feature . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.1.1 The benefits of using database partitioning feature . . . . . . . . . . . . . 16
2.1.2 Usage considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2 Table partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.2.1 Benefits. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.2.2 Usage considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
2.3 Multi-dimensional clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.3.1 Benefits. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.3.2 Usage considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.4 Combining usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Chapter 3. Database partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.1 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
3.1.1 Supported operating systems and hardware . . . . . . . . . . . . . . . . . . 36
3.1.2 Minimum memory requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.2 Planning considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.2.1 Deciding on the number of database partitions. . . . . . . . . . . . . . . . . 38
3.2.2 Logical and physical database partitions . . . . . . . . . . . . . . . . . . . . . 39
3.2.3 Partition groups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
3.2.4 Distribution maps and distribution keys. . . . . . . . . . . . . . . . . . . . . . . 40
© Copyright IBM Corp. 2007. All rights reserved. iii
3.2.5 Table spaces and containers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3.2.6 Sizing the tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
3.2.7 Buffer pools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.2.8 Catalog partition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.2.9 Coordinator partition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.2.10 Data placement and table join strategies . . . . . . . . . . . . . . . . . . . . 44
3.3 Implementing DPF on UNIX and Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . 45
3.3.1 Creating instances and databases . . . . . . . . . . . . . . . . . . . . . . . . . . 46
3.3.2 Defining database partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.3.3 Setting up inter-partition communications. . . . . . . . . . . . . . . . . . . . . 48
3.3.4 Creating database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
3.3.5 Switching partitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
3.3.6 Adding database partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
3.3.7 Removing database partitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.3.8 Creating database partition groups. . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.3.9 Viewing partition groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.3.10 Redistributing partition groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
3.3.11 Altering database partition groups . . . . . . . . . . . . . . . . . . . . . . . . . 58
3.3.12 Dropping a database partition group. . . . . . . . . . . . . . . . . . . . . . . . 61
3.3.13 Implementing buffer pools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3.3.14 Implementing table spaces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
3.3.15 Implementing tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
3.4 Implementing DPF on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
3.4.1 Installing DB2 Enterprise 9 on Windows. . . . . . . . . . . . . . . . . . . . . . 71
3.4.2 Working with partitioned databases . . . . . . . . . . . . . . . . . . . . . . . . . 79
3.4.3 DB2 Remote Command Service. . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.5 Administration and management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.5.1 DB2 utilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.5.2 Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
3.5.3 Rebalancer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
3.6 Using Materialized Query Tables to speed up performance in a DPF
environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
3.6.1 An overview of MQTs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
3.6.2 When to consider a MQT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
3.6.3 When to use the MQT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
3.6.4 Intra-database replicated tables and partitioning . . . . . . . . . . . . . . 121
3.7 Best practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
3.7.1 Selecting the number of partitions. . . . . . . . . . . . . . . . . . . . . . . . . . 122
3.7.2 Distribution key selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
3.7.3 Collocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
iv Database Partitioning, Table Partitioning, and MDC for DB2 9
Chapter 4. Table partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
4.1 Planning considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
4.1.1 Roll-in and roll-out strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
4.1.2 Range selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
4.1.3 Handling large objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
4.1.4 Indexing partitioned tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
4.2 Implementing table partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
4.2.1 Creating a data partitioned table. . . . . . . . . . . . . . . . . . . . . . . . . . . 130
4.2.2 Adding a new partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
4.2.3 Detaching a partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
4.2.4 Re-attaching a partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
4.2.5 RANGE option. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
4.2.6 Handling large objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
4.2.7 Optimal storage configurations for table partitioning. . . . . . . . . . . . 156
4.2.8 Partition elimination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
4.3 Administration and management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
4.3.1 Utilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
4.3.2 DB2 Explain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
4.3.3 Locking considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
4.3.4 Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
4.3.5 Using partitioned tables in your existing database . . . . . . . . . . . . . 168
4.3.6 Authorization levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
4.4 Best practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Chapter 5. Multi-dimensional clustering. . . . . . . . . . . . . . . . . . . . . . . . . . 177
5.1 Planning for the use of MDC on a table . . . . . . . . . . . . . . . . . . . . . . . . . 178
5.1.1 Verify database configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
5.1.2 Determine query workload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
5.1.3 Identify dimensions and columns . . . . . . . . . . . . . . . . . . . . . . . . . . 179
5.1.4 Estimate space requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
5.1.5 Adjust design as needed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
5.1.6 DB2 Design Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
5.2 Implementing MDC on a table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
5.3 Administering and monitoring MDC tables . . . . . . . . . . . . . . . . . . . . . . . 188
5.3.1 Utilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
5.3.2 Monitoring MDC tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
5.3.3 Explain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
5.4 Application considerations for MDC tables . . . . . . . . . . . . . . . . . . . . . . . 193
5.5 Examples of using MDC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
5.5.1 Applying MDC to the TPC customer table . . . . . . . . . . . . . . . . . . . 194
5.5.2 Utilizing both dimension and row-level indexes . . . . . . . . . . . . . . . 201
5.5.3 Using the Control Center to run DB2 Design Advisor. . . . . . . . . . . 206
5.5.4 Using MDC to provide roll-out functionality. . . . . . . . . . . . . . . . . . . 211
Contents v
5.5.5 Using MDC on a fact table in a star schema warehouse . . . . . . . . 213
Chapter 6. Using database partitioning, table partitioning, and MDC
together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
6.1 Database partitioning and MDC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
6.2 Database partitioning and table partitioning . . . . . . . . . . . . . . . . . . . . . . 219
6.2.1 Logical representation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
6.2.2 Implementing a table using table partitioning and database partitioning
220
6.3 Table partitioning and MDC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
6.4 Database partitioning, table partitioning, and MDC. . . . . . . . . . . . . . . . . 231
Appendix A. Configuring DB2 for SSH in a partitioned environment. . . 235
A.1 Setting up public key authentication. . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
A.2 Setting up host-based authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
A.2.1 SSH server configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
A.2.2 SSH client configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
A.3 Configuring DB2 to use ssh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Appendix B. Additional material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Locating the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Using the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
System requirements for downloading the Web material . . . . . . . . . . . . . 244
How to use the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
IBM Redbooks publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
How to get Redbooks publications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
vi Database Partitioning, Table Partitioning, and MDC for DB2 9
Notices
This information was developed for products and services offered in the U.S.A.
IBM may not offer the products, services, or features discussed in this document in other countries. Consult
your local IBM representative for information on the products and services currently available in your area.
Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM
product, program, or service may be used. Any functionally equivalent product, program, or service that
does not infringe any IBM intellectual property right may be used instead. However, it is the user's
responsibility to evaluate and verify the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter described in this document.
The furnishing of this document does not give you any license to these patents. You can send license
inquiries, in writing, to:
IBM Director of Licensing, IBM Corporation, North Castle Drive, Armonk, NY 10504-1785 U.S.A.
The following paragraph does not apply to the United Kingdom or any other country where such
provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION
PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR
IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer
of express or implied warranties in certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made
to the information herein; these changes will be incorporated in new editions of the publication. IBM may
make improvements and/or changes in the product(s) and/or the program(s) described in this publication at
any time without notice.
Any references in this information to non-IBM Web sites are provided for convenience only and do not in any
manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the
materials for this IBM product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without
incurring any obligation to you.
Information concerning non-IBM products was obtained from the suppliers of those products, their published
announcements or other publicly available sources. IBM has not tested those products and cannot confirm
the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on
the capabilities of non-IBM products should be addressed to the suppliers of those products.
This information contains examples of data and reports used in daily business operations. To illustrate them
as completely as possible, the examples include the names of individuals, companies, brands, and products.
All of these names are fictitious and any similarity to the names and addresses used by an actual business
enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information contains sample application programs in source language, which illustrate programming
techniques on various operating platforms. You may copy, modify, and distribute these sample programs in
any form without payment to IBM, for the purposes of developing, using, marketing or distributing application
programs conforming to the application programming interface for the operating platform for which the
sample programs are written. These examples have not been thoroughly tested under all conditions. IBM,
therefore, cannot guarantee or imply reliability, serviceability, or function of these programs.
© Copyright IBM Corp. 2007. All rights reserved. vii
Trademarks
The following terms are trademarks of the International Business Machines Corporation in the United States,
other countries, or both:
Redbooks (logo) ® DB2 Connect™ Redbooks®
eServer™ DB2® System p™
pSeries® IBM® Tivoli®
zSeries® Lotus® 1-2-3®
AIX® POWER™
The following terms are trademarks of other companies:
Oracle, JD Edwards, PeopleSoft, Siebel, and TopLink are registered trademarks of Oracle Corporation
and/or its affiliates.
Solaris, and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other
countries, or both.
Excel, Microsoft, SQL Server, Windows, and the Windows logo are trademarks of Microsoft Corporation in
the United States, other countries, or both.
Intel, Itanium, Intel logo, Intel Inside logo, and Intel Centrino logo are trademarks or registered trademarks of
Intel Corporation or its subsidiaries 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, or service names may be trademarks or service marks of others.
viii Database Partitioning, Table Partitioning, and MDC for DB2 9