Unanswered: Replace Legacy Tables... while maintaining data
I am currently working on a project with the final aim of converting a legacy system to a true N-Layer architecture. The initial part of the project involves converting the underlying database to a true relational design.
The underlying database is currently running on the IBM iSeries. The tables are defined using DDS and contain mountains of redundant data, no integrity checking and poorly designed keys etc. Basically refactoring them to a fully normalized design is a non-starter.
New tables are going to be designed from scratch. They will also be on the same iSeries but will be defined with DDL. This will also involve re-writing any insert or update code throughout the application to utilise the new tables. There is however a large amount of legacy apps responsible for reports, displays etc that will not be re-written at this point and will still be accessing the original tables. So we need to keep the data in old, legacy tables in sync with new table data. I was wondering if anyone had ever done something similar or had any suggestions? I am currently thinking either:
1) The stored procedures that inserts, updates, deletes from new table A will also do the same to matching legacy table B. At some point down the line the stored procedure will need to be modified to stop syncing table B
2) Place triggers on table A that also modifies table B. Then the triggers will be removed down the line... the stored procedure don't need to be changed but will this still work fine with transaction management?
3) Remove legacy table B and recreate it as view on table A. Not sure if this will work as table B works on keyed access and I believe views don't support this?
Know nothing about the db2/as400 specific behaviour. But in general I would go for option 3. Design a new database-tables and lt user-views mimic the old situation. On LUW you can use "instead of" triggers to capture the modifications on those views.
I agree. Native iSeries IO can perform keyed access to tables though, and from reading the documentation it seems that DB2/400 only allows sequential access on a view. I think this would break a number of old parts of the system. Wonder if anyone has any experience of this with the iSeries?