Hot Spots Beware

by Arlyn R
According to Lockwood Lyon’s (2012) article, “Strategies for Improved Data Availability in DB2,” important database design decisions can maximize availability and eliminate hot spots by creating indexes, partitioning, applying a routine archival, and implementing referential integrity in the application code. Hot spots are areas in the database that are heavily accessed and changed which cause deadlocks and timeouts. In order to evade hot spots, the author advises to set up an index for columns that are most frequently searched. Horizontal partitioning increases database availability because it frees up space table rows that are most accessed preventing contention of data. The author suggests to partition data for the current day so that purge processes will not interfere with new insertions. Lyon also advises to enforce referential integrity in the application code instead of storing it in the database. Referential integrity data code can take up more space and processing time. If implemented in the application code, user can be cautioned to perform an action that may take additional processing time.

While transforming the conceptual diagram into the logical database, it reveals the pros and cons of our design decisions. I found this article relevant to our current chapter as it highlights design strategies to create a database that performs to its maximum capacity. Although the article referred to the specific relational database, DB2, the strategies seemed to be applicable to relational databases in general.

As informative as this article was, some strategies seemed more like common sense. Such as Lyon’s suggestion to purge data to partitions separate from operational data. However, situations were presented that had some real-world applications and gave insight on the complexities of database design. The author also introduced some common industry vernacular that were new to me, yet described them well. The article was easy to understand even from my novice point of view.

Lyon, L. (2012, August 23). Strategies for Improved Data Availability in DB2. Retrieved from Retrieved on October 21, 2012.