Database Migration

White Paper: DB2 to Oracle Migration MacDB2O 2006: Version – February 1, 2005

1. Overview This document reviews Microsoft’s process methodology for migrating IBM Mainframe DB2 databases to Oracle. Much of our work in this area has been done in conjunction with work projects intended to migrate mainframe applications to server environments (Unix, Linux, Windows). We have our own migration tools (MMK – Mainframe Migration Toolkit), Toolkit ), as well as expertise in using industry standard tools such as Oracle Workbench, Micro Focus Revolve, etc. We are development partners of IBM and Oracle, and as well are partners of: Micro Focus, Migration Transformation Transformation Consortium (MTC) for legacy migrations, and Mainframe Migration Alliance (MMA). The method methodolo ology gy we use requir requires es minim minimum um intera interacti ction on with with the client client’s ’s mainfr mainframe ame produc productio tion n environmen environment, t, thereby reducing reducing costs costs and minimizing minimizing interruption interruptions s of the production production system. system. This is achieved through a phased activity, in which most of the analysis, tool-creation, mock-conversion, testing work is done on a PC (back office). Our global delivery model (offshore, near-shore, and onsite) further minimizes costs for the client.

2. Process Phases The migration process generally includes the following steps: 1. 2. 3. 4.

Creati Creation on of overal overalll Projec Projectt Plan Plan Creati Creation on of overal overalll Migrat Migratio ion n Plan Plan Data Databa base se Sch Schem ema a Migra Migrati tion on Data ata Mig Migra rati tion on

These are explained below:

2.1 Creation of Overall Project Plan This involves the following activities:

•

Determine migration scenario Identify all migration tasks

•

Develop infrastructure sustaining processes

•

Determine the training requirements

•

Determine resources that will be required (HW,SW and people)

•

Determine QA & Support processes and teams Identify customer responsibilities responsibilities Determine MVS change management process Determine the environments -- Test, Test, Development, Production Determine the network connectivity (VPN) to both source and target systems Determine the amount of data to migrate Determine the amount of customization for online and batch

•

• • • • • •

Trusted by over 1 million members

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.





• •

Archiving of historical data to reduce the amount of data to migrate Determine the parallel testing strategy

•

Determine details details of integration with with other systems Determine naming conventions and process standards [eg: Oracle database object names can be 30 chars long whereas in DB2 it is 18 chars ] Prepare a proto target environment en vironment

•

Test the migration scenario

• •

2.2 Creation of Overall Migration Plan This involves the following activities: • •

Analyze customization and platform differences Develop an infrastructure plan

•

Design the layout of the databases, table-spaces, and tables Estimate CPU and Storage (disk & memory) sizes

•

Analyze and choose migration tools

•

Analyze the fixes for present and future environments Develop plans for:  Print migration Batch migration   Testing (Test Criteria & Strategy ) Determine customization of source applications required

•

•

•

• • • •

Determine conversion of JCLs etc. to shell scripts required Determine conversion/Customization conversion/Customization of third party tools Determine EBCDIC-ASCII EBCDIC-ASCII conversion - possible issues Develop plans for  Security  Backup and disaster recovery Administration   Database monitoring  Performance monitoring  Change management  Stress test Incident tracking   Vulnerability Vulnerability assessment

2.3 Database Schema Migration The phases involved in DB schema migration are shown in Fig. 1 and explained below:





This is the major step of the schema migration process. All database objects in the source database need to be converted converted to the equivalent equivalent objects objects in the target target system. Typically ypically objects objects such as data types, tables, columns, views, indexes, stored procedures, triggers, packages, sequences, authorities, functions etc. need to be converted. Factors such as data type, scale, precision, length and default values for table column columns, s, functi functions ons,, and stored stored proced procedure ures, s, null null values values etc. etc. can cause cause issues issues.. Refer Refer Sectio Section n 4 for examples of terminology differences between DB2 and Oracle.

2.3.3 Convert Queries This is the next major phase in the database schema migration process. Even though the basic SQL commands are the same, SQLs differs from engine to engine (Refer to Section 4 for examples of differen differences ces between between Oracle Oracle PL/SQL PL/SQL and DB2 SQL). SQL translatio translation n requires requires good expertise expertise and knowledge of both the source and target systems in order to avoid performance issues.

2.3.4 Implement Converted Objects This phase involves building the database structure, on the target platform through scripts or the facilities provid provided ed in the target target syste system. m. Enhanc Enhanceme ements nts relate related d to the schema schema or perfor performan mance ce can also also be considered in this phase, utilizing the special features in the target system





This phase involves walkthrough of the data presently in the database (or in use). Some data, which is well accommodated in the source system, may not be accommodated in the target system. Usually the volume of data is large and a full walkthrough may not be possible. In such cases random samples are taken for identifying data items, which can cause problems in movement.

2.4.2 Data Cleanup / Enrichment A data data cleanup / enrichment prior to migration migration can help in effective movement of data. data. There could be obsolete or unused items, as well as items which will not affect the source or target system if modified. If this step is performed performed well in advance, advance, the subsequent subsequent phases in this process process will gain significant significant advantage.

2.4.3 Conversion Study This phase involves assimilation of the outputs from the above two phases, and detailed study for finalization of a conversion strategy. This phase can be categorized into the following steps:  Fitment / Conversion Study – Output of this phase is a study report detailing the changes

required in the data items for the movement.  Formation of Migration Strategy – Output of this phase is the “Migration Strategy Document”

detailing the planned process of migration, tools planned to be used etc.  Finalizatio Finalization n of Scope of Migration - In this phase, the scope of migration is defined. Items such

as scope, limitations, performance and maintenance issues etc. need to be well defined.





In this phase, user (client) approves all the documents mentioned above. This phase is very crucial, while handling critical data.

2.4.4 Conversion Tool Preparation In this phase, the tools required for the data movement are developed (or customized). In production systems, tools are very crucial since the final data movement is done in one shot (usually in 1 or 2 days during off hours or holidays). The tools preparation is a full project activity of its own involving all phases of SDLC.

2.4.5 Mock Conversion In this phase, a mock conversion is performed, using the existing data in the source system. This may involve several rounds as below: Mock Conversion Round 1 Fixing of mismatches observed in round 1 Mock Conversion Round 2 Fixing of mismatches observed in round 2 … It is very important to document the change records during this phase.

2.4.6 Conversion for Parallel Run Usually a pre-production system is setup for parallel run to which the data migration can be performed to ensure that the migration is problem-free. In this phase a one-shot data migration from the source system to the pre-production system is performed. Detailed testing is carried out to ensure that the data migration is fault free. Detailed performance testing and monitoring is also done in this phase.

2.4.7 Conversion for Live System This This is the final final step step of actual actual data moveme movement nt from from source source syste system m to target target system. system. In produc productio tion n systems, this should be done in one shot when the system is not active (usually off hours or holidays). In 24x7 systems, the system may have to be brought do wn to off-line mode for the data movement.

3. General Milestones and Deliverables Generally we envisage documents that include the following: Planning   

Overall Project Plan Document Overall Migration Plan Document System Overview document

 Plan – Sign Off

Analysis & Design  Gap Analysis document





 Design – Sign Off

Schema Migration  Schema Migration Reports  Unit Test Reports (Schema Validation)  Schema Migration – Sign Off

Data Migration  

Mockup Data Migration Reports (including cleanup details) Unit Test Reports

 Data Migration – Sign Off

User Acceptance Test  Test Reports  UAT – Sign Off Installation/Live Run  Live Migration Test Reports  Live Cutover – Sign Off Delivery & Post implementation Support  Parallel Run Reports  Tools & Application Software developed  All other documents  Project – Sign Off

4. Examples of Differences between DB2 and Oracle Terminology

DB2

Oracle

Database

A subsys system ca can ha have mo more th than on one database. Databases are used to logically group application data. All databases share the same system catalogs, system parameters, and processes in the subsystem. DBADM authority is granted on the database level. SYSADM authority is granted at the subsystem level. A data databa base se is logi logica call lly y div divid ided ed into into tablespaces. There are several tablespace types: simple, segmented, partitioned and large partitioned (for

Each instance has one database and one set of system catalog tables.

Table ablesp spac ace e

A database is logically divided into tablespaces. A tablespace tablespace can point to one or more physical database files on disk. One or more tables







Blocks

or more tables. Equivalent to pages; 4 K, 8 K, 16 K, 32 K.

Extents

The unit by which storage is allocated for a VSAM file. The size of the primary and secondary extents is specified in the CREATE TABLESPACE statement. A VSAM file can grow up to a maximum of 119 secondary extents. Extents are made up of contiguous pages.

Stogr togrou oups ps

A ser series ies of of DA DASD vol volu umes assi assign gned ed a unique name and used to allocate VSAM datasets for DB2 tablespaces and indexes. Stored procedures are written in C, C++, COBOL, Assembler, PL/1or the new DB2 SQL Stored Procedure language. The compiled host language is stored on the DB2 server and the compiled SQL is stored on the database.

Stored Procedures

The smallest unit of database storage. Database files are formatted into blocks, which can be from 2 K to 16 K. The unit by which storage is allocated in a database file. The size of the primary and secondary extents are specified in the Storage clause of the CREATE TABLE or CREATE INDEX statements or default to the sizes specified in the CREATE TABLESPACE TABLESPACE statement. stateme nt. Extents are allocated until there is no more free space in the files that make up the tablespace, or the maximum number of extents has been reached. The size of the file is specified in the CREATE TABLESPACE statement. Extents are made up of contiguous blocks of storage. No equivalent.

Written in PL*SQL, JAVA etc. Stored procedures are stored in an Oracle table and executed from within the database.





Index

Secondary Authid

Package

Other examples of differences

table where the data values are stored in the same physical sequence as the index. Allows for fast sequential access. Secondary Authid or RACF Group. Privileges can be granted to a secondary authid. Primary authids are assigned to the secondary authid Group. Primary authids inherit all privileges granted to the secondary authid (group) they are in. A package consists of a single program of executable SQL and the access paths to that SQL. The package is stored on the database and invoked by the host language executable. A package package is created by doing a BIND. A package may be part of a PLAN. PRIQTY SECQTY Smallint\Decimal FREEPAGE etc.

No direct equivalent in Oracle. Groups of privileges known as roles can be granted to a user ID.

No equivalent as known in Oracle. A “package” in Oracle has another meaning. Package is written in PL*SQL and allows you to group all related programming such as stored procedures, functions, and variables in one database object that can be shared by applications. INITIAL NEXT NUMBER FREELIST etc.

5. Database Migration – General Questionnaire (Example) A. Customer Data Customer Name:___________________________________ Phone no.:____________________ Contact Person: ____________________________________Fax no.: ______________________

B. Technical Data





__________________ ____________ __________________ ____________ __________________ ____________ __________________ ____________ __________________ ____________ Have any stored procedures been written to access the database? ( ) Yes ( ) No how many _________ What is the average number of SQL calls per stored procedure? ____________ How long are the stored procedures (total number of statements) ____________ Have any triggers been written to access the database? ( ) Yes ( ) No how many ________ What is the average number of SQL statements per trigger _____________ How long are the triggers (total number of statements)? _____________ Is an archival process in place ( ) Yes ( ) No Brief description of Hardware Configuration _____________________________________________ Brief description of application, third party tools & Host languages __________________________ __________________________________________________________________

B.2 Target System Hardware __________________________ Operating System Version Version ____________

Operating System Name ____________________ Database: Oracle

Database Version Version _________

Do Migration Tools such as “Oracle Migration Workbench” Exist ? ( ) Yes ( ) No Brief description of Hardware Configuration _____________________________________________ Brief description of application, third party tools & Host

Database Migration

Recommend Documents