Hi experts,
I need to only migrate data (tables + data) from a IBM DB2 database TO Oracle database.
Oracle database version: 11.2.0.3 (operating system Linux)
IBM DB2 database version: 10.1 (operating system
z/OS) database size 300 GB
Unfortunately it seems that I can not use the Oracle SQL Developer Migration tool, because it is not compatible with DB2 running on
IBM z/OS.
Oracle say that the latest version of SQL*Developer can be used to migrate from DB2 but only when running on Unix, Linux and Windows platforms, but not currently support migrating DB2 running on IBM z/OS.
It seems that the only possible ways to perform the migration, from DB2 on z/OS to Oracle, are:
Heterogeneous systems
Oracle Transparent Gateway
Oracle SQL Loader
I need to know if someone can tell me step by step how to do at least one of them and/or if there are other ways to migrate.
Thanks in advance!
Oracle Transparent Gateway
Those are the same thing, just different names from different versions.
The DB2 gateway is separately licensed but the ODBC gateway license is included with every licensed Oracle database.
Instructions for using the ODBC gateway can be found here.
http://www.experts-exchange.com/Database/Oracle/A_9850-Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html
As noted in the article you'll be limited to what your odbc driver supports. Also note some other considerations below.
Using sql*loader you could do something fairly simple like...
db2 connect ....
db2 -tv <<!
export to SOMEFILE of del,
select a,b,c,d from yourtable;
quit;
!
then build a sql*loader control file to load the delimited file
A couple things to note regardless of method...
db2 supports CHAR and VARCHAR with sizes > 4000. Unless you are importing into Oracle 12c with max_string_size=extended you won't be able to import them directly. You'll need CLOBs (or LONG but I don't recommend that)
Also note db2 allows zero-length strings in VARCHAR columns. Oracle will treat those as NULL. There is no built-in work around for this. If it's important to maintain the distinction between '' and NULL you'll need to determine some way. Either add a new column or create a "magic" value or use a user-defined data type, etc.