Oppolzer - Informatik / Blog

When transferring Data from DB2 to ORACLE (or vice versa),
I would recommend using a file format, which I call STANDARD
(the tool supports different file formats, but for this use case,
STANDARD is best).

In the STANDARD format, every data field has room for a NULL indicator byte,
so the DB2 null values are transferred to ORACLE NULLs without problem.

Loading data into ORACLE tables is done by INSERT statements
or by an abbreviation, which is called LOAD (LOAD gets the table structure
first by issuing a dynamic prepare for SELECT *, and then it knows, how
the INSERT has to look).

When using my tool, you can specify an SQLERROR clause. This way you can
tell an alternate SQL statement, that should be executed, if the original statement
fails. A similar clause is the NOTFOUND clause; the typical use for NOTFOUND is:

UPDATE tablex SET a = :a WHERE key = :key
NOTFOUND
INSERT INTO tablex VALUES (:key, :a, ...);

without an SQLERROR clause, the LOAD statement stops.
You can also specify an empty alternate statement; this way, the error is only
diagnosed, but execution continues.

Please feel free to ask if you want to know more details.

Kind regards

Bernd



Am 02.10.2014 04:10, schrieb C.B.:

How does your product handle db2 null value transfer to oracle null.
How does it handle exceptions while loading data into oracle.



From: Bernd Oppolzer [mailto:db2-l@lists.idug.org]
Sent: Wednesday, October 01, 2014 5:23 PM
To: db2-l@lists.idug.org<mailto:db2-l@lists.idug.org>
Subject: [DB2-L] - RE: Comparison of data between DB2 & Oracle

I am a small software company, and I have a extract / transfer / load utility
which is working with both DB2 and ORACLE (in fact, two versions, one
for DB2 and one for ORACLE, but with the same function) and which is able to unload the data
into a common flat file format that is ideal to do the compare. You can choose
between different target formats, for example simple flat text files (fixed offsets),
CSV, different XML formats, a tool specific transfer format including meta data
and more.

The unloads and loads can be controlled by SQL statements, so: if there are
minor differences in the DB structures, this is no problem - you will be able
to construct comparable files anyway.

BTW: the migration would have been much simpler using this tool, and there
would be no need to test the data after the migration, because the tool was
already used in many migrations of that kind - both directions. And, of course,
migrations without changing the DB system, too. The tool supports remote access,
too. For example, DB2 z/OS to DB2 LUW or the other direction.

If you are interested to know more about this, please contact me offline.

Kind regards

Bernd

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----