Up and running with DB2 UDB ESE partitioning for performance in an e-business intelligence world

Up and running with DB2 UDB ESE partitioning for performance in an e-business intelligence world

Data warehouses in the 1990's were for the privileged few business analysts. Business Intelligence is now being democratized by being shared with the rank and file employee demanding higher levels of RDBMS scalability and ease of use, being delivered through Web portals. To support this emergin...

Descripción completa

Detalles Bibliográficos
Autor Corporativo: International Business Machines Corporation. International Technical Support Center (-)
Otros Autores: Baragoin, Corinne (-)
Formato: Libro electrónico
Idioma:Inglés
Publicado: San Jose, CA : IBM Corp., International Technical Support Organization 2003.
Edición:1st ed
Colección:IBM redbooks.
Materias:
IBM Database 2.
Database management.
Data warehousing.
Electronic commerce.
Ver en Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009626880506719
Tabla de Contenidos:
  • 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.

Ejemplares similares