DB2 V10.1 Multi-temperature data management recommendations

Data in a data warehouse can be classified according to its temperature. The temperature of data is based on how old it is, how often it is accessed, how volatile it is, and how important the performance of the queries that access the data is. Hot data is frequently accessed and updated, and users expect optimal performance when they access this data. Cold data is rarely accessed and updated, and the performance of the queries that access this data is not essential.

Jim Seeger (jseeger@us.ibm.com), Senior Software Engineer, IBM China

Jim SeegerJim 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 (karenam@ca.ibm.com), Manager - DB2 WLM Development, IBM China

Author photoKaren 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 (naresh@us.ibm.com), Software Developer, IBM

Author Photo: Naresh ChainaniNaresh 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 (kkchinta@us.ibm.com), Software Developer, IBM China

Author photoKiran 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 (adesilva@ca.ibm.com), Testing Support Specialist, IBM

Aruna De Silva  photoAruna 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 (vinci@us.ibm.com), Software Developer, IBM China

Photo of author VincentVincent 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.

30 April 2012

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.

In this article

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 formatDB2V10_Multi-Temperature_0412.pdf1663KB


Zone=Information Management
SummaryTitle=DB2 V10.1 Multi-temperature data management recommendations