Front cover -- Contents -- Figures -- Tables -- Examples -- Notices -- Trademarks -- Preface -- The team that wrote this redbook -- Become a published author -- Comments welcome -- Chapter 1. e-Business Intelligence and DB2 UDB ESE -- 1.1 Business issues and challenges -- 1.1.1 Build larger and larger data warehouses -- 1.1.2 Load and populate the data warehouse as fast as possible -- 1.1.3 Perform queries with performances -- 1.1.4 Operate in a 24 hours/7 days mode -- 1.1.5 Manage the data warehouse -- 1.1.6 Upgrade the data warehouse -- 1.1.7 Summary -- 1.2 DB2 UDB V8.1 functions for e-Business Intelligence -- 1.2.1 Performance enhancements -- 1.2.2 Availability enhancements -- 1.2.3 Manageability enhancements -- 1.2.4 UNION ALL views -- 1.3 New functionalities of IBM e-server p690 -- 1.3.1 Hardware overview -- 1.3.2 Logical partitioning for OLTP-aware e-BI -- 1.3.3 Cluster 1600 -- 1.3.4 Enhancements with AIX V5.2 -- 1.3.5 Workload management -- 1.4 Case study and technical environment -- 1.4.1 The case study based on TPC-H -- 1.4.2 Technical environment -- Chapter 2. Build a large data warehouse -- 2.1 Steps for building a large data warehouse -- 2.2 Disk considerations -- 2.2.1 Summary of the most popular RAID levels -- 2.2.2 Data placement -- 2.2.3 Log placement -- 2.2.4 Data availability and performance -- 2.2.5 General storage performance recommendations -- 2.3 Database design considerations -- 2.3.1 Understand data partitioning -- 2.3.2 Define the number of database partitions -- 2.3.3 Define database partition groups -- 2.3.4 Design the table spaces -- 2.3.5 Understand partitioning map -- 2.3.6 Choose the partitioning key -- 2.3.7 Size the tables -- 2.3.8 Size for MDC utilization -- 2.3.9 Size for MQT utilization -- 2.3.10 Configure DB2 UDB -- 2.3.11 Recommended parameters for performance.

2.4 Installing and configuring DB2 UDB ESE V8.1 -- 2.4.1 Generic checklist to install and configure DB2 UDB ESE V8.1 -- Chapter 3. LOAD and populate the data warehouse in parallel -- 3.1 Initial mass load -- 3.1.1 Online LOAD -- 3.1.2 Loading the case study data -- 3.1.3 Some considerations when using LOAD -- 3.1.4 Multipartition LOAD support -- 3.1.5 Partitioning LOAD subagents in DB2 UDB V8.1 -- 3.1.6 LOAD QUERY command -- 3.2 Incremental updates and LOADs -- 3.2.1 Deleting data -- 3.3 LOAD and real-time notion -- Chapter 4. Speed up performance with MultiDimensional Clustering -- 4.1 What is MultiDimensional Clustering? -- 4.1.1 Overview of the clustering index -- 4.1.2 Introducing MultiDimensional Clustering (MDC) -- 4.2 Design guidelines for MDC tables -- 4.2.1 Step 1: Identify dimension candidates -- 4.2.2 Step 2: Evaluate storage requirements -- 4.3 Creating a MDC table -- 4.4 Using a MDC table -- 4.4.1 Insert processing -- 4.4.2 Delete processing -- 4.5 MDC and multipartition database -- 4.6 Performance -- 4.7 Benefits of MDC -- 4.8 Considerations and recommendations -- 4.9 MDC design prototype -- Chapter 5. Speed up performance with Materialized Query Tables -- 5.1 MQTs overview -- 5.2 When to consider a MQT? -- 5.3 When will the MQT be used? -- 5.4 Intra database replicated tables and partitioning -- 5.5 MQT and MDC? -- Chapter 6. Enhance query performance in a partitioned environment -- 6.1 Query performance and the DB2 compiler -- 6.2 Communication between partitions -- 6.2.1 Table queues -- 6.2.2 Table queue concepts -- 6.2.3 Local bypass -- 6.3 Join partitioning strategies -- 6.3.1 Collocated joins -- 6.3.2 Directed joins -- 6.3.3 Broadcast joins -- 6.3.4 Repartitioned joins -- 6.4 Join planning using the join partitioning strategies -- 6.4.1 Replicated table joins -- 6.5 Sort and aggregation parallelization.

6.5.1 Sort parallelization -- 6.5.2 Aggregation parallelization -- 6.6 Statistics in a partitioned environment -- 6.7 Analyzing query plans in a partitioned environment -- Chapter 7. Improve 24x7 operations with new online utilities -- 7.1 Availability -- 7.2 Online table load -- 7.2.1 Improved methods of loading -- 7.2.2 Improved table space and table access while loading -- 7.2.3 Why, how, and when these features can be used -- 7.2.4 Load wizard utility -- 7.3 Online and classic reorganization of tables -- 7.3.1 Why reorganize the data in a table? -- 7.3.2 How does an online reorganization benefit my environment? -- 7.3.3 What benefits will REORG by database partition provide? -- 7.3.4 When should the indexes on a table be reorganized? -- 7.3.5 Is there a way to monitor the REORG? -- 7.3.6 Is there a way to control the REORG? -- 7.3.7 Are there availability improvements for the classic REORG? -- 7.4 Index enhancements -- 7.4.1 How do the changes to the create index and rename help? -- 7.4.2 What are type-1 and type-2 indexes, and what do they mean? -- 7.4.3 Conversion of type-1 indexes to type-2 indexes -- 7.4.4 Online index reorganization -- 7.5 Online configuration parameters updates -- 7.5.1 Online bufferpool activities -- 7.5.2 Online changes to configuration parameters -- 7.5.3 How does online configuration improve availability? -- 7.6 Improve availability through backup/recovery plan -- 7.6.1 Backup and restore options -- 7.6.2 DB2 backup utility -- 7.6.3 Incremental backups -- 7.6.4 Backup using split mirror in conjunction with DB2 and ESS -- 7.6.5 DB2 full and incremental restore -- 7.6.6 Restore using split mirror in conjunction with DB2 and ESS -- 7.6.7 Parallel recovery -- 7.7 Additional ways to improve availability -- 7.7.1 DMS container operations -- 7.7.2 Online inspect command -- Chapter 8. Manage the data warehouse easily.

8.1 Self Managing And Resource Tuning -- 8.2 Health Monitor -- 8.3 New wizards -- 8.3.1 Memory Visualizer -- 8.3.2 Storage Management -- 8.3.3 Configuration Advisor -- 8.3.4 Design Advisor -- 8.4 AUTOCONFIGURE command -- Chapter 9. Upgrade to DB2 UDB ESE environment -- 9.1 Upgrade considerations -- 9.1.1 Upgrade recommendations -- 9.1.2 Upgrade restrictions -- 9.1.3 Backing up databases before DB2 UDB upgrade -- 9.1.4 Space considerations for DB2 UDB upgrade -- 9.1.5 Recording system configuration for DB2 UDB upgrade -- 9.1.6 Changing the diagnostic error level before DB2 UDB upgrade -- 9.1.7 Verifying that your databases are ready for migration -- 9.1.8 Understanding DB2 V8.1 objects terminology -- 9.2 64-bit considerations -- 9.2.1 Why 64-bit support? -- 9.2.2 64-bit restrictions on DB2 UDB V8.1 -- 9.2.3 64-bit client/server compatibilities -- 9.2.4 64-bit configuration on DB2 UDB V8.1 -- 9.2.5 How to check if the DB2 UDB is 32-bit or 64-bit -- 9.3 Upgrade procedure -- 9.3.1 Environment used in the examples -- 9.3.2 From V7.2 32-bit to v8.1 32-bit on the same machine -- 9.3.3 From V7.2 32-bit to V8.1 32-bit on different machine -- 9.3.4 From V8.1 32-bit to v8.1 64-bit on the same machine -- 9.3.5 Upgrade strategies examples -- Appendix A. Using DB2 LOAD for the case study -- Planning the load -- Load the multipartition tables -- Load the single partition tables, region, and nation -- Run SET INTEGRITY -- Appendix B. MDC dimension analyzer -- Overview of the dimension analyzer -- The script evaluate_dimensions.ksh -- How average row size is obtained -- The skeleton SQL file, evaluate_dimensions.sql -- A real example of use -- Create a duplicate of the lineitem table -- First cut -- Second cut, generated columns -- Creating lineitem as MDC table -- Summary of results -- Listing of evaluate_dimensions.ksh.

Appendix C. Additional 64-bit migration considerations -- Environments that should not be migrated to Version 8.1 -- Discontinued and deprecated functions -- Appendix D. DB2 UDB configuration parameters -- Database manager configuration parameters summary -- Database configuration parameters summary -- Appendix E. Additional material -- Locating the Web material -- Using the Web material -- System requirements for downloading the Web material -- How to use the Web material -- Related publications -- IBM Redbooks -- Other resources -- Referenced Web sites -- How to get IBM Redbooks -- IBM Redbooks collections -- Index -- Back cover.