lunedì 20 aprile 2015

DB2 move tables between workstations

There is a good tool to export data of the tables in a portable format,that allows to export from a platform and import to another platform destination.


This is the usage below, but let's talk about the procedure for different scenarios.

                            .----------------------------.   
                            V                            |   
>>-db2move--dbname--action----+------------------------+-+-----><
                              +- -tc--table-definers---+     
                              +- -tn--table-names------+     
                              +- -sn--schema-names-----+     
                              +- -ts--tablespace-names-+     
                              +- -tf--filename---------+     
                              +- -io--import-option----+     
                              +- -lo--load-option------+     
                              +- -co--copy-option------+     
                              +- -l--lobpaths----------+     
                              +- -u--userid------------+     
                              +- -p--password----------+     
                              '- -aw-------------------'     



Scenario 1. I have to move only some tables not everything. I already have the database created at the destination workstation.

  • Create a dir /dest where to write the data to be exported. Assign the dir the owner of the DB2 instance in order to be able to write.
  • Write the list of tables with the schema name as prefix in a tablist.txt like in this example:
"MAXIMO"."TICKET"
"MAXIMO"."TKSTATUS"
"MAXIMO"."WORKLOG"
"MAXIMO"."WORKORDER"




  • Run the command to export:
      db2move DBNAME export -u USERNAME -p PASSWORD -l /dest -tf tablist.txt


  • Compress the folder and move to the destination workstation.
  • On the destination workstation, run the command to import:
      db2move DBNAME import-u USERNAME -p PASSWORD -l /dest

Scenario 2. I have to move all tables to a new machine where I have the database at all.


  • Run the command to create the DDL statements for all the objects of the database:
db2look -d DBNAME -e -l -f -a -o /dest/db2look_DBNAME.sql


  • Edit the output sql file above and add the statement "CREATE DATABASE DBNAME" since at the destination directory it does not exists. Modify also the tablespaces file path accordingly to the drive you have prepared at destination workstation.
  • Run the command to export all tables:
      db2move DBNAME export -u USERNAME -p PASSWORD -l /dest

  • Compress the folder and move to the destination workstation.
  • On the destination workstation, run the command to create database and all objects type (tables, view, indexes, etc) running the command:
      db2 -tvf db2look_DBNAME.sql
  • On the destination workstation, run the command to import:
      db2move DBNAME import-u USERNAME -p PASSWORD -l /dest


Check DB2 documentation:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html?cp=SSEPGG_9.7.0%2F3-6-2-6-82