Migration from DB2 on z/OS to Oracle

Hi experts,
I need to only migrate data (tables + data) from a IBM DB2 database TO Oracle database.

Oracle database version:  (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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Heterogeneous systems
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.


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;

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.
Also note some code may operate differently due to the way trailing spaces are handled

In DB2:  "x" = "x " = "x       "
In Oracle, each of those is distinct
ralph_reaAuthor Commented:
Does anyone have instructions for using Heterogeneous systems/Oracle Transparent Gateway?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

what do you need beyond what is in the article above?

If you mean something specific to the DB2 gateway product; first are you licensed for it?  it's not free or included. Also, what do you need from the DB2 gateway that the ODBC gateway doesn't provide.

And last, even though the gateway would probably suffice, it'll probably be easier and faster to use the command line tools.  Install the  db2 client, do the export into delimited files and then load with sql*loader.

I'm in the middle of a long (19 months and counting) conversion project moving many applications from db2 to oracle and the hardest part is not the data move, it's the application changes afterward as noted above.
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Both DB2 10.1 on z/OS and Oracle support exporting and importing of CSV delimited files.
You could use that. Use JCL batch (Mainframe utilitity) to export the data from DB2 to a CSV file and then SQLLoader to import the data.
Just make sure that you unload the data in the same character encoding scheme as used by your Oracle DB.

    Tomas Helgi
ralph_reaAuthor Commented:
Tomas Helgi ,
Can you show me some example step by step, using JCL batch (Mainframe utilitity), on how to export from DB2?
My database DB2 has more than 2000 tables, Can you tell me how do I automatically export all the tables?
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Take a look at the manual and see the sample unload statements

For your second question look at the expamples 8 and 9 and you can also see some more examples on the LISTDEF by scrolling up in the left menu. :)

    Tomas Helgi
ralph_reaAuthor Commented:
I have on DB2 for z/OS many columns with datatype CLOB and BLOB.

How can I migrate these datatypes from DB2 to Oracle?
export to some_file.txt
of del
modified by chardelX1C
select * from yourtable' ;

depending on the content of the CLOB/BLOB you might want need to change the CHARDEL to some other delimiter that does NOT appear inside the data.

In your control file you can then specify the same delimiter
ralph_reaAuthor Commented:
some_file.txt or some_file.csv are txt file.

Text and CSV files are text (character) based. NOT BINARY.

How Can convert these txt files into BLOB (binary)?
the csv/txt holds binary data just as well as text data.

a byte of data is a byte of data.

Is a byte in hex "00"  a null character or a binary value with all bits 0 ?
Is a byte in hex "20" an ascii space or a binary value with bits 00100000  ?

When you read the file, you get to tell sql*loader how to read the bytes.  If you tell it a string of bytes is text it'll load into a char/varchar/clob,  if you tell it a string of bytes is binary it'll load into a raw/blob
ralph_reaAuthor Commented:
Interesting answer.
Could you please give a practical example?
You can find attached the csv file that I exported from DB2 with the following command:

db2 "export to exp_BLOB.csv of del modified by coldelX1C
select * from EMP_PHOTO"

describe EMP_PHOTO:

Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Here are datatype comparison guides and other useful info for migration

This is a Redbook for Oracle to DB2 migration but you can find usefull info there.

    Tomas Helgi
ralph_reaAuthor Commented:
Below the export from DB2 LUW and Import into Oracle Database with SQL*Loader run correctly:

db2 export to EMP_PHOTO.dat of DEL lobs to .\ modified by lobsinsepfiles coldel"#" datesiso nochardel "select EMPNO,'<EOFD>',PHOTO_FORMAT,'<EOFD>',PICTURE,'<EOFD>', '<EORD>' from DB2INST1.\"EMP_PHOTO\""

Open in new window

load data
infile 'C:\DB2INST1.EMP_PHOTO.dat'
 "str '<EORD>\n'"
into table SCOTT.EMP_PHOTO
fields terminated by '#<EOFD>#'
trailing nullcols

Open in new window

But when I run the export script from DB2 for z/OS (DB2 client installed on windows platform) I get the error:
The system cannot find the file specified.

C:\export_db2>db2 export to LOB_TABLE.dat of DEL lobs to C:\export_db2 modified by LOBSINSEPFILES coldel# datesiso nochardel select USER_CD,'<EOFD>',DATA_INS,'<EOFD>',ATTACH,'<EOFD>',USER_DS,'<EOFD>',<EORD>' from DB2INST.LOB_TABLE
The system cannot find the file specified.

How can I export correctly on DB2 z/OS platform?
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Try executing the command like this

db2 "export to LOB_TABLE.dat of DEL lobs to C:\export_db2 modified by LOBSINSEPFILES coldel# datesiso nochardel select USER_CD,'<EOFD>',DATA_INS,'<EOFD>',ATTACH,'<EOFD>',USER_DS,'<EOFD>',<EORD>' from DB2INST.LOB_TABLE"

You can always use the export utility.
If you have DB2 Administration Tool for DB2 on z/OS you can use that tool generate a JCL BATCH utility.
In the DB2 Administration Tool in TSO
1) Choose 1 - System Catalog
2) In Name put your table of choice and owner/schema and choose T press CTRL (Enter in TSO :) )
3) In front of the table you write U.UL or UT and choose then UL. and press CTRL.

At this point you will have a JCL Unload batch job ready for editing and submitting.

   Tomas Helg
ralph_reaAuthor Commented:
I tried but I get this error:

SQL1325N  The remote database environment does not support the command or one of the command options.

Have you any idea?
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Unfortunatly you can't unload data from tables residing on Mainframe using your client.
You will have to use the Mainframe utility to unload the data.

    Tomas Helgi
Unloading data from your mainframe to download to a non-mainframe environment may pose a few problems.

z/OS stores text data as EBCDIC.  So anything that is text will need to be converted from EBCDIC to ASCII.  Which can be done automatically if you use FTP to transfer from z/OS to the distributed box.

Anything that is packed decimal will need to be changed to text before being downloaded.

Binary data (other than packed decimal) will need to be transferred using FTP as binary so that it is not run through the EBCDIC to ASCII conversion process.
you can convert from EBCDIC to ASCII with the CONVERT function

CONVERT('your mainframe ebcdic value',
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:

Unload utility can convert to desired character set by setting desired CCSID (see unload syntax )

MORE info in the manual
LOB UNLOAD in the manual
Look at examples 9 , 10, and 11

I also recommend using TEMPLATES and LISTDEF to make it easy to unload from multiple tables in one go. Examples 7 and 8  :)

    Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.