Data Movement in DB2

Some time ago, I wanted to backup a complete schema of my DB2 database (version 9.5) to a file such that I am able to restore it when needed. Up to then, I always used the db2move utility for such tasks (using the EXPORT and IMPORT actions, respectively). Unfortunately, the schema I had to deal with contained some XML columns and the db2move tool made some trouble when I tried to reimport the data from the created file into a different database. I don’t know exactly what error messages I got but the problem seemed to be related to the fact that the IMPORT command options CREATE and REPLACE_CREATE are deprecated since DB2 9.5. In other words, db2move has problems in creating tables from the backup files created by db2move using the EXPORT action.

Well, what’s the way IBM now wants data to be backuped and restored? I don’t know exactly, but here is how I solved the problem:

  • First, save the schema’s DDL statements to a file using the db2look utility:
    db2look -d <em>dbname</em> -z <em>schemaname</em> -e -xs -x -o <em>outfile.sql</em>
  • Second, backup all data using the db2move’s EXPORT action:
    db2move <em>dbname</em> EXPORT -sn <em>schemaname</em>

To restore the schema, I did the following:

  • First, restore all tables and permissions:
    db2 -tvf <em>outfile.sql</em>
    Note that you might need to modify <em>outfile.sql</em> before doing this (e.g. in case you want to restore the schema in a different database).
  • Second, restore the data:
    db2move <em>dbname</em> LOAD

That’s it.

For data movement tasks different from the one I described, there might be better-suited tools available. The following two pages give an overview of data movement in DB2:

This entry was posted in DB2. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.