Resolution Moves Database Automation Forward

Published: July 22, 2008

by Dan Burger

The IBM Power System i users tend to be a volatile mix of old and new. Examples are many, but on this occasion the spotlight is on creating, populating, and manipulating databases. Dating back to the System 38, databases on this platform were created using DDS (data description specification). Outside this universe, other relational database management systems (RDMS) use structured query language (SQL) to define the database. Some System i shops have been converting to SQL, but there have been obstacles.

Those obstacles exist despite IBM's efforts to enhance DB2 UDB for iSeries and convince users that SQL offers more efficient data access methods and improved throughput. IBM has been at this long enough to have gained more traction. Two factors that can be sited are a lack of tools and a lack of skills. Therefore, the idea of replacing the familiar DDS with the current industry standard methodology known as DDL has only just sprouted.

"Shops were hesitating about moving DDS to DDL because they were not sure how it would impact existing applications," says Elie Muyal, president and CEO of Resolution Software, makers of a new diagnostic tool that has the potential to reduce the complexity and the amount of hand work that has frozen many database projects. "This move can be much smoother than what they imagined. The move can be done with virtually no impact on existing applications."

Resolution Software has been in the System i database design and modeling business since 1988, which gives Muyal a good insight into the evolving database landscape.

Muyal has been in the forefront of automating the process of converting DDS files to DDL files. Resolution's Xcase software, introduced in December 2007, has established itself as a convenient tool to reduce the labor-intensive project time by months and by thousands of lines of code. And, Resolution Software continues to whittle away on the time and effort required to modernize databases. The newest tool for accomplishing this is the Xcase Diagnostic utility.

Muyal noted that in the initial trials involving the Diagnostic tool, a large retailer used it against a database containing over 2,000 files, and the resulting report generated by the tool named 189 files with missing or invalid source code. "This information alone saved them a huge amount of analysis time," Muyal said. "They also learned they would need to generate over 375,000 lines of code during the conversion. They were planning to modernize this database manually, but they discovered that they could save months of programming time by automating the conversion process using Xcase.”

In a matter of minutes, the utility creates a basic impact analysis against databases, potentially saving several days of analysis time. Resolutions is offering this tool for free so that companies can get a better handle on what is necessary for completing a database modernization project. Naturally, the company also hopes for an opportunity to demonstrate how much time can be saved by using its Xcase automation software, which can be the difference between accomplishing the database conversion or keeping it on hold.

The Xcase Diagnostic is said to analyze databases at a rate of approximately 1,000 files per hour. In that time, it reverse engineers the database into a graphical model and generates a report that provides data for determining the scope and duration of a given database modernization project and a comparison based on whether the conversion is completed manually or automatically using Xcase.

Information provided includes quantifying files that may present challenges during the modernization process and estimating the programming time required to convert the database files; statistics such as the number of physical files and logical files in the database that cannot be converted to SQL due to missing or invalid source, and those that may need special handling; and a listing of files in the target database identified as presenting potential issues in the modernization process. Developers can use these lists to remediate issues before starting the conversion phase of the project.

When run against any portion of a database, the Xcase Diagnostic utility analyzes its potential for modernization based on the IBM-recommended process that upgrades the database structure without impacting existing applications. For more information on the IBM-recommended process, see the IBM Redbook "Modernizing iSeries Application Data Access."

IBM is advising companies to modernize DDS-generated databases to the industry-standard SQL DDL as part of the Better Architecture phase of the IBM i Developer Roadmap. This advice is based on issues relating to performance, data validation, portability (multi-platform), management and maintenance of existing databases, and the development of new databases.

Dan Cruikshank, a senior consultant for IBM's application and database optimization in the Lab Services for System i operations of the Systems and Technology Group, warns this is no walk in the park.

"Database modernization is much more than simply transforming an existing DDS database to an SQL DDL database," Cruikshank says. "We are seeing a new set of database positions emerging for traditional System i organizations. These positions are the database architect, analyst, and engineer. Not only do these new positions require advanced skills, they also increase the demand for more advanced database tools for the System i. Simply put, modernization requires modern tools.

"The best approach to database modernization is to create a team of individuals focused entirely on database design, development, deployment, optimization and administration," Cruikshank continues. "Many System i shops already have these individuals in house. Today they are called programmers and system administrators. Their first mission as database developers is to identify the tools that will be required to move forward with the database modernization project and continue with the on-going database development effort. The number one mission goal for this team is to minimize the impact of change on the business. This goal applies to both the database modernization effort and the continued development of the database. Selecting the right tools to meet this goal is a major first step."

Traditional source configuration management tools have been weak in a number of areas. As Cruikshank points out, prior to V5R1 a good data modeling tool could import DDS specifications and transform them to SQL DDL. The problem was that the DDL was not System i specific or it did not include the best of the System i DDL enhancements. In some cases the old tools simply did not support SQL.

Cruikshank, a guy who has probably seen more System i database modernization projects than anyone, also notes "there has always been a need for an impact analysis tool to identify the relationships between the physical file being transformed and the logical files using that physical file and the programs referencing both the physical file and all dependent logical files. This tool would also be used post-transformation to validate the record format IDs that are part of each database object and embedded within the programs that reference those objects."

"Because of changes in numeric data validation, tools are required to analyze the existing numeric data and convert any invalid numeric prior to data migration. Most legacy customers have addressed this but it still can be an issue for some System i shops."

What Resolutions Software has done is integrate the Xcase Database Modernization Diagnostic utility into its existing Xcase for System i software, which includes components for database modernization and future modifications.

The modernization component automatically augments traditional IBM i databases to be fully compatible with SQL, without impacting existing applications. It allows companies with data on the System i to modernize databases much more quickly and implement SQL functions across new and existing applications as needed.

The Xcase Evolution component automates the process of making ongoing modifications to SQL databases after modernization.

Those with an interest in SQL know the list of advantages over native I/O methods. For those who don't, it includes improved application response times; compatibility with modernized applications, particularly those using service oriented architecture (SOA) concepts; support for advanced database functions and data types; and greater data integrity.

The System i is fully capable of running a modern, industry standard SQL database. It's had that capability for years. The fact that most users don't use this capability is one of the reasons the platform gets tagged with the "old school" label. As database modernization becomes easier, and therefore more common, the platform might gain some overdue recognition for being a modern technology.

RELATED STORY

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