Physical database design for data warehouse environments
This paper provides best practice recommendations that you can apply when designing a physical data model to support the competing workloads that exist in a typical 24×7 data warehouse environment.
It also provides a sample scenario with completed logical and physical data models. You can download a script file that contains the DDL statements to create the physical database model for the sample scenario.
This paper targets experienced users who are involved in the design and development of the physical data model of a data warehouse in DB2 Database for Linux, UNIX, and Windows or IBM® InfoSphere® Warehouse Version 9.7 environments.
Short URL for this page: https://ibm.biz/Bdx2nr
Date: September 20, 2012
Share this page with colleagues and friends: Click on the buttons on the right of this page to share this page on your favorite social networks.
- Read the "Choosing partitioning keys in DB2 Database Partitioning Feature environments" developerWorks article
- Learn more about table partitioning keys in the DB2 for Linux, UNIX, and Windows Information Center
- Learn more about dimensional schemas in the Rational Information Center
- Learn more about how storage management has been improved in the DB2 Version 10.1 for Linux, UNIX, and Windows Information Center
- Read the "Database storage" developerWorks best practices article
- Read the "Storage optimization with deep compression" developerWorks best practices article
- Read the "Query optimization in a data warehouse" developerWorks best practices article
- Read the "DB2 10.1 Multi-temperature data management recommendations" developerWorks best practices article
- Read the "Temporal data management with DB2" developerWorks best practices article
- Read the "Physical database design for online transaction processing (OLTP) environments" developerWorks best practices article
- Read the "Getting started with IBM InfoSphere Data Architect" PDF
- Read the Upgrading to DB2 Version 10.1 roadmap
- Learn more about the DB2 database product documentation
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends
- Learn more about the Information Management best practices.
- Learn more about the DB2 for Linux, UNIX, and Windows best practices.
- Visit the DB2 Version 10.1 Information Center.
About the Authors
|When Maksym Petrenko worked for IBM, he worked exclusively with DB2 software. As part of his career he was developer, technical support analyst, lab services consultant and beta enabler. His experience included supporting clients with installation, configuration, application development, and performance issues related to DB2 Databases on Windows, Linux, and UNIX platforms. Maksym is a certified DB2 Advanced Database Administrator and DB2 Application Developer.|
|Amyris Rada is a senior writer with the DB2 for Linux, UNIX, and Windows product team at the IBM Canada Lab in Markham, Ontario. She has been part of the DB2 team since 1998, and has held different positions in partner enablement, quality assurance, and information development. She holds a Bachelor of Computer Engineer from the Simon Bolivar University. She is currently responsible for several content areas for the DB2 Information Center and collaborates with DB2 best practices development. Before working for IBM, Amyris worked at KL Group and INTERGRAPH.|
|Garrett Fitzsimons is a Data Warehouse Best Practices Specialist based in the IBM Dublin lab. He has worked with transactional and data warehouse database applications since 1990 on a variety of vendor platforms. Garrett is currently focused on developing, publishing, and promoting best practice guidelines for all areas of data warehousing and is dedicated to helping customers implement data warehouse and business analytics solutions on InfoSphere Warehouse and the IBM Smart Analytics System.|
|Enda McCallig is a DB2 Data Warehouse QA Specialist in Information Management in the IBM Dublin Lab. He joined IBM in 2009 having previously worked as a database consultant in the banking sector. Since 1997 Enda has worked on database development, design and administration on a variety of platforms. His work is currently focused on testing warehouse features on very large data warehouse environment.|
|Calisto Zuzarte is Senior technical Staff Member (STSM) in the IBM Canada Lab. He serves on the Distributed Database Architecture Board (DDAB) as a DB2 architect and manages the DB2 Compiler continuous engineering team. He specializes in Database Query Optimization. He is also a Research Staff Member (RSM) in the Centre for Advanced Studies (CAS) at the lab overseeing collaborative Information Management projects between IBM and academia.|