![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Read this article in our interactive digital edition format!
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.
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.
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.
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.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
