Bookmark and Share

Recent Posts

How DB2 11 Manages Ever-Increasing Amounts of Data

October 20, 2015

While many customers have upgraded to DB2 11, not all of them are taking full advantage of features like the DB2 Analytics Accelerator. While I've written extensively about the accelerator and other new capabilities that help customers manage high volumes of data, for an in-depth look at these topics, check out this recently released IBM Redbook.

I'll just highlight a few things in this document. For starters, there's the information about  temporal tables. The Redbook provides great examples of the impact of Insert, Delete, Updates and sample SELECT statements that return the correct data for a given time period. You'll also learn about changes in DB2 11, such as new special registers and support for using views with temporal tables.

The section on archive transparency brings back memories of the more complex REORG DISCARD technique, which was used to purge rows from active tables and then load the discarded rows into archive tables. The SQL logic in the REORG job was complicated and seemed to change constantly, which required DBAs to modify the SQL control card statement used by the REORG utility. On top of that, DBAs had to maintain separate active and archive tables, which added complexity to the application logic because multiple SQL statements were required to process some business conditions.

However, the new archive transparency implemented in DB2 11 eliminates the complexity of having to write two distinct SQL statements to process both active and archive data. The table that contains current rows is called an archive-enabled table, and the table holding the preexisting rows is called an archive table. Now applications can design the business rules to DELETE data from the enabled-archive table, and DB2 will insert these rows into the archive table. Existing REORG jobs can continue to REORG DISCARD and use LOAD RESUME to populate the archive table.

In addition, reading the data in the enabled-archive and archive table no longer requires two separate SELECT statements. You can now select against the enabled-archive table (current data) and DB2 will automatically read the related archive table. Using the new built-in global variable, SYSIBM.GET_ARCHIVE_DB2, you can have DB2 read the archive table. When SYSIBM.GET_ARCHIVE_DB2 is set to “Y,” DB2 transforms the select statement on the enabled archive table with a union all on the archive table.

The final sections are about the accelerator and how it can be used for not only analytic queries, but also with temporal and archive tables to reduce storage requirements. I’m truly amazed at how quickly IBM continues to innovate and integrate the Netezza technology with DB2 for z/OS to reduce total cost of ownership. The accelerator was designed used to greatly improve the speed of answering complex analytic queries, and then support was introduced to save storage through the accelerator's high performance storage saver (HPSS). Integration with temporal tables as well as transparent archiving just continue the accelerator's evolution.

In my experience, too few customers are aware of everything the DB2 Analytics Accelerator can do. This document is brimming with detailed information on the accelerator and other key DB2 11 enhancements. It's well worth your time.

Posted October 20, 2015| Permalink

comments powered by Disqus