Re: SQL*Loader and mainframe data

Neil Sauerwein <neils_at_ptd.net> wrote in article <3429102F.50D94D1B_at_ptd.net>...
> I need to get mainframe data from both DB2 and VSAM to populate an
> Oracle database on AIX. Since there are over 200 tables,I was planning
> on using SQL*Loader by running the creates on DB2, generating the
> SQL*Loader control files using the DB2 unload utility, and downloading
> these files along with the data which will be formatted in table format
> by COBOL programs. Of course I found that after FTPing the data, the
> binary record lengths for the varchars had been rendered unusable. Does
> anyone have any experience with something like this? Is there an easier
> way? Should I forget SQL*Loader and deal with each table in JAVA and
> PL/SQL?
I've sat many a night loading mainframe data into Oracle... :-)

The best method for us is fixed length dump of a table from the mainframe db - then a COBOL program that converts any COMP (computational fields), ftp to UNIX and the loading it as fixed length into Oracle.

Here's how one of our control files looks like:

--
LOAD DATA
INFILE '$FILE' "fix 296"
APPEND INTO
TABLE bs2000.Members
(
   dbkey1                position(001:004) raw,
   dbkey2                position(005:008) raw,
   dbkey3                position(009:012) raw,
   dbkey4                position(013:016) raw,
   member_unique_number  position(026:033),    
   ...etc...
---

Oh yes, the dbkeys above are 4 byte hexnumbers containing the primary key
number and foreign keys. You're lucky to have DB2 - we have to work with an
old hierarchical database. :-)

regards,
Billy