Oracle to DB2 UDB Conversion Guide2003

This IBM® Redbook provides procedures and examples of converting Oracle to DB2® UDB. We focus on the technical considerations and methodology involved in performing the database and application conversion. The laboratory examples are performed under Oracle 8i and DB2 UDB V8.1. However, the migration process and examples can be applied to Oracle 7, 8, and 9i. The redbook is organized as follows.

• Chapter 1 provides an overview of both Oracle and DB2 UDB to facilitate the understanding of both architectures.

• Chapter 2 introduces the common process of migration and porting, the planning process, and the available tools and resources.

• Chapter 3 describes the IBM Migration Toolkits functions and installation procedures.

• Chapter 4 discuss and demonstrates the conversion of database objects and the extraction and loading of data into a DB2 UDB database using the IBM Migration Toolkit.

• Chapter 5 explores some tools and methods for manually converting the triggers, store procedures, and functions. It also provide examples of implementing SQL features in DB2 UDB, and technics in building external procedures and functions using C/C++ and Java™.

• Chapter 6 discusses the data conversion methods for deploying the data from Oracle to DB2 database.

• Chapter 7 describes the application conversion from an Oracle to a DB2 UDB environment including planning, Proc*C, OCI, Java conversion, and package application migration such as SAP, PeopleSoft, and Siebel.

• Chapter 8 discusses the administration script conversion from an Oracle environment to a DB2 UDB environment including data load script conversion, administration script conversion, and report tools available to DB2.

• Chapter 9 describes the test objectives and a generic testing methodology that can be employed to test a migrated application.

The team that wrote this redbook

This redbook was produced by a team of specialists from around the world working at the International Technical Support Organization, San Jose Center.

Figure 0-1: From left to right, Ranjit, Ken, Fadel, Whei-Jen, Stefan, Artur

Whei-Jen Chen is a Project Leader at the International Technical Support Organization, San Jose Center. She has extensive experience in application development, database design and modeling, and DB2 system administration. Whei-Jen is an IBM Certified Solutions Expert in Database Administration and Application Development as well as an IBM Certified IT Specialist.

Fadel Fiani is a Certified IT Specialist with IBM Information Management Software. He has over ten years experience in both database administration and application development using a wide range of client/server and Web based platforms. His experience includes designing and implementing logical and physical database systems. He has been working for IBM for almost three years providing DB2 presales technical support, engaging in proof of concepts, and leading many competitive Oracle to DB2 migration initiatives. Fadel is based in Dublin, Ohio.

Marina Greenstein is a Certified Consulting IT Software Specialist with the IBM Database Migration Team. She is an IBM Certified Solutions Expert who joined IBM at 1995 with experience in database application architecture and development. During the 8 years Marina has been with DB2 Migration Team, she has assisted the customer in their migrations from Microsoft® SQL Server, Sybase, or Oracle databases to DB2 UDB. She presented migration methodology at numerous DB2 technical conferences and at SHARE. She is also author of multiple articles and a white paper on the DB2 migration subject.

Stefan Hummel is an IT Specialist for competitive database migrations in IBM, Germany. He has more than 9 years of IT experience using a wide range of client and server platforms. His experience includes application development as well as database design, implementation, and administration of Oracle and DB2 databases on UNIX®, Linux, and Windows®. He is a IBM Certified Solutions Expert for DB2 UDB Application Development and Administration.

Ranjit Kumar Kalidasan is a Database Consultant in Cognizant Technology Solutions, India, a major IT solutions provider and IBM Business Partner. He has more than 5 years of experience in the IT Industry. His areas of expertise are in technical and system support of IBM DB2 in all platforms, including DB2 for OS/390® to Universal Database on Windows NT®, UNIX, IBM iSeries™ server; and Oracle database administration on Windows and UNIX platforms. He is an IBM Certified Solutions Expert on Universal Database 8.1 Database administration and Oracle Certified Professional on Oracle 8i Database administration. He trains exclusively on Oracle and DB2 for OS/390 and Universal Database on Windows NT and UNIX at Cognizant.

Ken Leonard is an Information Technology Specialist with IBM Information Management group. He has 14 years experience in the areas of application development and database administration. Before joining IBM, he worked for Oracle Corporation for more than 7 years as a Senior Systems Engineer and a consultant. He became certified on Oracle 7.3 in 1998.

Art Sammartino is a Certified Consulting IT Software Specialist with the IBM Database Migration Team. He is an IBM Certified Solutions Expert who joined IBM with experience in Microsoft SQL Server, Sybase, and Oracle. In the 4 years that Art has been with IBM, he has assisted more than 200 clients in their migrations from Microsoft SQL Server, Sybase, and Oracle databases to DB2 UDB. He is also a co-author of the DB2 UDB V7.1 Porting Guide, SG24-6128.

Artur Wronski is a Advisory IT Specialist within IBM Software Group, Poland. He has more than nine years of experience in the IT Industry. He holds a master's degree in Knowledge Discovery in Databases, at Warsaw Technical University. His areas of expertise include DB2 UDB administration, troubleshooting and tuning, as well as Business Intelligence solution implementation.

Acknowledgement

The authors express their deep gratitude for the help they received from Paul Yip, IBM DB2 UDB Consulting Services. They would also like to thank the following people for their contributions to this project:

Kevin Decker IBM Data Management Marketing

Barry Faust, David Dean, Debra Eaton, Burt Viaplando, IBM Software Migration Office

Tom Christopher, Jean-Jacques Daudenarde, Gerry Fisher, Maxime Tiran IBM Database Migration Toolkit Development, IBM Silicon Valley Laboratory

Drew Bradstock, Ted Wasserman, Bill Wilkins IBM DB2 UDB Consulting Services, Toronto Laboratory

Alex Jarzebowicz IBM Data Management User Technology, IBM Silicon Valley Laboratory

Dan Simchuk IBM World Wide Consulting - Data Management group

Sotaro Izuha DB2 UDB DM SW Technical Sales, IBM Japan

Murali Sankar, Ramasamy Senthilvel, Jaishankar Shanmugam Cognizant Technology Solutions

Maritza M. Dubec - Technical EditingEmma Jacobs International Technical Support Organization, San Jose Center