Solved

Migration from DB2 on z/OS to Oracle

Posted on 2014-01-03
20
3,474 Views
Last Modified: 2014-01-28
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!
0
Comment
Question by:ralph_rea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 6
  • +1
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39754022
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.

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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39754082
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
0
 

Author Comment

by:ralph_rea
ID: 39755863
Does anyone have instructions for using Heterogeneous systems/Oracle Transparent Gateway?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39756161
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.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39757235
Hi!

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.

Regards,
    Tomas Helgi
0
 

Author Comment

by:ralph_rea
ID: 39757320
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?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39757435
Hi!

Take a look at the manual and see the sample unload statements
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_utl_unload.htm

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. :)


Regards,
    Tomas Helgi
0
 

Author Comment

by:ralph_rea
ID: 39761902
I have on DB2 for z/OS many columns with datatype CLOB and BLOB.

How can I migrate these datatypes from DB2 to Oracle?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39762935
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
0
 

Author Comment

by:ralph_rea
ID: 39765289
sdstuber,
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)?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39765311
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
0
 

Author Comment

by:ralph_rea
ID: 39765737
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
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO SYSIBM CHARACTER 6 0 No
PHOTO_FORMAT SYSIBM VARCHAR 10 0 No
PICTURE SYSIBM BLOB 102400 0 Yes
exp-BLOB.csv
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39766415
Hi!

Here are datatype comparison guides and other useful info for migration
http://docs.oracle.com/cd/E35137_01/doc.32/e18460/oracle_db2_compared.htm
http://en.wikibooks.org/wiki/Oracle_and_DB2,_Comparison_and_Compatibility/Storage_Model/Data_Types/Summary

This is a Redbook for Oracle to DB2 migration but you can find usefull info there.
http://www.redbooks.ibm.com/abstracts/sg247736.html?Open

Regards,
    Tomas Helgi
0
 

Author Comment

by:ralph_rea
ID: 39770501
Hi,
Below the export from DB2 LUW and Import into Oracle Database with SQL*Loader run correctly:

#DB2 EXPORT#
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

#SQL LOADER#
load data
infile 'C:\DB2INST1.EMP_PHOTO.dat'
 "str '<EORD>\n'"
into table SCOTT.EMP_PHOTO
fields terminated by '#<EOFD>#'
trailing nullcols
(
EMPNO ,
PHOTO_FORMAT ,
PICTURE_ref  FILLER,
PICTURE LOBFILE(PICTURE_ref) TERMINATED BY EOF
)

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?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39770745
Hi!

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.

Regards,
   Tomas Helg
0
 

Author Comment

by:ralph_rea
ID: 39770809
Tomas,
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?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39776507
Hi!

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.

Regards,
    Tomas Helgi
0
 
LVL 57

Assisted Solution

by:giltjr
giltjr earned 100 total points
ID: 39776522
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.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 39776811
you can convert from EBCDIC to ASCII with the CONVERT function

CONVERT('your mainframe ebcdic value',
           'US7ASCII',
           'WE8EBCDIC500'
       )
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 300 total points
ID: 39777093
Hi!

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

MORE info in the manual
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_unloadlobdata.htm
LOB UNLOAD in the manual
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_unloadlobdata.htm
Look at examples 9 , 10, and 11
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_unloadlobdata.htm

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

Regards,
    Tomas Helgi
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question