First, I am an application developer, not an experienced DBA and this have maybe been asked for before ...

In general, how do solve a scenario like this the easiest way?

I have a DB2 database with a lot of data and I have changed the the SQL scripts I use to create the database structure. Some new tables have been added, some columns have been added to existing tables, a few tables have been dropped and a few columns have been dropped from existing tables.

Now, before I drop the database and creates it again with the changed SQL scripts, how do I save the data? And how do I put all the data back again?

One way to do this that I can imagine is to maintain two set of SQL scripts. One of these that use the old database structure is for copy the data from the current database to a temporary database (something like a lot of SELECT FROM ... INTO ...). And the other that use the new database structure is for copy the data from the temporary database to the recreated database.

Is this the way to go or are there better ways for a developer to do this during continuous development where I have to regenerate the database repeatedly?

Opinions and suggestions on this would be very interesting, thanks!

the best would be to prepare the export jobs to keep the target data
export with literals, joins... whatever needed
after just execute the preformatted load
if the 2 db will exist next to each other and need to be in synch, maybe replication could be a solution for you

Thanks for your suggestions, and they are probably what I have to select from as I just have realized that it is complicated, if possible at all, to just read from one database into another, which I should know if I have thought about it for some minutes. I would prefer some solution that could be executed from Ant as I already use Ant for create and drop of the databases and their tables. But replication is maybe the easiest way to go.

Because of a lot of work during workdays will I only have time to look at this now and then ut more comments and advices on this would be appreciated.

If you have to drop and recreate a table to make changes to a table, then the approach most use is the following:

• Rename the old table to something else.
• Create the new table.
• Do an insert/select from to move the data from old table to new table. Obviously this may need to be customized for some of the new or changed columns.
• Drop the old table.

If there are foreign keys involved, you will have to drop them and recreate them.

There is actually an administrative stored procedure to do a lot of this for you. The DB2 Control Center will invoke the SP when certain kinds of alters are done on a table (you can see the script which uses the SP if you select the SHOW SQL button right before you submit it).

In newer versions of DB2 (9.5 and 9.7), additional support for online changes to tables has been added, but you may have to do a table reorg after the alter table.

Thanks for these advices!

I read a very interesting article at Martin Fowler´s site a while ago. It is an article about Evolutionary Database Design and they talk about data migration scripts in that article. From what I know about this for DB2 so far must these data migration scripts then use the type of alterings you talk about when they are to be used for DB2. With a backup made before execution of any altering. For small occasional changes to the schema is this maybe the fastest and easiest way to go and maybe could some exercises show me how well, or painful, this would work.

What I really would like is if I could execute something like this from Ant scripts

That works in Microsoft SQL Server management Studio for Microsoft SQL Server Express 2008. This does of course not even work for Microsoft SQL Server when executed from an Ant script but except from that is it an very easy way to migrate data.

Phew! I have not realized before that data migration can be this tricky. But then is it as more important to listen at good advices and tips.

3 part is possible if activating federation and defining nicknames for remote tables. then use the nickname for source or target table

Unless I am missing something why drop the DB at all?

Correct me if I am wrong, but here is what you are trying to archive:

1. You have some old structures and data in it that must be gone.
2. you have some new structures that must have data from the old structures.

if that is correct.

1. load new structure from your old structures. Use load from cursor to do the job.
2. drop old structures.