Data migration from UDB(DB2) to Oracle
Data migration from UDB(DB2) to Oracle
Source: DB Vendor: UDB
UDB Version:v10.5.0.7
UDB Fix Pack:fp7
DB Size: 9 GB
Target DB Vendor: Oracle
Version: 12.2.0.1.0
Schemas:STG_APSH , APSH
Tools/Scripts: SQL Developer & SQL scripts
step 1) Create users a) STG_APSH and b) APSH in Target Oracle Database.
step 2) Get the record count of the Source DB2 tables. — DB2 DBA will provide
step 3) Run the object creation scripts in APSH schema(this will be main target schema)
few insert statements will be there in scripts to load base tables —- Apps SPOC will provide scripts
step 4)Compare the columns/datatypes between source to target object structure
step 5) First, copy all the records from DB2 to oracle into STG_APSH schema by using SQL developer.
Link 1: How to load data from DB2 to Oracle by using SQL developer?
In the above step as per the n/w bandwidth some tables will take more time.
step 6) For some tables every column and datatype is same in DB2 and oracle ( step 4 ),
a.Then execute “insert into APSH.obj1 select * from STG_APSH.obj1.
b.If there are less no.of columns required , then write the insert statements as per the required columns.
c.If there is any constraint violation while inserting data ,create one more staging schema like STG_APSH1.
then insert data excluding those records(STG_APSH) into STG_APSH1 schema.
step 7) After the above step insert all records from STG_APSH1 to APSH schema.
step 8) Compare the source record count and target record count mark that table data load completed as 100%.
step 9) Once all the tables’ data load is completed, then request apps team to start application,
if there is any mismatch then we need to work on those records. – Apps SPOC will provide mismatch count
step 10)We can create as many staging schemas as required based on the errors/issues we face as data in every environment may not be same.
If the data is same then this is not required ,and this helps in migrating data faster.
Link 1 :How to load data from DB2 to Oracle by using SQL developer?
Details:
Source: DB2
Instance name in DB2= APDB
schema name : APSH
Target: Oracle
DB : APDB
Table name: TFL1
SQL>conn APSH/APSH
Connected.
SQL> select count(*) from TFL1;
COUNT(*)
———-
0
Loading Data by using SQL developer
Step 1: Connect to SQL developer
Step 2: Goto source database =>select “APSH” schema?tables? right click on “TFL1”
Step 3:
Select “separate files” in save as field . and type any foldername in “File” drop down box that will contain
data file and control file here “E:\export_oct10_ldr” is given .
Link 2 : sql Loader Control file to load data
Select “yes”
Select next
Select finish
Step 4:
Go to E:\export_oct10_ldr
Data and control files details
Step 5:
Add the tnsentries in tnsnames.ora
Step 6:
Run the sqlldr
Sqlldr userid=APSH/APSH@APDB control=TFL1_DATA_TABLE.ldr log=TFL1_DATA_1.log
Encountered below two errors
Error1:
Record 1: Rejected – Error on table “TFL1”, column “UID”.
ORA-01465: invalid hex number
Above error had been resolved by changing datatype of “UID” Column from RAW to Varchar2(200).
SQL>alter table TFL1 modify UID varchar2(200);
Error 2
Record 1: Rejected – Error on table “TFL1”, column “CREAT_TIMESTAMP”.
ORA-01821: date format not recognized
Below mentioned columns’ datatype changed from date to TIMESTAMP.
SQL>alter table TFL1 modify VALID_FROM_DATE timestamp
SQL>alter table TFL1 modify VALID_TO_DATE timestamp;
SQL>alter table TFL1 modify CREAT_TIMESTAMP timestamp;
SQL>alter table TFL1 modify MUT_TIMESTAMP timestamp;
And below changes to be done in sqlldr control file
In the SQLLDR control file , add the timestamp field shown below.
“CREAT_TIMESTAMP” TIMESTAMP “YYYY-MM-DD HH24:MI:SS.FF” ,
“MUT_TIMESTAMP” TIMESTAMP “YYYY-MM-DD HH24:MI:SS.FF” ,
And rerun the SQLLDR
Sqlldr userid=APSH/APSH@APDB control=TFL1_DATA_TABLE.ldr log=TFL1_DATA_3.log
Link 2: SQL Loader Control file to load data
| OPTIONS (ERRORS=57437) LOAD DATA INFILE ‘E:\export_Nov15_TFL1\TFL1_DATA_TABLE.ldr’ “str ‘{EOL}'” APPEND CONTINUEIF NEXT(1:1) = ‘#’ INTO TABLE “TFL1” FIELDS TERMINATED BY’|’ OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘ TRAILING NULLCOLS ( “DOC_NR” , “VERS_NR” , “COMP_NR” , “LANG_C” , “DESCR” CHAR (255), “TITLE” CHAR (255), L_0 FILLER char, “COMP_TEXT” LOBFILE( L_0) TERMINATED BY EOF NULLIF L_0 = ‘null’, L_1 FILLER char, “NOTICE” LOBFILE( L_1) TERMINATED BY EOF NULLIF L_1 = ‘null’, “HELP_LINK” CHAR (255), “CREAT_TIMESTAMP” TIMESTAMP “YYYY-MM-DD HH24:MI:SS.FF” , “CREAT_USER_ID” CHAR (20), “MUT_TIMESTAMP” TIMESTAMP “YYYY-MM-DD HH24:MI:SS.FF” , “MUT_USER_ID” CHAR (20), “DELETE_IND” , L_2 FILLER char, “COMP_TEXT_ZIP” LOBFILE( L_2) TERMINATED BY EOF NULLIF L_2 = ‘null’, L_3 FILLER char, “NOTICE_ZIP” LOBFILE( L_3) TERMINATED BY EOF NULLIF L_3 = ‘null’, “MANUAL_CHAPT_NR” CHAR (32)) |
See Also: