Transform data from DBase IV to DB2 with VB6.

[MrSatan]

Hi All,

I need to transform large amount of record from DBase IV free tables into DB2 v7.2. The total DBase files size is 1.94GB and is around 12 million records. The process has to be run as much "Unattended" as possible, and have to finish within 2 days.

Currently I am using ADO in VB6 to connect to both DBase IV and DB2, then pull data out of DBase, transform the record and insert into DB2, the process is running fine, except it takes more than 10 days to complete the whole process...:-(

Then instead of doing the transformation in VB, I simply pull data out of DBase and insert into DB2, and do the transformation within DB2, but the whole process still take more than a week...:-(

The bottleneck is due to 2 different databases are involved and I have to deal with one record at a time.

Any suggestion that can improve the process time will be much appreciated.

Thank in advance.

 

[BKtechie]

Can you write a report to run out of dBase to generate a fixed width text file then write an import utility in DB2 to grab it?
You can do all the formating in the report and just step through the file. It will be 2 very long steps, but its faster than using a PC to transfer the data between the two databases.

BKtechie

 

[MrSatan]

Thank you for your suggestion. Your idea does really help me out.

Since the process has to be run as much "Unattended" as possible, so I have created a FoxPro object which initialized by VB to create the formatted text file, and call db2cmd command from VB to load the text file into DB2. So the user really need to click a button and the whole process will finish in hours...

Once again, thank you very much.