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!!!
So I will explain you how to migrate from Oracle to DB2
The reverse is left to the reader as an exercise!!
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
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
