Database Modernization Still Unknown Territory

Just because an IT manager is accustomed to flying in the face of convention doesn’t always mean there is a flight plan involved. And that’s not to say that the ranks of IT professionals are filled with high stakes gamblers, either. By and large, IT is a safe haven for the risk adverse. As an example, let’s take a look at the integrated database used by AS/400, iSeries, and System i users and compare it with what is considered a modern database. Who is ready to make the jump?

The long accepted practice in the System i environment–which dates back to the System/38, a thirty-year-old system that is the predecessor to the AS/400–is to use a form called Data Description Specifications (DDS) to define the database physical and logical files. This was fine when the platform was highly proprietary, but when that becomes a liability and an alternative to DDS for creating databases–SQL Data Definition Language (DDL)–becomes an industry standard, the conventional method becomes a one-way ticket when a round trip ticket would be a lot more convenient.

Outside of the user base, the DDS-defined database contributes to the AS/400’s reputation as an over-the-hill system. That’s a hot-button issue that brings many of the AS/400 faithful to its defense. However, it’s hard to deny that DDS-defined databases are an obstacle to interoperability. For companies that are moving to a service oriented architecture, are modernizing applications, or are managing and maintaining new databases, the traditional database is an issue that must be dealt with.

Usually, the first topic considered is the improved performance with DDL-defined SQL. Over time the DDS-defined database shows its age by not responding fast enough to increased I/O requests. Beyond performance, there’s the issue of database integrity when external tools or applications gain access. This is best done with business logic built into the database rather than the application. It’s faster, more secure, and it saves development time. When new RPG programs are written, the business rules are already within the database.

Not that many OS/400 and i5/OS shops are facing these database issues. . . yet. But that day will come.

Ted Holt, who moonlights as the senior technical editor of our Four Hundred Guru newsletter and who has a day job as the manager of manufacturing systems development for a international company in the consumer and business-to-business markets, says his company is not converting database files to SQL, but it is using SQL for new projects. His situation is fairly typical.

“We have plenty of things that we need to do that are more important than building new definitions for our database files,” Holt says. “However, Infor, who supplies our manufacturing software, has redone some of their database descriptions in order to improve performance. Since we are using their graphical interface more and more, and getting away from their green screens, that should help us.”

His opinion is that converting DDS-described files to SQL DDL in order to take advantage of the better performance is probably worthwhile for big shops. “I’m not so sure it’s necessary in small shops,” he says. “There’s not going to be any difference for shops that use RPG, Query, and native interfaces. But for shops that use GUI tools, such as client-server apps, they should see some improvement.”

DDL gets dismissed by some people as technology for the sake of technology. And, calling DDL an industry standard doesn’t carry a lot of weight with them for two reasons: an industry standard, if it’s a lower standard that what you are accustomed to, is not a good way to go, and the term “industry standard” has been watered down. The industry standards of today are not necessarily the industry standards of tomorrow. For many AS/400 veterans, their experiences with DDS have been good and continue to be good. A lot of them believe DDS is superior and that DDL suffers from a lack of tools and a lack of understanding. Some look to IBM and say, “Where the heck is the help if this conversion is so important?”

IBM has not done much in the way of tools to assist in this conversion (iSeries Navigator and Rational Developer provide some help) and it hasn’t been very strong in explaining to its user base why this migration is worthwhile. But the development money for DDS has dried up and the efforts are going into SQL DDL.

An IBM Redbook, “Modernizing IBM eServer iSeries Application Data Access–A Roadmap Cornerstone”, is available as a guide to reverse engineer DDS-created databases and provide tips and techniques for modernizing applications to use SQL as the database access method. This was published in 2005. It never made the IBM Rebooks best seller list.

Paul Tuohy, chief executive officer of System i consulting company ComCon and a founding member of System i Developer, a group of experts who host the RPG & DB2 Summit conferences, says database modernization projects are rare. He describes those he is familiar with as “uphill battles.”

Replicating the database from an i platform to another platform is one of the reasons a modernization project gets under way, Tuohy says. A worst case scenario–and the all too frequent scenario–is project management by those without in-depth knowledge of the i platform. Those unfamiliar with DDS and the integration of DB2 with the operating system are destined for problems. The same fate awaits those who are unaware of what can be done with DDL.

“The requirement for database modernization is usually introduced as a side issue as part of an application modernization,” Tuohy says. “It is introduced late in the process and usually because someone has come upon one of the ‘new’ features, such as referential integrity or check constraints. This means that the database is only partly modernized–the bulk of the database being left as DDS defined and add-ons are done with DDL.”

Major projects (for redefining a database with DDL) occur when a substantial amount of SQL is being used to access the database. That typically comes, Tuohy says, after the performance and integrity benefits are understood.rn”The nub of the problem is when people know,” Tuohy says. “I find there is an incredible lack of knowledge among i analysts and programmers about what can be done with the database on i.”

The fact that the System i can function very well as a data warehouse, for instance, seems to be overlooked.

Dan Cruikshank, a senior consultant for IBM’s application and database optimization in the Lab Services for System i operations IBM’s Systems and Technology Group, calls the System i “the best damn database server for commercial applications in the world.” OK. He’s entitled to his opinion. To support it, he points out that the i platform runs on the fastest processors available (Power6); it supports 2 TB of main storage (tops in the industry); and it can run any language, be accessed via any interface, it supports multiple databases on a single footprint; runs Linux and Unix in separate partitions; and supports PHP and MySQL.

Don’t let anyone tell you he has been on the front lines of more System i database modernization projects than Cruikshank. He’s worked the trenches and is familiar with the weaknesses that database tools and lack of education bring.

On the topic of a lack of good tools that have held back past projects, Cruikshank points out that prior to V5R1 good data modeling tools could import DDS specifications and transform them to SQL DDL. At that time, however, the DDL was not System i-specific and it did not include the best of the System i DDL enhancements. As a result, there was a lot of hand-crafted DDL. There was also a lot of frustration from those attempting the project. Some of that frustration lingers on. Cruikshank says, the addition of the QSQGNDDL API, as part of V5R1, eased some of this pain. The ISV community has been even better at providing assistance.

Elie Muyal has studied the process of converting DDS files to DDL files since 1988. His company, Resolution Software, is making some important advances in automating the DDS to DDL conversion; just two weeks ago, the company released a free impact analysis utility. (See Resolution Moves Database Automation Forward in the July 22 issue of Four Hundred Stuff. Muyal agrees that manual database modernization projects have been plagued by high cost, slow completion times, and a high degree of complexity.

“Many companies just couldn’t project an immediate enough ROI, even though they knew it was the right thing to do,” Muyal says. “For example, we know of shops that looked into outsourcing the job, but decided not to implement when they saw the potential bill. Another shop estimated that it would take them a year and a half to modernize their 5,000-object database.” Automating the process has reduced the completion time to between 10 percent and 20 percent of the manual estimate, Muyal claims. “Suddenly, the ROI begins to look far more attractive. And many of the other hurdles to modernization–like the fact that manual modernization requires intimate knowledge of DDS, DDL, and how to preserve compatibility with existing applications–also go away because they’re handled by the tooling. Now it’s easier to envision putting the proper DDL foundation in place so your developers can begin to exploit its performance and functionality.”

Another traditional hindrance to DDS-to-DDL conversions has been the perceived risk to existing applications, to data stored in DDS-defined objects being transferable to DDL-defined objects, and how to deal with downtime during the modernization process. Muyal says automating the process mitigates these risks.

Despite the infrequency of database modernization projects, third-party vendors have picked up the ball when it comes to automating much of the labor-intensive work. It’s a ground-level opportunity with a potentially large upside.

Stuart Milligan, chief operating officer at Databorough, confirms the number of companies involved in database modernization is small, but he says the “number is beginning to grow steadily.” He’s collected positive feedback from the Databorough customers that have done DDS-to-DDL conversion, and these customers cite benefits such as improved performance, more efficient maintenance, database redesign without disruption to legacy code, and more generic access to data.

“Externalizing and modernizing the I/O into stored procedures and SQL is a consistent motivator for most of our customers,” Milligan notes.

“Reporting and inquiry modernization” is another database modernization pathway that a few Databorough customers are pursuing. It involves using the relational data model recovered from RPG and COBOL and the database to populate DB2 WebQuery meta data. Databorough worked with IBM to create a product that automates this process.

There remains little doubt that migrating DDS-defined databases to SQL DDL is a heady project. All of the companies mentioned in this article offer consulting services to help on-site IT staff plow through this or to take complete control of the project. Third-party tools have added a great resource with plug-ins to iNavigator or Rational Developer for i (RDi, the replacement for WDSc) or offering enterprise versions of their products that encompass the entire application development project life cycle. It will take a combination of skills and tools to make these projects successful.

“The ability to quickly change a database becomes increasingly difficult as the window of opportunity for maintaining databases decreases,” Cruikshank says. “These scenarios seem to be most apparent in those IT shops that do not have a single point of control for database analysis and design.”

RELATED STORIES

Resolution Moves Database Automation Forward

Database Tool Maker Joins the System i Market

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