IBM i 7.1 TR4 Database Enhancements: What’s Going On?

May 21, 2012 Dan Burger

Even if you are not onboard with database modernization, IBM is moving forward and expecting that sooner or later the majority of the IBM i customer base will fathom the SQL/DDL emphasis that goes into DB2 for i. With the Technology Refresh 4 update that has just come warm out of the oven, the database enhancements that baked into TR4 include a little bit for everyone. That is, everyone who no longer hangs on to the traditional DB2/400 way of doing things.

Traditions are nice for holidays and family reunions. And they can sometimes serve you well in the IT business for quite a few years. But the DDS-based database is on its way out. The most obvious reason is that it’s too proprietary for a multi-platform world.

Users expect a lot more from the data and they want it presented immediately and in many ways involving many devices. A database that stands in the way of easy multi-platform, multi-device access stands a good chance of being replaced.

“Big data and analytics is where the game is now,” says Scott Forstie, the architect for the DB2 for i database at IBM. Companies have more value in the data they have than they are getting out of it, he says. “It needs to be studied, and it takes investments.”

Companies are ready to spend. According to IDC, the 2012 market for business analytics software will grow 8.9 percent to $33.9 billion.

Forstie is one of three DB2 for i architects that plan the database investments, strategy, prioritization and direction. The other two are Mark Anderson, the chief architect for DB2 on i, and Rob Bestgen, the chief architect of the SQL Optimizer.

Last week I spoke with Forstie about the latest database updates, which cover a lot of ground. They can be sorted into categories beginning with functional, where the majority of IBM’s investment is made, and including security, performance, database management, and database availability and recovery. Functional enhancements tend to be long-term investments, Forstie says. These are rolled out incrementally with additional features added over the course of multiple technology refreshes and new releases.

XMLTABLE and remote SUBSELECT are two functional enhancements that fit the category of long-term investments.

When IBM i 7.1 first hit the streets two years ago, native support for XML data types in DB2 for i was introduced to bring the capability to store XML documents alongside relational data. XMLTABLE goes beyond storing XML documents. The database now has the capability to use XQuery standards-based technology to join XML data with relational data. And that means integrating and managing data from multiple data sources, including relational databases, applications, Web services, message queues, file systems, and documents. It makes it easier to mix and match, locate, and aggregate all the core database stuff, including DDS data from DB2 for i. Third-party vendor products had similar capabilities, but now they will be built into the database.

The remote SUBSELECT feature allows the use of an SQL statement to reference a single remote database separate from the current server connection. This improvement benefits database administration and programming by allowing data to reside in a single location. It should save a lot of workarounds, and is a feature that will likely see expanded functionality in future technical releases.

IBM’s effort to bring additional security features to the database led the DB2 for i team to the creation of function use ID. Forstie described it as an alternative to a higher level authority access, which sometimes is so restrictive it prevents staff from accomplishing specific jobs.

In a multi-platform world, access from remote machines becomes commonplace and authority checks against individual user profiles, associated group profiles, and public authority before providing object access determine whether system security is a big concern. Many companies would write programs to track or deny access. This would be a hassle when personnel changes required new programs with new user IDs assigned based on the necessary access to sensitive data.

Function use ID creates database-level security as users–identified by an authorization ID–can successfully execute SQL or XQuery statements only if they have the authority to perform the specified function. To create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table; and so forth.

This may not be a big deal to a small company, but at a large company it could save a lot of time. It could also save money by not requiring security software from a third-party vendor.

An enhancement that fits better with small to midsize business is the database availability and recovery feature. It pertains to the automatic management of the SQL Plan Cache. Typically at the enterprise level someone manages the plan cache, along with a lot of other things. In smaller companies, many times the database is not constantly supervised. When IBM i 7.1 was introduced, it included an algorithm for managing the size of internal objects in the database. With this just-released feature, the plan cache can be increased or decreased with a built-in protection against temporary objects, for instance, taking over more plan cache than they should. As additional work is driven through the SQL Query Engine, this type of monitoring becomes a bigger issue. Moving from V5R4 to i 7.1 will naturally cause this to happen.

This is another instance where it seems something more elaborate will be coming down the road, and this is the first step in that direction.

Discussions about SQL-based databases always come around to performance and the most notable enhancement in that regard has to do with the capability to maintain temporary indexes. In this regard, the SQL query engine now recognizes queries that will benefit from having a permanent index and will automatically create one based on which queries are used frequently. Permanent indexes based on execution-oriented queries bring performance gains.

Users of System i Navigator have used the index advisor tool to do the same thing manually in the past. But now the database can do this automatically.

More information on the DB2 for i enhancements included in the TR4 for IBM i 7.1 can be seen at the DB2 for i technology updates section of the developerWorks website, which, by the way, is managed by Forstie.

With regard to the database functionality being enhanced by Power7 hardware, Forstie noted that with the increase in capacity it allows more database activity in the background without disrupting the core business.

“The query engine recognizes the hardware being used and adapts to it,” he says. IBM calls it adaptive query processing. “There are things it takes into account like are you enabled for symmetric processing and can we go to multi-threaded in parallel. If you do that, you are taking advantage of the new hardware. It is built to run better in a thread-parallel manner. We’d like to be able to do that and we like to decide when and how much to do it. There are controls that allow customers to get very interactive with the database. From a restrictive basis, controls can be used to limit the amount of parallelization or to designate specific workstreams to be used. Or at the other end, customers can choose the optimized version.”

That’s at the high end of the chart, where the enterprise customers tweak their systems.

On the level where most IBM i companies are working, the thinking is more along the lines of what can this modern database help us accomplish. Forstie says those companies are redefining their goals and planning how to achieve them now and in the future. His advice is to consider database performance, data access, scalability, and security issues going forward.

“You can pick off smaller bits and work your way down a path,” he says. “No one wants to eliminate all DDS files, but there is a way to identify the highest value points for using SQL. It gets you out of the business of doing the workarounds that are often being used today. You have to ask yourself how long do you want to continue making copies of data and distributing it? Long term? Probably not.”