Converting data from C-ISAM to Oracle

Migrating data with Uniface systems is far less painless than with out it, however there are plenty of times when it would be more convenient to use Sybase BCP or Oracle SQL*Loader to load data in the database.

In 1999, March Hare were presented with just such a case. A customer needed to move over 500,000 records from a C-ISAM based system to an Oracle one. The structure of the data would be changing considerably, and they needed to be able to run the data migration easily several times.

After considering using Uniface to do a direct copy, they selected the March Hare UD8/CSV driver because:

It would be considerably faster to load using SQL*Loader

SQL*Loader provides more functionality for 'massaging' the data on the way through.

CSV files were more readily understood by the development team than TRX files.

Once installed on their NCR 3000 series server running System V UNIX, it made short work of the 500,000 records.

The assignment file setting was, e.g.:

[DRIVER_SETTINGS]
USYS$UD8_PARAMS=alert, separator \xb6

This combination used a separator that was unlikely to appear in the data, and an alert message to warn if the separator HAD been found in the data.

An example SQL*Loader control file

LOAD DATA
INFILE 'MASTER.csv'
INTO TABLE TMP_MASTER
FIELDS TERMINATED BY "¶"
TRAILING NULLCOLS
(
MS_SEQ SEQUENCE(1,1),
MS_ITERATION CONSTANT 0,
MS_CONVERSION_STATUS CONSTANT " ",
MS_SORT,
MS_BNAME,
MS_BTITLE,
MS_BADDR1,
MS_BADDR2,
MS_BADDR3 ,
MS_BPCODE ,
MS_BW_PHONE ,
MS_BH_PHONE ,
MS_B_FAX,
MS_GTITLE,
MS_GSORT ,
MS_GNAME,
MS_GADDR1,
MS_GADDR2 ,
MS_GADDR3,
MS_GPCODE ,
MS_GW_PHONE,
MS_GH_PHONE,
MS_G_FAX ,
MS_WDATE "TO_DATE(:MS_WDATE,'YYYYMMDD')",

MS_RADDR1,
MS_RADDR2 ,
MS_RADDR3 "TO_DATE(:MS_RADDR3,'YYYYMMDD')" ,
MS_RPCODE,
MS_DADDR1,
MS_DADDR2 ,
MS_DADDR3 ,
MS_DPCODE,
MS_INST ,
MS_INST2 ,
MS_BP_NAME,
MS_BP_PHONE,
MS_GP_NAME ,
MS_GP_PHONE ,
MS_RDATE "TO_DATE(:MS_RDATE,'YYYYMMDD')" ,
MS_STORE,
MS_GCNT,
MS_RCNT,
MS_RAMT,
MS_BCNT ,
MS_BAMT ,
MS_DELCLOSE "TO_DATE(:MS_DELCLOSE,'YYYYMMDD')" ,
MS_OCCASION)

- UD8/CSV driver is available for all Uniface supported platforms

$Revision: 1.7 $ $Date: 2011/02/28 02:56:32 $

[go to top]