Solved

Migration from DB2 on z/OS to Oracle

Posted on 2014-01-03
20
3,239 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
  • 7
  • 6
  • 6
  • +1
20 Comments
 
LVL 73

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 73

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
 
LVL 73

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 24

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 24

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 73

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 73

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 24

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 24

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 24

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 73

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 24

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now