June 11, 2003
Changing Tables in DB2
Further investigation reveals that you can't change the name or data type of columns within DB2 either. With the exception of increasing the length of VARCHAR columns that is.
Which means that when you want to change a column definition you have to;
- Create a temporary table
- Copy the data from your table to the temporary table
- Drop and re-create the table (including any foreign keys that point to it)
- Copy the data back from the temporary table
- Drop the temporary table
So far this week my project team have generated thirty four changes to the data model - well, we are very early in the development cycle - and I haven't looked in my in box yet this morning.
I was planning on producing weekly deltas allowing the team to upgrade their local copies of the database. At this rate I don't think its possible to do that, as it will take me longer than a week to produce a script to perform all of the changes.
As the model matures it should be possible to do it but until then I will just have to produce whole sets of DDL scripts and get people to drop and re-create their databases on a regular basis.
Posted by Andy Todd at June 11, 2003 09:51 AM