Data migration

[Will Honea]

I have a mess of old Foxpro data from 1995 to present - each using various
version of Foxpro, naturally - that I need to migrate into DB2 Express-C 9.
So far, none of the tools I have found lend themselves to anything like a
reasonable process, or at least one that can be scripted.

Because of some ill-advised re-assignment of reference values and table
formats across the years, the composite result needs to be rebuilt in
chronological sequence. The input set consists of 25 sets of data
representing 50 - 65 .dbf files each so the value of scripting is apparent.
So far, nothing I can find existing extracts the field information that I
know is embedded in the original files so even creating the DB2 tables is a
long, tedious prospect.

Can anyone point me to a program or some useful reference that would permit
me to write code to automate some of this work? The DB2 migration toolkit
solves most of the import side, but I'm having little luck with the export
side.

[f***@yahoo.com]

If you are good at MS Access, then you may use it to get the desired records out of the FoxPro databases and it allows you to link to the tables from DB2 at the same time and you may directly insert them into DB2. Access offers the flexible way to manipulate the source records before inserting them into target database. The good part is it links tables from different databases into one work space screen. For file source, you create codes to read them into access temp tables, which is a piece of cake. If it's excel file or delimited text file, then you don't have to write codes.

I did some migration from other database into UDB with 8B in size, of course it took some time and need to have some tricks to speed up the processing.

[Blair Kenneth Adamache]

Post by Will Honea
I have a mess of old Foxpro data from 1995 to present - each using various
version of Foxpro, naturally - that I need to migrate into DB2 Express-C 9.
So far, none of the tools I have found lend themselves to anything like a
reasonable process, or at least one that can be scripted.
Because of some ill-advised re-assignment of reference values and table
formats across the years, the composite result needs to be rebuilt in
chronological sequence. The input set consists of 25 sets of data
representing 50 - 65 .dbf files each so the value of scripting is apparent.
So far, nothing I can find existing extracts the field information that I
know is embedded in the original files so even creating the DB2 tables is a
long, tedious prospect.
Can anyone point me to a program or some useful reference that would permit
me to write code to automate some of this work? The DB2 migration toolkit
solves most of the import side, but I'm having little luck with the export
side.

Can you select all data from each Foxpro table with an order by that
gives you the desired sequence for import, and pipe the results of the
select to a file. This could be an ASCII file for DB2 to import.

[f***@yahoo.com]

If I understand you correctly, what you want is to output all Foxpro tables' records into one delimited xxx.asc or xxx.txt file that these table records should be output in certain order.

If yes, you can do this with MS Access. I give you one example manually, you link the table 'xyz' in Foxpro into Access, then open it, then you can from File/Export to export its records into delimited xyz.asc or xyz.txt or even XML file, then you may do so to other tables, then you may copy/append these into one file.

But you may write code to autmate this process to output these tables' records into one file, if you prefer.

Good thing is that your boss will be happy since you are using the existing software, and it is very easy to learn.

[Will Honea]

Post by Blair Kenneth Adamache
Can you select all data from each Foxpro table with an order by that
gives you the desired sequence for import, and pipe the results of the
select to a file. This could be an ASCII file for DB2 to import.

While waiting, I went to the brute force route of Foxpro -> Excel -> CSV ->
DB2. Real eye opener - the company that wrote the program puts out a new
version on a 2 year +- schedule. Each new version will convert ( the
schema changes slightly with each release ) up to 2 versions back IF the
old version is installed and has the desired data in the tables but will
not import backups from any prior releases. And I thought I could design
some kludges!

Interesting side note - Foxpro incorporates some wierd date/time formats
which blow DB2 out of the water and their delimited formats are primative
at best.

I hadn't even thought about Access - I'll have to see just how that will
work but it's the only suggestion so far that looks like it stands a chance
of working. Thank, all.