Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Aug 6, 2002

Consolidating Relational Databases - Page 2

By Drew Robb

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.

SQL etc Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM