Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11

    Unanswered: Migrate data after change of the database structure

    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!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2009
    Location
    Sweden
    Posts
    11
    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

    Code:
    INSERT INTO TEMP_DATABASENAME.SCHEMANAME.TABLENAME (col1, col2, col3)
      SELECT col1, col2, col3
      FROM DATABASENAME.SCHEMANAME.TABLENAME
    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.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    3 part is possible if activating federation and defining nicknames for remote tables. then use the nickname for source or target table
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •