IBM i TR8, Database Driven

April 21, 2014 Dan Burger

The TR8 updates for IBM i 7.1, announced last week and available June 6, were once again stacked with DB2 for i appropriations. That’s good news for the growing number of SQL advocates in the IBM midrange community. If you are not yet onboard with SQL, for data access and/or data definition, you are falling behind in modern skills, tooling, and programming framework. This kind of stuff is leading the way in IBM’s Technology Refresh program.

There are more than a couple of good reasons for this. One is that SQL fits into modern, multi-platform data access. Another is the fierce competition in the relational database market with Oracle 11g and 12c and Microsoft SQL Server 2012 and 2014 keeping the pressure on DB2. Continual updating is mandatory for a database to remain competitive. It becomes quickly apparent when one company lacks the innovation that a competitor is putting forth. There’s also the application development shift toward using more database muscle and reducing the amount of long-term legacy application maintenance. And not to be overlooked is the fact that the DB2 database sits on top of the operating system and allows the refreshes to be their own entities rather than be so entwined into the operating system that additional teams outside of database development are not needed to deliver the solution. Scanning the eight Technology Refreshes for 7.1 reveals a lot of DB2 for i tweaking has been going on.

One of the team members from IBM’s DB2 for i Center of Excellence, Kent Milligan, was the guest speaker at the OCEAN user group of Southern California meeting last week. I talked with him about the database enhancements in TR8 before he launched into two SQL sessions for the audience of approximately 45 OCEAN members. OCEAN, by the way, is celebrating 25 years as an IBM i user group this year.

Milligan’s primary role with IBM is to work with the IBM i independent software vendors (ISVs) on database-related matters. He relays feedback and requests to the DB2 for i development team and recommends enhancements that can be incorporated into the Technology Refresh program and be valuable to a wide segment of users.

One of the database upgrades in TR8 originated from an IBM i ISV Milligan worked with at the beginning of this year. That partner is using SQL and the database rather than writing traditional application code. The SQL-based application worked fine in standalone mode, but when it was replicated to 10 testing environments, using change management tools, the next value to be generated was not propagated. The test environments would get properly populated with data, but the application would fail.

“We (IBM’s database development team) worked on a management utility–a stored procedure interface–that was easy to drop that in and solve that problem,” Milligan says. “This was a management and administration issue that related to how the change management tools were working. The process used in this change management tool is used in other change management tools and that’s why the development team was open to the idea of building this utility. It wasn’t just one partner that was being helped.”

This type of database management enhancement, based on system-provided stored procedures, has been the foundation of TR database upgrades. It isn’t rocket science, Milligan says, but it improves and simplifies database management and administration.

Another TR8 database enhancement was added because it makes it easier to identify performance problems.

“We have graphical performance tools to identify longer-running SQL statements,” Milligan notes, “but users have to do that interactively and select their way through it using a mouse. Some would like to do that programmatically.”

For instance, a user might want to identify the top 20 longest-running SQL statements in the plan cache tool, so he or she can examine those statements and determine if there are ways to make them run faster. TR8 has added new system-provided utilities or stored procedures–dump plan cache and extract statements–that programmatically pulls that information. It avoids the time it takes a user to do that work interactively with a graphical client.

An example would be setting a program schedule to once a month dump the longest-running statements from a plan cache into a file so possible performance problems could be worked through each month. Maybe someday it will automatically fix those problems, but for now that part of the task requires the human touch.

This enhancement required accessing the system objects that have that type of information and a deeper SQL knowledge than most customers possess, so the development team determined this was a good DB2 for i services project.

The majority of these services, Milligan says, are delivering information that was always available via API calls on the system. However, some of the APIs are complex to invoke and manage. So the benefit of DB2 for i services is that they allow the data to be requested without writing a program or calling an API to create an interface that writes the data and puts it into a file where it can be accessed. It’s a matter of taking existing interfaces and making them easier to use.

Additional TR8 database features have been built into the tools that do DDS to SQL conversion–iNavigator and the graphical client. Once again, this is about automating a manual processes. Now there is a “generate SQL” stored procedure that allows users to evoke the same system API that iNavigator invokes and identify the objects to be converted from DDS to SQL. With a simple stored procedure call, users can start that conversion.

You might wonder why these types of things aren’t built into the database when it was released. Well, maybe you are familiar with the saying, “If I woulda known then what I know now, things would be different.” The short answer is that IBM is wired to deliver APIs and let others apply their expertise to the APIs. Software companies depend on customer feedback to better understand how users are using the product.

“It is a compromise to some extent,” Milligan acknowledges. “For instance, some customers want new CL commands generated to return some of this information. You could view CL commands as a 5250 enhancement, but another approach could be to write CL commands that would return this information in CL. The ultimate solution is a program that customers could use to easily return some information. Sometimes you have to give a piece of a function and then go from the feedback in a direction that the customers ask for most often.”

Milligan’s perspective is “don’t deliver a Cadillac full of stuff only to find out people aren’t interested in some or most or all of it. What we do is more of a step-like approach.”

Regarding the process of sorting through the feedback for requests that generate TR-level enhancements and the deeper involvement that requires a new database release he says, “I’ve worked in development for almost 10 years. I have a pretty good idea if something I ask for is going to be easy or difficult and expensive. I know going in to my meetings with the database development team which decisions are going to be tougher and I do more research up front. A couple of weeks for an individual developer to accomplish is not a big deal, but if it takes one person or multiple people a month or months to accomplish, a strong case needs to be made to go forward with that request.”