IBM DB2 Universal database 重重 Administration Guide: Planning Version 8
IBM® DB2 Universal Database™ Administration Guide: Planning Version 8 SC09-4822-00
IBM DB2 Universal database 重重 Administration Guide: Planning Version 8
IBM® DB2 Universal Database™ Administration Guide: Planning Version 8 SC09-4822-00
efore using this information and the product it supports, be sure to read the general information under Notices This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. ToorderpublicationsonlinegototheIbMPublicationsCenteratwww.ibm.com/shop/publications/order To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-YOU (426-4968) When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incur ny obligation to you o Copyright International Business Machines Corporation 1993-2002. All rights reserved. US Government Users Restricted Rights- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Before using this information and the product it supports, be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968). When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. © Copyright International Business Machines Corporation 1993 - 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Contents About this book vii Chapter 4. Logical database design 47 Who should use this book What to record in a database How this book is structured Database relationships a brief overview of the other Administration One-to-many and many-to-one Guide volumes relationships Administration Guide: Implementation.. ix Many-to-many relationships Administration Guide: Performance One-to-one relationships Ensure that equal values represent the Part 1. Database concepts same entity Column definitions 900125 Chapter 1. Basic relational database Primary keys concepts Identifying candidate key columns Identity columns Configuration parameters Normalization 2556788 Business rules for data First normal form Second normal form Developing a Backup and Recovery Strategy 19 Third normal form Authentication Fourth normal form Authorization Multidimensional clustering Considerations when choosing MDC table dimensions Chapter 2 Parallel database systems Considerations when creating MDC tables Data partitioning Constraints Parallelism Unique constraints Input/output parallelism. Referential constraints Query parallelism Table check constraints 885 Partition and processor environments Triggers Single partition on a single processor Additional database design considerations Single partition with multiple processors 31 Chapter 5 Physical database design Multiple partition configuration ummary of parallelism best suited to each Database directories and files hardware environment Space requirements for database objec Space requirements for system catalog tables 882g9 Chapter 3. About data warehousing Space requirements for user table data What is data warehousing? Space requirements for long field data 39 space requirements for large object data Data warehouse objects 95%g Subject areas Space requirements for log files Warehouse sources Warehouse targets Space requirements for temporary tables 40 Database partition groups 102 Warehouse agents and agent sites Database partiti 104 Processes and steps Warehouse tasks Partitioning maps Partitioning keys Table collocation Part 2. Database design Partition compatibility o Copyright IBM Corp. 1993-2002
Contents About this book . . . . . . . . . . vii Who should use this book . . . . . . . viii How this book is structured . . . . . . viii A brief overview of the other Administration Guide volumes . . . . . . . . . . . ix Administration Guide: Implementation . . ix Administration Guide: Performance . . . x Part 1. Database concepts. . . . .1 Chapter 1. Basic relational database concepts . . . . . . . . . . . . .3 Database objects . . . . . . . . . . .3 Configuration parameters . . . . . . . 13 Business rules for data . . . . . . . . 15 Developing a Backup and Recovery Strategy 19 Security . . . . . . . . . . . . . 22 Authentication . . . . . . . . . . . 23 Authorization . . . . . . . . . . . 24 Chapter 2. Parallel database systems. . . 25 Data partitioning . . . . . . . . . . 25 Parallelism . . . . . . . . . . . . 26 Input/output parallelism. . . . . . . 26 Query parallelism . . . . . . . . . 26 Utility parallelism . . . . . . . . . 29 Partition and processor environments . . . 30 Single partition on a single processor . . 30 Single partition with multiple processors 31 Multiple partition configurations . . . . 33 Summary of parallelism best suited to each hardware environment . . . . . . . 37 Chapter 3. About data warehousing . . . 39 What is data warehousing? . . . . . . . 39 Data warehouse objects . . . . . . . . 39 Subject areas . . . . . . . . . . . 39 Warehouse sources . . . . . . . . . 40 Warehouse targets . . . . . . . . . 40 Warehouse agents and agent sites . . . . 40 Processes and steps . . . . . . . . 41 Warehouse tasks . . . . . . . . . . 43 Part 2. Database design . . . . . 45 Chapter 4. Logical database design . . . 47 What to record in a database . . . . . . 47 Database relationships . . . . . . . . 49 One-to-many and many-to-one relationships . . . . . . . . . . . 49 Many-to-many relationships. . . . . . 50 One-to-one relationships . . . . . . . 50 Ensure that equal values represent the same entity . . . . . . . . . . . 51 Column definitions . . . . . . . . . 52 Primary keys. . . . . . . . . . . . 54 Identifying candidate key columns . . . 55 Identity columns . . . . . . . . . . 56 Normalization . . . . . . . . . . . 57 First normal form . . . . . . . . . 58 Second normal form . . . . . . . . 58 Third normal form . . . . . . . . . 60 Fourth normal form . . . . . . . . 61 Multidimensional clustering. . . . . . . 62 Considerations when choosing MDC table dimensions . . . . . . . . . . . . 73 Considerations when creating MDC tables . . 77 Constraints . . . . . . . . . . . . 80 Unique constraints . . . . . . . . . 81 Referential constraints. . . . . . . . 81 Table check constraints . . . . . . . 84 Triggers . . . . . . . . . . . . . 85 Additional database design considerations . . 86 Chapter 5. Physical database design . . . 89 Database directories and files . . . . . . 89 Space requirements for database objects. . . 92 Space requirements for system catalog tables 93 Space requirements for user table data . . . 94 Space requirements for long field data . . . 95 Space requirements for large object data . . 96 Space requirements for indexes . . . . . 97 Space requirements for log files . . . . . 100 Space requirements for temporary tables . . 101 Database partition groups . . . . . . . 102 Database partition group design . . . . . 104 Partitioning maps . . . . . . . . . . 105 Partitioning keys . . . . . . . . . . 107 Table collocation . . . . . . . . . . 109 Partition compatibility . . . . . . . . 110 © Copyright IBM Corp. 1993 - 2002 iii
Replicated materialized query tables Transaction manager (TM) Table space design 112 Resource managers(rM) 7 System managed space 115 Resource manager setup 176 Database managed space 17 Database connection consideration Table space maps xa_open string formats 176 How containers are added and extended in xa_open string format for DB2 Version 7 DMS table spaces and later Rebalancing 124 xa_open string format for earlier versions 180 Without rebalancing(using stripe sets) 130 How containers are dropped and reduced in Updating host or iSeries database servers DMS table spaces 133 with an XA-compliant transaction manager 182 Comparison of SMS and DMS table spaces 136 Manually resolving indoubt transactions .. 182 Table disk I/O Security considerations for XA transaction Workload considerations in table space Configuration considerations for XA Extent size transaction managers Relationship between table spaces and buffer XA function supported by DB2 UDB 142 XA switch usage and location elationship between table spaces an Using the DB2 Universal Database XA abase partition groups switch Temporary table space design XA interface problem determination Catalog table space design XA transaction manager configuration Optimizing table space performance when Configuring IBM WebSphere Application data is on raid devices 147 Serve 191 Considerations when choosing table spaces Configuring IBM TXSeries CICS for your tables 149 Configuring IBM TXSeries Encina Configuring BEA Tuxedo Chapter 6. Designing Distributed Databases Part 3. Appendixes 197 Units of work Updating a single database in a transaction Using multiple databases in a single Appendix A Incompatibilities between releases transaction Updating a single database in a DB2 Universal Database planned multi-database transaction incompatibilities plating multiple databases in a System catalog information Utilities and tools DB2 transaction manager configuratio.. 158 Version 8 incompatibilities between releases 201 transaction DB2 transaction manager System Catalog Information 201 Updating a database from a host or iSeries Application programming Database security and tuning Utilities and tools Error recovery during two-phase commit 168 Error recovery if autorestart=off Connectivity and coexistence 214 tion parameters Chapter 7. Designing for XA-compliant transaction managers Version 7 incompatibilities between releases 219 171 X/Open distributed transaction processing Application Programming model Utilities and Tools Application program(AP) Connectivity and Coexistence IV Administration Guide: Planning
Replicated materialized query tables . . . 111 Table space design . . . . . . . . . 112 System managed space . . . . . . . . 115 Database managed space . . . . . . . 117 Table space maps . . . . . . . . . . 119 How containers are added and extended in DMS table spaces . . . . . . . . . . 123 Rebalancing. . . . . . . . . . . 124 Without rebalancing (using stripe sets) 130 How containers are dropped and reduced in DMS table spaces . . . . . . . . . . 133 Comparison of SMS and DMS table spaces 136 Table space disk I/O . . . . . . . . . 137 Workload considerations in table space design . . . . . . . . . . . . . 139 Extent size . . . . . . . . . . . . 141 Relationship between table spaces and buffer pools . . . . . . . . . . . . . . 142 Relationship between table spaces and database partition groups . . . . . . . 143 Temporary table space design. . . . . . 144 Catalog table space design . . . . . . . 146 Optimizing table space performance when data is on RAID devices . . . . . . . 147 Considerations when choosing table spaces for your tables . . . . . . . . . . . 149 Chapter 6. Designing Distributed Databases . . . . . . . . . . . . 153 Units of work . . . . . . . . . . . 153 Updating a single database in a transaction 154 Using multiple databases in a single transaction . . . . . . . . . . . . 155 Updating a single database in a multi-database transaction . . . . . . 155 Updating multiple databases in a transaction . . . . . . . . . . . 156 DB2 transaction manager . . . . . . 158 DB2 transaction manager configuration 159 Updating a database from a host or iSeries client . . . . . . . . . . . . . . 163 Two-phase commit . . . . . . . . . 165 Error recovery during two-phase commit 168 Error recovery if autorestart=off . . . . 169 Chapter 7. Designing for XA-compliant transaction managers . . . . . . . . 171 X/Open distributed transaction processing model. . . . . . . . . . . . . . 172 Application program (AP) . . . . . . 172 Transaction manager (TM) . . . . . . 174 Resource managers (RM) . . . . . . 175 Resource manager setup . . . . . . . 176 Database connection considerations . . . 176 xa_open string formats . . . . . . . . 176 xa_open string format for DB2 Version 7 and later . . . . . . . . . . . . 176 xa_open string format for earlier versions 180 Examples . . . . . . . . . . . 180 Updating host or iSeries database servers with an XA-compliant transaction manager . 182 Manually resolving indoubt transactions . . 182 Security considerations for XA transaction managers . . . . . . . . . . . . 185 Configuration considerations for XA transaction managers . . . . . . . . 186 XA function supported by DB2 UDB . . . 188 XA switch usage and location. . . . . 188 Using the DB2 Universal Database XA switch . . . . . . . . . . . . 189 XA interface problem determination . . . 190 XA transaction manager configuration . . . 191 Configuring IBM WebSphere Application Server. . . . . . . . . . . . . 191 Configuring IBM TXSeries CICS . . . . 191 Configuring IBM TXSeries Encina . . . 191 Configuring BEA Tuxedo . . . . . . 193 Part 3. Appendixes . . . . . . . 197 Appendix A. Incompatibilities between releases . . . . . . . . . . . . 199 DB2 Universal Database planned incompatibilities . . . . . . . . . . 200 System catalog information . . . . . 200 Utilities and tools . . . . . . . . . 200 Version 8 incompatibilities between releases 201 System Catalog Information . . . . . 201 Application programming . . . . . . 202 SQL . . . . . . . . . . . . . 207 Database security and tuning . . . . . 212 Utilities and tools . . . . . . . . . 213 Connectivity and coexistence . . . . . 214 Messages . . . . . . . . . . . 217 Configuration parameters . . . . . . 218 Version 7 incompatibilities between releases 219 Application Programming . . . . . . 219 SQL . . . . . . . . . . . . . 221 Utilities and Tools. . . . . . . . . 223 Connectivity and Coexistence . . . . . 223 iv Administration Guide: Planning