Subject | Automating Data Migration FB1.0 to FB1.5 |
---|---|
Author | brian.gooch.uk@btinternet.com |
Post date | 2010-10-14T14:39:23Z |
Permanent Data Migration from version2:FB1.0 dialect1 to version3:FB1.5 dialect3
As we have a large number of similarly structured databases to migrate, we are looking to automate as much of the processing as possible. There are a great number of differences between the data structures of the 2 versions. Version 3v0 has been created using SQL scripts and includes all domains, permanent tables, populated lookup tables, and skeleton SPs.
Using SQL only we have
a) Run an SQL Metadata Script to alter all version2.x tables to match the columns and datatypes found in version3 but not in version2
b) Run an SQL Conversion script of SPs to populate the added columns in version2
c) Run an Extract script for each permanent table at a time to produce an INSERT script
d) Run an SQL INSERT script chaining a sequence of inputs into version3
e) Run modified version2 scripts for FKs, SPs, generators and triggers.
As Step c) is very time consuming, does anybody have experience of automating the dumping out / transferring of permanent data?
We have Database Workbench version 4 which has 3 possible tools:
i) Database Migrator : this deals with whole schemas and is therefore unsuitable
ii) Export Multiple tables: all columns of all tables and is therefore unsuitable
iii) Data Pump which has stored files(s) of column matchings from version2 to version3 but it can fail with errors without specifying the error(s).
Any ideas/comments will be much appreciated. We would like to just invoke steps a) to e) to effect the migration.
Many thanks.
Brian Gooch.
As we have a large number of similarly structured databases to migrate, we are looking to automate as much of the processing as possible. There are a great number of differences between the data structures of the 2 versions. Version 3v0 has been created using SQL scripts and includes all domains, permanent tables, populated lookup tables, and skeleton SPs.
Using SQL only we have
a) Run an SQL Metadata Script to alter all version2.x tables to match the columns and datatypes found in version3 but not in version2
b) Run an SQL Conversion script of SPs to populate the added columns in version2
c) Run an Extract script for each permanent table at a time to produce an INSERT script
d) Run an SQL INSERT script chaining a sequence of inputs into version3
e) Run modified version2 scripts for FKs, SPs, generators and triggers.
As Step c) is very time consuming, does anybody have experience of automating the dumping out / transferring of permanent data?
We have Database Workbench version 4 which has 3 possible tools:
i) Database Migrator : this deals with whole schemas and is therefore unsuitable
ii) Export Multiple tables: all columns of all tables and is therefore unsuitable
iii) Data Pump which has stored files(s) of column matchings from version2 to version3 but it can fail with errors without specifying the error(s).
Any ideas/comments will be much appreciated. We would like to just invoke steps a) to e) to effect the migration.
Many thanks.
Brian Gooch.