BOOM! Another IBM i 7.1 Technology Refresh

Offering performance, usability, and integration enhancements - something for everyone!

Today IBM announced the next technology refresh for 7.1.  If you are not running on the latest version of IBM i, you are not keeping up. And you're simply missing out on a bunch of new features, functions and benefits.  The announcement letter can be found here.

As you might expect, the awesome DB2 for i Development team is delivering some cool new things too. TR5 contains enhancements that will help you get more, do more and see more with the one of a kind integrated database that resides inside your IBM i.

A few items of particular interest to me (as an SQL performance and scalability guy):

New Query optimizations for EVIs

In a January 2012 article I shared how encoded vector indexes (EVIs) were enhanced in 7.1 to include and maintain aggregate data such as summary, count and average values for each key in the index. Using this EVI allows the DB2 for i engine to avoid reading the underlying table and bypass the work of aggregation. By the way, EVIs are unique to DB2 for i.

A few years ago I wrote about the SQL OLAP grouping functions newly available in 6.1. With TR5 code installed the query optimizer will now recognize and use EVI aggregates to provide data to queries that specify CUBE, ROLLUP and/or grouping sets. For these types of requests, the advanced indexing and query optimization found in DB2 for i can significantly reduce both run time and resource utilization. This means better performance AND better scalability.

Index Advisor - Show Me the Query!

 

I talked about the autonomic index advice in a past post. One of the really nice things about the DB2 for i performance tooling is the ability to go from a specific index advisory directly to the SQE plan cache and pull up a list of queries that reference the table which is the focus of the advice. In other words, all statements referencing this table are shown, not just the statement generating the specific advice.  With enhancements in TR5, the tooling can now be more focused and target the actual statement(s) that generated the index advisory. This ability to cross reference an SQL statement and the index advice will be very helpful in understanding the scope and use of said index. If you're not using System i Navigator to interact with DB2 for i, you're missing significant productivity enhancements only available in the GUI.

Create a Local Table from a Remote Query

 

In a previous post I illuminated a couple DB2 for i enhancements that allow for
querying data on a remote system and inserting the results on the local system via three part naming. Allow me to shine the light another related enhancement to SQL...

TR5 extends transparent access to a remote DB2 database to the CREATE TABLE AS statement. CREATE TABLE AS has been available for many releases. It is a powerful SQL statement that requests DB2 to create a new table based on the results of query. By specifying the optional WITH DATA clause, the query is executed and the results inserted into the new table. This process is faster and more efficient than opening two files in a program and reading from one and writing to the other, not to mention much simpler to code. Now with 7.1, getting data from a remote DB2 server into a newly created table on the local system is easier than ever!

It Pays to Stay Current

 

As Kent Milligan shared with us here it is easy to keep current with the latest enhancements being delivered periodically. More details on the DB2 for i enhancements, past and present can be found in the wiki.