Using faster, more expensive storage devices for hot data and slower, less expensive storage devices for cold data optimizes the performance of the queries that matter most while helping to reduce overall cost.
This paper presents a strategy for managing a multi-temperature data warehouse by storing data on different types of storage devices based on the temperature of the data. It provides guidelines and recommendations for each of the following tasks:
- Identifying and characterizing data into temperature tiers.
- Designing the database to accommodate multiple data temperatures.
- Moving data from one temperature tier to another.
- Using DB2® workload manager to allocate more resources to requests for hot data than to requests for cold data
- Planning a backup and recovery strategy when a data warehouse includes multiple data temperature tiers
The content of this paper applies to data warehouses based on version 10.1 or later of DB2 Database for Linux, UNIX, and Windows.
For additional best practices to help you implement multi-temperature data management with earlier versions of the DB2 software, refer to the best practice article "Multi-temperature data management" located in the Resources section.
|Article in PDF format||DB2V10_Multi-Temperature_0412.pdf||1663KB||HTTP|
- Read the "Best Practices for multi-temperature data management"
- Learn more about Best Practices for Physical Database Design.
- Learn more about "Best Practices for Workload Management".
- Learn more about the best practices for "Building a Recovery Strategy for an IBM Smart Analytics System Data Warehouse".
- Learn more about best practices for "DB2 for Linux, UNIX, and Windows".
- Follow developerWorks on
- Watch developerWorks on-demand demos advanced functionality for
Get products and technologies
products in the way that suits you best: Download a product trial,
try a product online, use a product in a cloud environment, or spend a few
hours in the SOA Sandbox learning how to implement Service Oriented
- Participate in the discussion forum.
- Get involved in the My developerWorks
community. Connect with other developerWorks users while exploring
the developer-driven blogs, forums, groups, and wikis.
Jim Seeger has been a member of the DB2 Linux, UNIX, and Windows team since 2008, and worked on the development of relational databases for 23 years. He worked on the shared storage layer support in DB2 V9.8, and led the development of Multi-temperature Storage Management support in DB2 V10. While working at IBM over the past 10 years, he has worked on storage area network file systems, storage virtualization products, high availability offerings, and open standards storage management interfaces. Jim also worked at NCR, Informix, and EMC prior to joining IBM.
Karen McCulloch has been a member of the DB2 Linux, UNIX, and Windows team since 2002 with most of that time spent in the DB2 workload management area. She was a lead developer, first with DB2 Query Patroller and then with DB2 Workload Manager (WLM). For the past four years, in addition to being one of the DB2 engine developers focusing on WLM and monitoring, she is also the technical manager for the DB2 WLM development team.
Naresh Chainani is a software developer and has worked in DB2 for Linux, UNIX, and Windows development in IBM Beaverton for the past 10 years. His expertise spans several areas of DB2 with a particular emphasis on table partitioning and decimal floating-point. Recently, he led the development of several enhancements to the table partitioning feature in DB2. Prior to that, he led the development of the IEEE-compliant DECFLOAT data type. He frequently interacts with customers either to present table partitioning or provide consulting assistance related to table partitioning, as well as to seek valuable stakeholder feedback.
Kiran Chinta is a Software Developer and has worked in DB2 for Linux, UNIX, and, Windows development at IBM Beaverton lab since 2006. He contributed to the development of various projects including IEEE-compliant DECFLOAT data type, HADR Reads on Standby, and Multi-Temperature Warehouse. Since 2011 he has been providing HADR technical support and resolving high-severity problems for businesses. In this role, he sometimes suggests architectural changes and presents best practices to achieve the best performance for the HADR environment. At IOD 2011, Kiran presented the best practices for HADR over WAN as experienced by banking systems in China.
Aruna De Silva is a member of the Quality Assurance (QA) team for DB2 on Linux, Unix, and Windows product at the IBM Toronto software laboratory. Aruna is an IBM Certified Advanced Database Administrator for DB2 9. In addition, he holds numerous IT certifications from IBM and other vendors in database, hardware, and systems administration. Aruna is actively involved in DB2 product integration, implementing and testing complex product stacks, and designing and testing HA and DR solutions. He is also the key contact person for EMC Storage within IBM Information Management Software division. He holds a Bachelor of Science degree in Computer Science from York University.
Vincent Kulandai Samy is a DB2 kernel developer in IBM Beaverton Lab, working on DB2 for Linux, UNIX, and Windows kernel development for the past 10 years. He came to IBM as part of Informix acquisition. Prior to the Informix acquisition, he was working on Informix IDS and XPS database kernel. His areas of expertise are database kernel, DB2 HADR, Multi-Temperature Warehouse, recovery, backup and restore, Linux kernel internals and kernel debugging. He was the technical lead for DB2 HADR Reads on Standby feature, released in DB2 Version 97 Fix Pack 1. For the past three years, Vincent has also been championing several DB2 HADR adoptions and new sale/deployments through on-site customer visits, consultancy, customer advocacy. He had presented DB2 HADR/TSA customer success stories at IOD conferences with Fidelity Investments and PepsiCo in 2008 and 2010.