Re: Problem: MVS/DB2 Unload data to AIX/ORACLE.

bmanry_at_upsizeme.us.oracle.com (Bill Manry - Oracle Corporation) wrote:

>The SQL*Loader CHARACTERSET option is not a general solution
>to the problem of loading "foreign" data. It takes care of the
>character data but not the numbers (binary ints, packed decimal,
>and floats). If the representation of these number types
>differs between your two platforms (for example: "normal" versus
>"byte-reversed" ints), Loader will fail or produce row data
>other than what you expect.

Indeed, as in my case, it failed.

In case anyone else is working with the same kind of problems:

After posting the main subject article on this, I've managed to dig out enough information on this (usenet archives & web-sites) to successfully build a setup that can run in an online production environment.
The solution seems to be to unload all DB2-data to character format on MVS, then transfer (ftp) all data as ascii to the Unix-platform, thus avoiding any use of Loader CHARACTERSET options.

There is a utility (mentioned about a year ago on this n.g.) from Texas Instrument called DB2ORA that unloads data to character format, and at the same time generates a load-description for use with SQL*Loader. The utility is basically a modified DNSTIAUL (IBM-util). It will also handle differences in datatypes (DATE/TIME). However, I chose not to use this.
Instead, I did the DB2-unload as QMF-queries, writing data in DB2-IXF character-format. This gives you a characterfile where the header describes the DB2-table (attributes/lenghts). To simplify things a bit, I also did an ordinary DNSTIAUL on the tables, because it writes out a DB2 load-description (SQL*Loader can use these when slightly modified).

Note: all this applies to DB2-tables that contains SMALLINT/DECIMAL/ FLOAT data. If you've only got CHARACTER and DATE, ordinary DNSTIAUL unloads will do the job (and give you a nice control-file to use when loading).
Håkon Eide - hei_at_sn.no Received on Fri Sep 06 1996 - 00:00:00 CEST