A long journey to migrate DB2 to Oracle

1. The table tbtest exists in the db2 database and has the following types of fields:

Column name	Db2	Oracle
C1	Char (10)	Char (10)
C2	Varchar (10)	Varchar2 (10)
C3	Integer	Number (10)
C4	Date	Date
C5	Timestamp	Timestamp
C6	Decimal (24, 2)	Number (24, 2)
C7	Decimal (12, 7)	Number (12, 7)

2. Create and export data from db2

 
 

  
  
export tbtest.del of del messages tbtest.msg select * from tbtest; 
 
 

Note: There is warning information in the tbtest. msg file. Generally, a record is changed to multiple rows and needs to be modified manually.

3. Create the oracle Data Import Control File tbtest. ctl. The content is as follows:

 
 

  
  
load data 
  
  
infile ‘tbtest.del’ 
  
  
truncate 
  
  
into table tbtest 
  
  
fields terminated by ‘,’ optionally enclosed by ‘”‘ 
  
  
trailing nullcols 
  
  
( 
  
  
c1, 
  
  
c2, 
  
  
c3, 
  
  
c4 date ‘yyyymmdd’, 
  
  
c5 timestamp ‘yyyy-mm-dd hh24.mi.ss.xff’, 
  
  
c6 “to_number(:c6,’9999999999999999999999.99′)”, 
  
  
c7 “to_number(:c7,’99999.9999999′)” 
  
  
)  
 
 

Note: to append data, change truncate to append.

4. Import Data

 
 

  
  
sqlldr shen/shen@db control=tbtest.ctl log=tbtest.log  
 
 

Second:

1. the DB2 db2look command can be used to directly export database information, including table structure, views, functions, and stored procedures (the only pity is that the DB2 system table information is also exported, depressed !).

2. Separate the statement for creating a table and replace Varchar with Varchar2. (Of course, the fields in the table do not have the big data type ).

3. Execute the table creation statement in Oracle SQL developer.

4. Use PB to import tables to Oracle one by one, and the data migration is completed. (There are a lot of data tables. It takes only one day to import them. Is the method stupid ?).

Suggestion:

1. If there are few data tables, you can use the Kettle tool, which can automatically convert the source table structure to the target table structure.

2. It is best to export and import data in PB. At first, I imported tables into Excel and imported them using Oracle tools, which always caused many errors. It is difficult to process Date data, but PB can be automatically converted.

3. the Kettle tool has powerful functions, but it does not have time to study it. It may provide many functions.

4. PowerBuilder and PowerDesign have many features in database changes, table changes, or data migration.

• Database migration should not be ignored
• A long journey to migrate from Oracle to DB2
• Where to go for database migration
• SQL Server database migration remedies
• SQL Server database minimal downtime migration Solution