I’ve encountered several times the need to make copies of a DB2 database from one system to another. Maybe because the database has some data I need in my local environment, or I just need to change its name (I haven’t seen an easy way to do this). I’ll provide the steps I’ve done to do this, which works to do backups and restore them on a Windows or Linux system.
The example I’ll provide will be from Linux to Windows:
1) Connect to the Linux system with a user with DB2 privileges (that can connect to a database, run db2, db2move, db2look commands, etc).
2) Create a new folder and cd into it (we’ll create lots of files in it).
3) Run command: db2move DATABASE_NAME export
This last command exports the database DATA.
4) Run command: db2look –d DATABASE_NAME –e –a –o db2look.sql
Note: This has the options I use/need. For a complete set of options please see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html
This last command exports the database STRUCTURE.
Now we have all we need to make a copy of the database in the Windows system (or any other system).
5) Copy the folder we created in the Linux system to the Windows system.
6) Adjust (if needed) the db2look.sql script. Maybe you want to change the database name, schema authorization, etc.
7) Now, open your command prompt (with a user that can run db2, db2move, db2look commands).
8) Create the database with command: db2 create database DATABASE_NAME
9) Connect to the database with command: db2 connect to DATABASE_NAME
9.5) If you used the same options as I did for the db2look command (step 4) you may need to create the database tablespaces manually before the next step.
10) Run the db2look.sql script with command: db2 –tvsf db2look.sql
11) Load the data into the database with command: db2move DATABASE_NAME load
After doing this, the tables will be in CHECK PENDING state. To see all tables in that state connect to the database and run this query: SELECT tabname FROM syscat.tables WHERE status = 'C';
12) To switch the table back to a normal state, you can run the following: SET INTEGRITY for TABLE_NAME IMMEDIATE CHECKED;
Note: If you have lots of tables, or have circular relations, just use the same command but with all table names separated by commas, like: SET INTEGRITY for TABLE_NAME, TABLE2_NAME, TABLE3_NAME IMMEDIATE CHECKED;