Consolidating Relational Databases

In these days of constant mergers and acquisitions, it’s quite common for companies to find themselves operating two or more mission-critical relational databases. Though you struggle along running these database management systems (DBMS) side by side, it ties up IT time, adds unnecessary expense and slows down standard reporting functions. But which database to chose?

This article discusses the options, taking the stance that IBM’s DB2 family of products (DB2 for OS/390 and Universal Database for UNIX and Windows NT) probably offers the most advantages. It also gives an example of how data centers leverage the advantages of both manual code re-engineering and automated conversion when moving from SQL Server, Sybase or Oracle 9i to DB2 UDB without impacting the business or losing application functionality.

Database Boom

Last year alone, the database market grew 10% to $8.8 billion, and Gartner Group estimates it will reach $12.7 billion within two years. It appears that despite the state of the economy, the corporate world has come to realize that the pain of remaining on aging or disparate systems and databases is far greater than the cost and inconvenience of migration.

This mushrooming database marketplace means that companies are being forced to confront various options in terms of database consolidation, merger and standardization. Selection, of course, is based on a wide range of factors including size of the operation, transaction volume, importance to business survival, existing environment, costs, personal preferences as well as a host of other considerations.

The sketchy overview given here does not pretend to encompass all possible scenarios. Bear in mind that it is based on a combination of personal opinion and analyst feedback. For every analyst quoted here favoring IBM, there are probably two or three who side with Microsoft or Oracle.

So here goes:

SQL Server and Sybase tend to be best as small and mid-range DBMS’, but they generally scale less well in the enterprise. They do not typically include the extended relational and object-oriented features that are now common amongst their rivals. Further, due to some Java-based driver problems they may have some limitations in a Web-based traffic intensive environment.

When it comes to high-end enterprise-class databases, the two principal contenders are Oracle 9i and DB2 UDB. Though both sides hotly contest superiority, IBM appears to have the edge, at least for the moment.

According to research firm D.H. Brown Associates Inc., Oracle 9i loses out to IBM’s DB2 UDB in its installation routine, query optimization, architecture for distributed database, and query governance. On average, DB2 UDB efficiencies yield an overall reduction in work effort of 6% for OLTP systems, 15% for large OLTP systems, 20% for Internet enabled databases, and 18% for data warehousing.

“DB2 UDB provides TCO advantages, saving the customer 20%-32% compared to Oracle,” said D.H. Brown.

DB2, for example, holds a strong price advantage over Oracle for scenarios where the DBMS will be accessed from an external Internet. Further, DB2 leads when configurations support 25 or more named users per CPU. According to D.H. Brown, DB2 is half the price of Oracle on systems configured with 50 named users or more per CPU.

These findings are supported by a recent study by the International Technology Group: Five-year software costs on average are 1.72 times higher for Oracle than for DB2. If differences in personnel cost are included, Oracle’s costs are 2.25 times higher. For Windows servers, Oracle software costs 89.8% of DB2 while overall costs are 1.77 times higher.

Richard Yevich, principal at Yevich, Lawson & Associates, agrees. He claims that Oracle 9i demands far more tuning than DB2.

“In DB2 applications, only 7% of the medium and complex queries require further tuning as compared to 62% for Oracle,” he says. “The main reason Oracle is not used for very large databases is that its shared disk architecture is not suitable for scalability in the terabyte range.”

According to Bloor Research’s comparative analysis of all relational databases, DB2 is the ideal choice for large companies.

“The parallel facilities of DB2 UDB are at least as good, and probably better than, any competitive product on the market,” Bloor concludes.

D.H. Brown concurs. “DB2’s advantages suggest that it is the more economical choice over a five-year period for almost all scenarios.”

DB2 Migration

Granted that plenty of counter claims by Oracle exist, backed up in some cases by analyst muscle. But let’s assume that DB2 currently holds the lead. How do you get from SQL Server, Sybase and/or Oracle 9i to DB2 without impacting the business or losing the functionality of existing applications?

Manual code reengineering/rewriting is certainly a reliable way to go about it, but demands a lot of time as well as highly skilled programmers who go through application and database code line by line. A successful approach leverages the advantages of both manual code re-engineering and automated conversion. Such a methodology results in programs that are efficient while being converted speedily and inexpensively. This is the basis of tools like Arlington, Va.-based ManTech Systems Solutions Corp.’s (MSSC) SQL Conversion Workbench (SQL-CW).

SQL-CW is a database conversion assistant designed to facilitate the conversion from various mid-tier and mainframe databases (and associated applications) like SQL Server, Sybase and Oracle to DB2 UDB. Such tools alleviate much of the manual effort associated with conversions of this nature while ensuring that the resulting system functions well.

This method combines the best aspects of code reengineering and database conversion with the speed and consistency of automation. It includes tools for refining the converted database, mapping the existing data structure to the new environment, resolving discrepancies between the database products and converting the application code.

SProCT (Stored Procedure Conversion Tool), for example, is a subset of SQL-CW that allows migration of schema, data, and procedural language for Microsoft, Sybase, and Oracle databases to DB2. It is available to end-users through IBM as a free offering here.

Conversion Steps

Unload/load metadata. Extract metadata from the source database tables into the SQL-CW repository. The conversion tool builds an internal data dictionary for documenting and converting the source database and its internal structures.The source database objects can be automatically mapped to corresponding DB2 database objects. Alternately, this can be performed manually.

Customize metadata. Use the Repository Editor to implement desired data design changes before the application or stored procedure code conversion begins. Once done, the Repository Editor generates Data Definition Language (DDL) and data migration scripts.

Convert application code. Assess the code; set or verify conversion options; select and modify a program, procedure, or trigger; convert the file; verify the conversion output; and compile the program.

Data Conversion. Export and load the data into the DB2 database.

Testing. Test the newly converted application and database objects.

One DB To Rule Them All?

Too many companies these days find themselves operating multiple databases, relational and non-relational. While some parts of the company use DB2, others run on Oracle, SQL Server, Adabas, IDMS, Datacom, Supra and Informix. Managing so many DBMS is costly, time consuming and inefficient. Reporting can be a monthly nightmare, data consolidation can keep IT working long hours and HR can spend a fortune attempting to lure high-paid resources for a wide range of platforms. Consolidation onto one easy-to-manage DBMS is generally a smart move.

By implementing a common database architecture, whether it is DB2 UDB, Oracle 9i or something else, it may be possible to streamline and simplify database management. Through the use of effective conversion tools, the process can be reduced from a lottery into an exact process that maps out the route from database chaos to standardization.