Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

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;

  1. Create a temporary table
  2. Copy the data from your table to the temporary table
  3. Drop and re-create the table (including any foreign keys that point to it)
  4. Copy the data back from the temporary table
  5. 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