New Xcase Release Propels DDS-to-SQL Migrations

October 18, 2011 Dan Burger

If you think IBM DB2 database modernization is a time-consuming, money-gobbling adventure into the unknown, you’re right. But that’s the correct answer only if you try to wade through it while depending on manual procedures. Automation will save time and money, as it always does when the alternative is dependent on heavy manual labor. That’s exactly why Resolution Software designed and is selling Xcase Modernize-DB, which last week became generally available in version 9.1.

Databases at typical IBM i shops are like roadkill. The older they are, the more likely they are to smell. If you’re around them all the time, you might not even realize the odor.

DB2 data is being called in ways that were never dreamed of when DDS came on the scene. And just like RPG has been modernized, the same is true about the DB2 for i database. Old RPG and old DDS still work for many companies, but both provide limits in a technology-driven world that is moving on.

At the very least, a database that has not been well maintained suffers from performance issues related to queries because data structure has not been properly created. Some of that can be corrected in DDS, but the best DDS performance still falls shy of what the SQL engine can achieve.

SQL offers other significant benefits over the original integrated database on the IBM i. You want some examples? Here you go: It has a standard for development that provides for design continuity. There’s improved data integrity over what you have now, better performance and scalability; easier implementation of business intelligence; storage of unstructured data such as BLOBs, CLOBs and XML documents; easier integration with Web services and other applications; and faster development.

“Many companies would like to take advantage of SQL’s performance, data integrity, and more flexible reporting options, and gain all of the benefits of moving the management of business rules and data processing into the database,” says Elie Muyal, CEO of Resolution Software. “There are lots of compelling reasons to move forward with SQL. Kent Milligan lays out this vision nicely in his white paper DDS and SQL: The Winning Combination for DB2 for i.”

In fact, a growing number of IBM i shops are moving new database development to SQL, which most would agree is the modern standard. IBM has pushed and prodded (maybe you prefer the term incentivized) the IBM i users for years to migrate to native SQL by bringing SQL enhancements to the operating systems and repeatedly stating that DDS will not be enhanced but will be supported. (If this was Oracle, support would likely be gone as well.)

At Old Dominion Freight Line, a long-standing IBM i advocate, the need to become agile when responding to customers’ needs has been a major driving force behind the modernization process. Sheila Burwell, director of IT Services at Old Dominion, is quoted in a press release from Resolution. “An incremental approach made the most sense in today’s challenging economic climate; therefore, changing our System i database access to SQL was the first step,” Burwell says. “Xcase allows us to achieve this in a fraction of the time by automating a lot of the time-intensive steps.”

Dave Hendrickson, president of Tree Line Data, a company that specializes in database modernization projects on IBM i, calls the IBM i server ideal for SQL workloads.

“It was one of the first platforms to be 100 percent compliant with the core level of the ANSI 2008 SQL Standard. And it offers many features that make it relatively easy to port databases from MySQL, SQL Server, Oracle, and others,” he points out. “Generally speaking, I believe we need to educate ourselves and our management on the business and technical advantages of modernizing with SQL. This will allow us to move forward with the new features, the openness, and the interoperability with other platforms that comes with adopting SQL.”

Hendrickson came from the mainframe world to the System 38 community in the late 1970s. He’s been on hand for the introduction of the AS/400, iSeries, System i, and IBM i.

“There are people in IBM i shops who are looking forward and they see that the platform is getting more closely in tune with what other platforms do. They think that is the right thing to do. Many are already doing conversions to SQL and have stopped building any more DDS. Now they are looking at getting their key DDS data moved, but it’s cumbersome to do,” Hendrickson says. “These are companies that are interested in Xcase.”

With its latest release, Xcase’s Modernize-DB has expanded its level of automation, which directly impacts the amount of time spent on modernization. In certain situations manual methods could easily take days or even weeks compared to automated methods that complete the task in minutes.

Among the new automation features is the capability that steers users away from doing unnecessary work. Specifically this involves identifying work files that do not need to be modernized, which reduces the overall time allotted to doing the modernization. It amounts to a “separating the chaff from the wheat” process, and things like this come up in cost-benefit analysis. Prior to the release of version 9.1, the work file identification capability was not included.

Another automation feature finds objects with missing source, which happens surprisingly often in databases where controls have been lax. It then recreates the source files. Give a developer this job and he’ll go cross-eyed within the first 30 minutes.

Much of the newness in Modernize-DB version 9.1 relates to flexibility and adaptability to local environments. To provide a level of adherence to individual company standards that can now be put in place, there’s template-driven naming conventions for newly created SQL tables, fields, and indexes and also support for “CamelCase” names that contain a mixture of upper and lower case letters.

These may sound insignificant, but all of these features were the result of customer feedback from the earlier version.

“The ability to customize Xcase to enforce our shop’s database object naming standards was a feature we looked for when evaluating the tool,” says Thomas Hall, DBA on the IT Services team at Old Dominion. “While we could customize naming for most objects, we wanted to control index naming too. We submitted the request to Resolution Software and they agreed it was a needed feature. . . . I believe it will allow us to complete our modernization projects even faster.”

For those not familiar with the earlier versions of Modernize-DB, it has the capability to add auto-maintained columns–such as those containing unique identity, time and user stamps, or custom attributes–to new SQL tables created during the modernization process. This is accomplished without interfering with existing applications, which is an important consideration. The new release provides more granular control over defining and positioning new auto-maintained columns.

Users can choose whether these columns appear at the beginning or end of the table, the name, heading, description of the column, and its identity attributes. It also provides a degree of customization by allowing users to position specific columns in specific tables.

When Modernize-DB examines a database, it provides a report that, in part, identifies files that can be modernized, which ones don’t have to be modernized, and which ones have problems being modernized. The diagnostic tool also provides an estimate of how long it will take to perform certain jobs compared to how long it would take to complete them manually. This diagnostic tool can be downloaded free from the Resolution Software website.

This is not a new feature in Modernize-DB v9.1, but it is one of the most useful features when planning a modernization project.

Many business-critical DDS databases are 20 years old, or even more. And the code in there probably reflects the “do your own thing” coding style of anywhere from several to several dozen programmers, particularly in the small to mid-size shops, where formal database training often did not (and does not) exist. That lack of continuity and standardization has taken a toll. Muyal describes it as database “sprawl.” Another way to say it is databases that have little consistent adherence to a corporate standard. Over the years it has sprawled into a sluggishly performing niche database that doesn’t fit the modern multi-platform IT environment. While these inconsistencies can slow down a modernization project, they are just one more reason to do it.

With regard to resolving the differences in the way code was written in databases that are filled with do-your-own-thing coding, Hendrickson says Xcase is very successful at handling the DDS to SQL conversion of most of what it runs into and can identify instances where it takes hand coding to complete the conversion. Not everything is going to migrate completely.

“Xcase for i makes it far more practical and cost-effective to achieve database modernization,” Muyal says. “One hospital network that we worked with was able to complete its entire 33-server, DDS-to-SQL migration project in just over two months using Xcase. The majority of the time was spent testing results before implementing the transition on a single server. Once the testing and pilot projects were complete, the actual implementation for the remaining 32 servers took just 4.5 hours.”

That’s a good example of what can be accomplished, but be careful of making assumptions or drawing conclusions from that to come up with an estimate of another project. There are many variables to take into consideration.

Xcase Modernize DB version 9.1 is generally available, with North American technical support, services, and sales provided by Technical Decisions of Merrimack, New Hampshire. It is part of the Xcase for i suite of products, which also includes Evolve-DB and the soon-to-be-released Relate-DB. This is the first time Xcase has used release numbers with its IBM i-based products. The 9.1 designation is consistent with the open system version of Xcase.

Additional resources on DDS-to-SQL migrations can be found on the Resolution Software website. There’s a white paper, written by IBM DB2 database guru Kent Milligan, titled “DDS and SQL The Winning Combination for DB2 on i” and a webcast presented by Mike Cain, a member of IBM’s senior technical staff for DB2 on i, titled “From DDS to SQL: How to fully exploit DB2 on i.”