What's new: DB2 for i 7.1

Adaptive performance tuning and new SQL features give data professionals a smooth ride

The latest version of IBM DB2 for i boasts a trunkload of features designed to make your job easier. This overview article highlights some of the most interesting enhancements in DB2 for i 7.1, including adaptive performance tuning via Adaptive Query Processing (AQP), a real-time self-tuning engine, additional SQL features, and improved encryption integration that does not require additional coding.

Share:

Kent Milligan, Senior Certified IT Specialist, IBM Corporation

Kent Milligan Kent Milligan is a DB2 for i senior certified IT specialist in IBM ISV Solutions Enablement for the IBM i platform. He spent the first eight years of his IBM career as a member of the DB2 development team in Rochester, Minnesota, and he speaks and writes regularly on DB2 for i relational database topics.



30 April 2010

Since its early days on the AS/400 platform, IBM DB2 for i has been known for its ease of use and low administration requirements. With the newest release, which hit the streets in late April, the DB2 for i team focused on ways to support developers through self-managing technology and enhancements that simplify common tasks. At the top of the list of feature enhancements: simplified performance tuning, a new real-time self-tuning engine, additional SQL features, and a method to integrate advanced processes such as data encryption into existing applications without additional code.

Fast, simple performance tuning

The DB2 for i 7.1 release greatly simplifies performance tuning in several ways, starting with enhanced management tooling in IBM i Navigator, the central management console for DB2 for i. The latest release includes performance boosters in the DB2 engine, such as enabling solid-state drive and in-memory searches of DB2 databases. IBM DB2 Web Query has also been enhanced, making it easier for DBAs to meet the response-time requirements for both online transaction processing (OLTP) and business intelligence solutions.

But perhaps the most exciting new performance optimization feature in DB2 for i 7.1 is Adaptive Query Processing (AQP). With this feature, the DB2 for i query optimizer can make real-time plan adjustments—such as changing the join order or utilizing a new index—while the SQL request is running and without disrupting the application. Imagine a car engine that can tune itself as you cruise down the road instead of making you stop to adjust the cylinder timing or tighten the belts; that's what AQP can do.

AQP builds on the self-learning query optimization capabilities introduced in the prior release of DB2 for i (version 6.1). But where self-learning optimization technology must wait for a slow-running query to complete before it can learn from past executions and adjust the plan for future executions of the query, AQP can act in real time, as the query is running.

AQP relies on intelligent monitor agents to coordinate real-time performance adjustments, automatically assigning agents to queries that are expected to run longer than a few seconds. During query execution, the monitor agent periodically compares the runtime execution metrics with the optimizer's estimated costs. Whenever an agent detects a significant deviation between the runtime and estimated costs, the query optimizer is invoked to re-examine the query plan and look for a more efficient option.

If a more efficient plan is found (such as a new join order or utilization of a new index), the query is altered to use the new plan. The new plan and restart of the query are completely transparent to the application and the user—the only noticeable effect is improved performance.

AQP is available only to those SQL statements processed by the SQL Query Engine (SQE). But the 7.1 release adds support within SQE for logical file references on FROM clauses. As a result, even more applications will be able to leverage SQE and take advantage of the numerous cases where SQE delivers performance that can be magnitudes faster than the heritage query engine.


New SQL and field procedures

The DB2 for i 7.1 release also contains many new SQL features that make it easier and faster for developers to both extend the functionality of their existing applications and deliver new solutions. A new XML data type and set of XML publishing functions allow IBM i applications to easily interoperate with XML documents. Simple integration of stored procedures returning result sets is now available to RPG and COBOL applications using embedded SQL and to SQL routines. In addition, support for global variables, array types, Merge statements, Currently Committed concurrent access resolution, and the Or Replace clause also lower the barriers associated with porting applications based on Oracle and other database management system products.

Data privacy is a top concern for companies around the world, and new support for a field procedure exit routine, known as Fieldproc, enables developers to transparently deliver column-level data encryption with minimal coding changes. DB2 Fieldproc support enables developers to register an exit program at the field level that DB2 automatically calls each time that a row (record) is written or read. Registering the program at the field (or column) level ensures that the Fieldproc program will be called each time that a database read and write is performed from any interface or application—regardless of the programming language used. On write operations, DB2 will call the Fieldproc program to get the encoded value of the input data and then place the encoded value into the corresponding DB2 column.

The Fieldproc program can perform any type of encoding—including data compression—but it's expected that encryption algorithms such as Advanced Encryption Standard (AES) will be used most often for sensitive data. Correspondingly, any read operation would cause the DB2 engine to invoke the Fieldproc program to return a decoded value of the stored data, which DB2 then passes back to the application. The Fieldproc program could call system APIs to retrieve user or environment information to have conditional encoding and decoding behaviors. For example, it may be a requirement that only users belonging to the administrator group are allowed to see the complete credit card number, while all other users have access to only the last four digits (see Figure 1).

Figure 1. With the field procedure exit routine, developers can easily add column-level encryption and data masking to existing applications.
Fieldproc program diagram

Using Fieldproc requires no changes to existing applications. With prior releases, adding column-level masking or encryption to applications using the native, non-SQL interfaces required developers to add encryption and decryption logic to every program that performed I/O on the table containing sensitive data. This is not a trivial task! Relying on Fieldproc support to centralize and integrate the encryption routines eliminates the need for extensive programming changes, dramatically simplifying the development and delivery of column-level encryption solutions.

The new release of DB2 for i adds up for both administrators and developers. By enabling better performance, simplifying tuning, and putting advanced technology to work, version 7.1 keeps you—and your end users—on Easy Street.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=486904
ArticleTitle=What's new: DB2 for i 7.1
publish-date=04302010