Centerfield Aims to Ease iSeries SQL Management with AutoDBA

Published: March 27, 2007

by Alex Woodie

The calendar reads spring, which means the snow is melting, grass is growing, and the sounds of baseball will soon emanate from diamonds across the country. It's also a time of renewal at Centerfield Technology, the DB2/400 performance tool developer from the friendly confines of Rochester, Minnesota. Spring training is starting for the latest addition to Centerfield's roster: an autonomic SQL performance tool called AutoDBA that should give IT admins with average skills the capability to put up Barry Bonds-like numbers.

Performance-enhancing substances are now verboten in professional baseball. But in the world of IT, professionals are encouraged to adopt any product and method that can yield a real performance edge (provided it doesn't bypass or "patch" IBM's proprietary microcode).

For the do-it-yourselfers, IBM provides a slew of papers, articles, and books, that can give a System i administrator the knowledge to optimize their DB2/400 environment for the highest throughput and performance. However, unless you have an abundance of time and the inclination to become an expert database administrator (DBA), these optimization tricks are beyond the reach of the average IT administrator.

Traditionally, that hasn't posed a problem for most iSeries shops, which haven't had the need to pay for a DBA the way, say, Oracle's database demands regular care and feeding. But that was before SQL became such a critical element in the application performance equation, says Centerfield CTO Mark Holm, a DB2/400 expert and former IBM developer.

"In the good old days you didn't really have to worry about having a DBA on an iSeries. It just managed itself," he says. "But a lot of iSeries shops over the last four to five years have started using a lot of SQL, or SQL-based applications. SAP, J.D. Edwards EnterpriseOne, Lawson, Intentia, BPCS, MAPICS--they're all SQL. So what's happened, essentially, is the environment has gotten a lot more complex to manage."

Centerfield has been making and selling tools to help OS/400 and i5/OS shops tame the SQL tiger and squeeze the most performance out of their DB2/400 implementations since it was founded 10 years ago. Last summer, the company introduced a new suite, called HomeRun, that includes all of Centerfield's key performance tools, including insure/INDEX, which identifies critical access paths and creates new indexes; insure/ANALYSIS, which correlates SQL statements with application behavior; insure/MONITOR, for real-time problem diagnoses; insure/RESOURCES, for implementing controls that grant or deny access to precious system resources; and insure/SECURITY, a network security tool for protecting data and locking down system resources.

The introduction of HomeRun corresponded with the version 5.0 releases of the products that make up the suite, but version 5.0 was mostly a minor update that brought limited new functionality, Holm says. The biggest changes had to do with the packaging of the modules into a bundle and a reduction in price to make it more attractive to companies outside of Centerfield's traditional clientele of Fortune 1000 companies. HomeRun costs $12,000 per LPAR.

Now, the company is getting ready to roll out version 6.0, which will feature more radical changes to the products. The biggest change with version 6.0 will be the introduction of the new AutoDBA module, which will handle database optimization tasks with less input from humans than previously required.

AutoDBA

The first release of AutoDBA will focus on automating many of the database tuning tasks currently handled by insure/INDEX and insure/ANALYSIS. AutoDBA will automatically analyze access paths, SQL indexes, and logical files, and other database stuff, and determine the best way to improve them.

"We read thousand of pages of manuals and extracted the best practices to design an autonomic product that applies the best practices in the right situation, based on analysis of the database," Holm says. "We lay it out for people. Then if they want us to take action, we do, but we allow full control."

Actions taken by AutoDBA could include the deletion of extraneous indexes (the "boat anchors" hindering performance, Holm says), the optimization of existing indexes, and the creation of new indexes that will open up performance, among other features. If the administrator agrees with AutoDBA's assessment, they can let the product automatically implement the recommendations. At any time, the administrator can regain control over the system, or even reverse the changes and rollback to previous settings.

Centerfield took pains to give users the flexibly to incrementally give hand control over to AutoDBA as they become more familiar and confident in the product and its autonomic capabilities. "We try to adhere to IBM's definition of autonomics," he says. "We have to be very customizable, and while it's automated, you don't give up control."

Centerfield is walking the line that separates AutoDBA from becoming a totally autonomous products with full authority to make changes on the one hand, and requiring the user to become an expert in SQL optimization techniques on the other. It has done a lot of work distilling the thousands and thousands of pages of SQL optimization techniques written by the database gurus in Rochester into a wizard-driven, Windows-based graphical user interface that's easier to understand and use than IBM's tuning tools and takes advantage of SQL improvements IBM made with i5/OS V5R3 and V5R4.

"iSeries Navigator has some really nice tools for tuning SQL performance," Holm says. "[But] it goes back to time and complexity. Those tools are written in Rochester, and for people that have the expertise to use them, they're very effective. But if you look at the masses, there's not the expertise out there. Users are presented the data with very little information. There are screens and screens of stuff, but . . . there's a disconnect between what you can do theoretically, and what you can practically do in most shops."

The GUI received a lot of attention with AutoDBA. "In essence, it all boils down to one screen. There are a lot of knobs if you want to manipulate them," Holm says. "[But] we're not forcing you to learn the bits and bytes. [We're saying] here are the pros, the cons, the caveats. We have a lot of links embedded in the GUI back to information on our Web site that's written in a way users can understand."

In addition to making applications run faster, AutoDBA should save users money when it comes to hardware. "A lot of shops are over-sizing their boxes now," Holm says. "The amount of CPU looks reasonable but there are very large complex applications, particularly because SQL is in the picture and it tends to be spiky."

Legacy RPG applications utilizing native database access should also benefit. We have been focused on SQL totally in the past but this really opens the door" to other workloads, Holm says. "Things you can do in indexes to make it faster without having to run SQL or compile the RPG."

AutoDBA is platform for future product development at Centerfield. "There are hundreds and hundreds of best practices in IBM manuals. We've just scratched the surface."

Holm is also working on tackling other problems with AutoDBA. "We would like to get into more the file and table related things that can optimize data itself," he says. "For example, people run with variable length character fields. But you can misuse the way you use those and suffer pretty significant performance penalties if you don't get them right."

Development of AutoDBA is nearly complete, and beta testing is slated to begin later this month. The product won't be ready for field use when the big league's start playing for real on April 1, but it should be available soon after that.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot