Migration from DB2 on z/OS to Oracle

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.

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

Does anyone have instructions for using Heterogeneous systems/Oracle Transparent Gateway?

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.

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

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?

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

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

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)?

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

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

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

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\""

Select all 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
)

Select all 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?

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

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?

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