Author Topic: Moving from DB2 to Oracle  (Read 16433 times)

sandeepveldi

  • Guest
Moving from DB2 to Oracle
« on: January 31, 2011, 09:59:33 AM »
Hi All,
I'm trying to find out:
1) What are the limitations in using Oracle Vs DB2?
2) Are there any issues that I might encounter while doing a migration from DB2 to Oracle?
3) Are there any performance implications with Oracle Vs DB2?

Any help on this is highly appreciated.

Regards,
Sandeep Veldi

Alessandro Perucchi

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1001
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #1 on: January 31, 2011, 02:43:33 PM »
Hi,

Well, I have done migration from DB2 <-> Oracle
I mean by migration, it was really a database conversion, and not a export/import from the index and data.

So from my experience, I've seen no real performance difference between Oracle and DB2.

And you must be aware that all the numerical fields in Oracle takes 8 bytes (for small Int, Integer, Big Integer, ...) so in the end it take a lot more space than DB2, which reserves exactly the number of bytes that you expect.
So in the last migration I went from a 370 Gig Library Database in Oracle to a "mere" 160 Gig in DB2, no compression involved.

If you are doing the DB2 -> Oracle migration, you need to be carefull, because the name of some fields are not the same (resourceid and res_id), the types are not always the same (Annotation: Oracle use CLOB and DB2 use varchar), etc... so this is not an easy transition, but it is possible, and you need to test it very carefully.
Depending on the CMOD Version you are using you need to be careful of the data type. before 8.4.1 Oracle knew only Integer and small Int... no Big Int was supported. So you might have here some nasty tricks to do if you want to convert to DB2 and have sometimes Integer or Big Integer.

You need to know also that, you are on your own, since it is not officially supported by IBM.
If you are taking the risk, then there will be at IBM some services people (like me) who could help you in the process.



May I know the reasons for considering the migration from DB2 to Oracle?

I'm asking because from my point of view if a product works, don't change it. I was working for a company where I was managing CMOD with DB2, and in 10 years we had no issues, it was working without any problems. And suddently the management said... the Software RoadMap is Oracle, and we need to migrate all product that can be migrated to oracle.
We did the work, but I still find it st*pid, especially for a database where we had 0 problems in 10 years (with updates too) except for filling filesystems... Only because nobody knew how to operate DB2, and they were fearing the day they might need someone, and at the same time, they had lots of Oracle DBA...
Now, these Oracle DBA are ... playing around with the internal of CMOD... which is for me worse than having supposively no one to take care of DB2.

So I am always wanting to know the reasons someone wants to migrate CMOD from DB2 <-> Oracle, especially if there are no issues!

Cheers,
Alessandro
Alessandro Perucchi

#Install #Migrations #Conversion #Educate #Repair #Upgrade #Migrate #Enhance #Optimize #AIX #Linux #Multiplatforms #DB2 #Windows #Oracle #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #JavaApi #ContentNavigator #ICN #WEBi #ODWEK #Services #PDF #AFP #XML

sandeepveldi

  • Guest
Re: Moving from DB2 to Oracle
« Reply #2 on: January 31, 2011, 02:54:25 PM »
Alessandro,
Thanks for your response.
It's just an enterprise level initiative that's driving us to move towards Oracle. There are no issues/concerns with the existing system which has been there for more than 12 years using DB2 as the back end database.

I'm just trying to find strong reasons for not moving to Oracle.

Can you please provide any highlevel steps that you usually perform for a CMOD database migration from DB2 to Oracle?

Regards,
Sandeep Veldi

pankaj.puranik

  • Sr. Member
  • ****
  • Posts: 374
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #3 on: January 31, 2011, 10:43:23 PM »
Yes..any pointers to any documentation would also be helpful.
I am also interested in knowing how these kind of migrations are done.

And Sandeep, if your management decides to go for Oracle, it would be great if you could note down the steps during the migration process and post it here.

Alessandro Perucchi

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1001
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #4 on: February 01, 2011, 02:37:45 AM »
Hello Sandeep,

the only 2 strong reasons are the following:
- You will need to pay the CMOD license AND Oracle License (because with CMOD, DB2 License is "free" as long as you use it only with CMOD)
- The other reason is the risk involved by such move, and no official support from IBM

Another reason, maybe less important is:
- The size of the library server will be bigger (around 100% bigger in average than the DB2 version if using lots of small int/integer fields)

From my experience, I've seen, that Oracle DBA loves to play with internal from some CMOD... and personally this is the biggest reason, BUT for management maybe this is not a strong reason.


Now, I see that there is some people interested in such migration :-D Hmmmm What should I do?  ;D

So first, there is no official documentation, even unofficial. I have written some scripts to do it automatically, and it depends a lot on the customer data.
Now with the newest version of CMOD, I have no more problems with the conversion between number types (small int, integer, big int), so I might simplify some parts.

Well I will check what I have and explain in high level how it works, in another post.

Just for your information, all the information I'm going to write is without ANY WARRANTY, you are on your own, and IBM is officially not supporting such migration, and I won't be held responsible for any kind of corruption, loss of data, etc... due to the explanation I'm going to write in my next post.

Cheers,
Alessandro
Alessandro Perucchi

#Install #Migrations #Conversion #Educate #Repair #Upgrade #Migrate #Enhance #Optimize #AIX #Linux #Multiplatforms #DB2 #Windows #Oracle #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #JavaApi #ContentNavigator #ICN #WEBi #ODWEK #Services #PDF #AFP #XML

sandeepveldi

  • Guest
Re: Moving from DB2 to Oracle
« Reply #5 on: February 01, 2011, 07:30:13 AM »
Alessandro,
Trust me. You won't be held responsible for anything  ;D

Just FYI...our system has the following versions of software
CMOD - 8.4.1.3
DB2 - 9.5.5
TSM - 5.4.2.0
AIX - 5.3

Please let me know your inputs on migration strategy for this environment, if possible.

Regards,
Sandeep Veldi

Trambak

  • Guest
Re: Moving from DB2 to Oracle
« Reply #6 on: February 01, 2011, 11:26:02 AM »
Thanks Alessandro. We are all eagerly waiting for your migration related post. Will the size have such a huge increase on migration to Oracle if we store most of the indexes currently as string on DB2 rather than integer.

Alessandro Perucchi

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1001
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #7 on: February 01, 2011, 11:42:20 AM »
Thanks Alessandro. We are all eagerly waiting for your migration related post. Will the size have such a huge increase on migration to Oracle if we store most of the indexes currently as string on DB2 rather than integer.

If you are storing mainly strings, then no... no differences.

Cheers,
Alessandro
Alessandro Perucchi

#Install #Migrations #Conversion #Educate #Repair #Upgrade #Migrate #Enhance #Optimize #AIX #Linux #Multiplatforms #DB2 #Windows #Oracle #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #JavaApi #ContentNavigator #ICN #WEBi #ODWEK #Services #PDF #AFP #XML

Alessandro Perucchi

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1001
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #8 on: February 01, 2011, 12:08:41 PM »
Well, I think I will disapoint some people :-)

But as you know, I work for IBM, and we are not really happy to know that you are going away from DB2 to the evilnice Oracle!!! 8)

So I will explain you how to migrate from Oracle to DB2  ;D The reverse is left to the reader as an exercise!!  :D

DISCLAIMER

THERE IS NO WARRANTY FOR THE EXPLANATION PROVIDED, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING I WILL PROVIDE THE EXPLANATION ?AS IS? WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE EXPLANATION IS WITH YOU. SHOULD THE EXPLANATION PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.


I have automated some of what I describe in my post with scripts, but of course I cannot share them with you. I hope you understand.
I can only give you an explanation of what you need to be careful if you try to do the conversion yourself.

Conversion Oracle - DB2

0) BE SURE TO USE THE SAME INSTANCE NAME IN ORACLE AND DB2, otherwise you will loose the link with the local cache and with TSM.
1) Be sure to have a new working db2 installation configured only with the cache.
2) Then stop the DB2 and Oracle CMOD environment (but not the databases!)
3) Copy the content of all the ARS* tables from Oracle -> DB2
Please be careful with the table ARSANN and ARSAGPERMS, you need to find a way to convert the CLOB in VARCHAR some fields.
4) Then recreate all the segment table and repopulate them by reading the definition in the Oracle table syscat.columns and syscat.tables.
Please be careful, because the internal fields from each document index are not exactly the same between the 2 databases, for example in DB2 you have RESOURCE, but in Oracle the field is called RES_ID, and the type of COMP_TYPE and ANNOT are type RAW(1) in Oracle and CHARACTER(1) in DB2
You will see also that fields in Oracle are CHAR(n) and in DB2 they are called CHARACTER(n), or VARCHAR2(n) in Oracle and VARCHAR(n) in DB2.
When converting from Oracle -> DB2 you need to be extra carefull because Oracle has everything as number, and in DB2 they need to be converted correctly in SMALL INT, INTEGER or BIG INT. To help you can check for each field what type they are by looking at the ARSAGFLD in the Oracle Database.
If you are using CMOD <=8.4.0.X, then you need to be extra careful with all the fields of type integer in Oracle, because it might required a type BIG INT in DB2, so you need to check before with all your data, if some values are greater than 2147483647 and change accordingly the field type when you create the segment table AND you need to change the type in the ARSAGFLD, which means also that you need to change also the type of the field mapped in the folder... and if you do that with the folder, it might have a consequence with other Application Group where you will need to change the corresponding field... and guess what, it might have an influence in other folders, and other Application Groups as a side effect!!!
If you are using CMOD 8.4.1.X then you don't have these problems, except if your Oracle CMOD Data model was created before 8.4.1.X, and then you need to be careful on that aspect too!
One way to see what are the fields to be careful would be to export the whole data model from Oracle to DB2, and load one document in each Application Group and compare all the tables.
5) Of course, you need then to recreate all the index that CMOD created for you, so you need to check in the Oracle table sys.user_ind_columns which field is an index, and you need to recreate it back in DB2 with exactly the same name.
6) You might need also to be careful with the tablespace created with each segment table.
7) You might need to change in the folder MAX value and MIN value for the numerical fields, in case you made some modification with numbers in point 4)
8 ) You will need to reset the value of the last expiration date of password, otherwise all your passwords are going to be expired
9) You will need to be careful before starting CMOD with DB2, that you have a correct ars.ini pointing to the correct configuration files, and that the cache are point to the cache used by Oracle (be carefull with the permissions in case you are using different users), that TSM is configured correctly (if you are using TSM). take the oracle configuration as a template for your DB2 new instance.
10) After checking that the content of each tables are ok, that the configuration files are ok, and that all the permissions are ok, you can start CMOD in DB2
11) if CMOD DB2 starts OK, then you can try EVERY functions (log with a user, change password, load a document, retrieve a document, launch arsmaint, delete a document, etc...)
IF CMOD DB2 doesn't start... then, you need to check everystep to see what went wrong.
12) If Step 11 is ok, then you have a newly migrated CMOD in Oracle DB2  :D ;)

I have found that DB2 Federation Server is just perfect to populate tables with CLOB/BLOBs between Oracle/DB2, and all the conversion of data types.
Maybe Oracle has a similar tool to interface with DB2, but I'm not aware of it.
Before using DB2 Federation Server, I was doing everything by hand... all the conversion of types, etc... and that was really a nightmare.
Now I can use it for Oracle<->DB2 conversion.

My experience tells me, that such migration is not trivial, and should be prepared very carefully, otherwise you will have problems.

Cheers,
Alessandro
« Last Edit: February 01, 2011, 01:28:50 PM by AlessandroPerucchi »
Alessandro Perucchi

#Install #Migrations #Conversion #Educate #Repair #Upgrade #Migrate #Enhance #Optimize #AIX #Linux #Multiplatforms #DB2 #Windows #Oracle #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #JavaApi #ContentNavigator #ICN #WEBi #ODWEK #Services #PDF #AFP #XML

Alessandro Perucchi

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1001
    • View Profile
Re: Moving from DB2 to Oracle
« Reply #9 on: February 02, 2011, 12:19:06 AM »
Just FYI...our system has the following versions of software
CMOD - 8.4.1.3
DB2 - 9.5.5
TSM - 5.4.2.0
AIX - 5.3


Hello Sandeep,

since it's a pure database conversion, it doesn't matter if it is AIX, Sun, HP, Windows or Linux (at least all the Multiplatform, for z/OS I don't know, but Oracle is not on z/OS :-P ).

The only think that might matters is really the version of cmod. But mostly when you go from Oracle -> DB2, the reverse is normally """easier""", but now with version 8.4.1+ you don't have the problems of numerical fields.

Cheers,
Alessandro
Alessandro Perucchi

#Install #Migrations #Conversion #Educate #Repair #Upgrade #Migrate #Enhance #Optimize #AIX #Linux #Multiplatforms #DB2 #Windows #Oracle #TSM #Tivoli #Performance #Audits #Customizing #Availability #HA #DR #JavaApi #ContentNavigator #ICN #WEBi #ODWEK #Services #PDF #AFP #XML