By Ravi Shankar | Article Rating: |
|
November 16, 2009 06:00 PM EST | Reads: |
9,548 |

This article provides a step-by-step approach to cloning the DB2 Universal Database (DB2 UDB) across platforms and describes the usage of DB2 utilities that help to accomplish the task.
There is no direct support in DB2 UDB for cross-platform backup and restore operations. It means one cannot simply take a backup of your DB2 UDB running on the Windows operating system and restore it to DB2 UDB running on the Linux operating system. This simple restore operation will not work across platforms. The two DB2 utilities db2move and db2look can be used for cross-platform backup and restore operations.
The db2move command helps accomplish the movement of database tables and the db2look command helps transfer other database objects, such as triggers, indexes, sequences, constraints, buffer pools, and tablespaces. b2look command helps to capture the data definition language (DDL) for these objects on the source database and re-create these objects on the target database (see Figure 1).
Migration Life Cycle
The various steps involved in the migration life cycle are shown in Figure 2.
Data Migration ProcessIn this section we go through each of the life cycle steps that are the part of the data migration process. We assume that the data is being migrated from Linux DB2 UDB to Windows DB2 UDB.
1. Creating dumps on a Source machine
The first step in the data migration process is to generate the DB2 dumps from the Linux box. You can use db2look and db2move commands to create the dumps by yourself or you can use the help of the DBA to accomplish the same. Once the dumps are ready start the download process.
2. Downloading Dumps on a Target machine
Before downloading the dumps ensure that you compress them to a .tar file. This will reduce the download time.
For example, the following UNIX command will compress the dump files into a single jar file.
Example: tar -cf db2dump.tar /<folder path >
If you are using FTP for transferring files from the Linux machine to the Windows machine, please note the following.
After Compression, the .tar file is to be downloaded in binary mode, whereas the files
db2move.lst and db2look.sql are to be downloaded in ASCII mode.
- Transfer the .tar file in Binary Mode.
- Transfer db2look.sql & db2move.lst files in ASCII Mode.
After downloading the file to the Windows machine, you can use tools like TUGZIP to uncompress the tar file.
3. Checking Code page settings
Open DB2 CLP [Command line prompt] and run the following command.
- db2 get db cfg for DBNAME
- Ensure that Database code page is the same across both the platforms.
- On the Linux box code the page is set to 1208.
- On Windows by default the code page is set to 1252, so when the db2move command is run, an error is generated related to the codepage difference and the data will not get migrated. The code page error message will be SQL3525N The "CODEPAGE" option is incompatible with the "LOBSINFILE" option
We need to change the code page settings on Windows if it is not the same as Linux box. This is explained in the next section.
4. Changing code page settings
To change the code page settings on Windows please use the following steps:
- Go to DB2 Command line prompt.
- Change the application code page in one of two ways
This change affects all sessions
db2set db2codepage=1208
db2start
db2stop
This change affects only the current session and should be done before connecting to the database
export DB2CODEPAGE=1208
The above code page settings will be reflected for newly created databases and not existing ones.
5. Drop Old DB instance
- Take a backup of the old Database instance before you drop it. In case you don't need the backup of the old instance, you can drop it without taking a backup.
- The database instance can be dropped either from Control center or from DB2 Command line prompt using the following command
To drop the database instance you need to have admin privileges.
6. Creating a new database
- To create a new database run the following command from DB2 CLP.
db2 create db DBNAME
DBNAME is the name of the database you want to create.
E.g. db2 create db MYESTDB
- Once the database is created successfully, ensure that the CODE PAGE settings are correct by running the following command from DB2 CLP.
db2 get db cfg for DBNAME
It should show the following settings
7. Connecting to the created database
- Connect to the created database using the following command from DB2 CLP
DB2 CONNECT TO DBNAME USER <user-name> USING "<password>"
E.g., DB2 CONNECT TO MYTESTDB USER admin USING "admin"
8. Creating a buffer pool and Table space
If the buffer pool and table space are not sufficient then db2move command will fail with the following error
SQL0286N A default table space could not be found with a page size of at
least "8192" that authorization ID "admin" is authorized to use.
And the data for that particular table will not get migrated.
To overcome this we need to create buffer pool and table space before we run the db2move command.
There are two options to do this.
- First option is to run the db2look _tbs_buf.sql file which is generated on Linux box.
- Second option is to manually create a buffer pool and table space. This should be opted when we are not having the above sql file.
Creating the buffer pools and Table spaces from db2look_tbs_buf.sql
To create buffer pools and tablespaces from a sql file please perform the following steps.
- Open the above file in any Text Editor.
- Check the line "CONNECT TO DBNAME". DBNAME will be the name of the database from which this sql file was generated.
- Change DBNAME to the name of your newly created database for the migration.
- Also ensure that DDL statements for Tablespaces are in one line. If they are not, then make the DDL statements in one line by removing the new line character.
- You need to modify the USING clause of Tablespace DDL statements, e.g., if the USING clause is having entries like USING ('/home/db2db/db2db/NODE0000/SQL00002/TAB8K') change it to USING ('c:\TAB8K')
- Repeat step 5 for all tablespace-related DDL statements.
- Save the changes.
- Open the DB2 command line prompt and change to the folder where you have the above sql file.
- Run the following command. db2 -tvf db2look_tbs_buf.sql
- No errors should be generated.
- To confirm if buffer space and tablespaces are created successfully open Control center and connect to the database which you have created.
- Now click on Buffer pools and Table Spaces.
- You should be able to see the newly created buffer pools and tablespaces. You can identify them with the name (see Figure 7 and Figure 8).
Creating Buffer Pools and Table Spaces from db2 Commands
This option should be used when we are not having the sql file that can generate the buffer pools and tablespaces.
Run the following commands from DB2 CLP.
- Go to DB2 CLP and connect to the database.
- Type db2 on the command prompt and press enter.
- Db2 prompt will be shown like this db2=>.
- Type the following commands.
CREATE BUFFERPOOL BP_16 IMMEDIATE SIZE 250 PAGESIZE 16 K
CREATE REGULAR TABLESPACE TBSP_16 PAGESIZE 16 K MANAGED BY DATABASE USING (FILE 'C: \cont1' 3200) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BP_16
5. Please ensure that the above commands are run one after the other
6. Also ensure that each command is run in one-line mode.
9. Running DB2MOVE command
To run the db2 move command you have to do the following steps.
1. Open DB2 CLP.
2. Connect to the newly created database.
3. Change the directory to the folder where you have copied the dumps.
cd <path to dump folder>
4. Folder dump should contain all the dump files along with db2move.lst file
5. Open this file in any Text Editor
6. Change the schema name for all the tables if you want.
Entries in first file will be like this!"DB2UAT "."DESC"!tab1.ixf!tab1.msg!
If you want SCHEMA name to be ADMIN change DB2UAT to ADMIN.This can be easily done with the replace feature of text editors. After changing your entries would be like this
!"ADMIN "."DESC"!tab1.ixf!tab1.msg!
7. Run the following command
DB2MOVE DBNAME import -io REPLACE_CREATE -u <user-name> -p <password>
E.g. DB2MOVE ADMIN import -io REPLACE_CREATE -u admin -p admin
8. After completion of DB2MOVE command open IMPORT.out file from the same folder and check for any Errors or Warnings logged. For successful completion no errors should be reported.
10. Verification
To verify the number of tables that were migrated run the following command.
- SELECT COUNT(*) FROM SYSCAT.TABLES WHERE TABSCHEMA='xxxxx' and TYPE='T'
- SELECT COUNT(*) FROM SYSCAT.TABLES WHERE TABSCHEMA='SYSTOOLS' and TYPE='T'
The count of all three should be equal to the table count in your dump folder.
11. Running DB2LOOK command
Once db2move command is completed successfully, we will run db2look command.
To run the db2 look command you have to do the following steps.
1. Open DB2 CLP.
2. Connect to the newly created database.
3. Change the directory to the folder where you have copied the dumps.
cd <path to dump folder>
4. Folder criuat should contain all the dump files along with db2look.sql file.
5. Open db2look.sql file in any Text Editor.
6. Check the line "CONNECT TO DBNAME".DBNAME will be the name of the database from which this sql file was generated.
7. Change DBNAME to the name of your newly created database for the migration.Run the following command
8. Change the SCHEMA name as you have done in db2move command.
9. Run the below command.
DB2 -tvf db2look.sql
12. Running the INTEGRITY1.BAT file
After running the db2look commands it's possible that some of the tables can be in check pending state and we have to bring them to normal state.
To do that run integrity1.bat file from DB2 CLP.
cd D:\integrity
D:\integrity> integrity1.bat
13. Verification
To verify the success of db2look command you can do the following:
- Open the control center and connect to newly created database.
- Select a table for which you know constraints should have been created.
- Right click on that table and click Alter.
- Open the keys and constraints tab and check if the foreign keys and other constraints are created for that table.
- Try inserting into child table having foreign key relationship and it should throw foreign key constraint error.
- Like this you can carry out other test cases.
Conclusion
In this article we have seen how to leverage the two DB2 utilities db2move and db2look to clone DB2 UDB across cross platforms. I have tried to cover all the possible steps which can be the part of the migration life cycle. We have also seen how to cross verify the success of the migration process.
Resources
- Refer to Migration from Aix to Windows on db2dba blog spot.
- http://www-01.ibm.com/support/docview.wss?uid=swg21220689
Published November 16, 2009 Reads 9,548
Copyright © 2009 Ulitzer, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.
More Stories By Ravi Shankar
Ravi Shankar is working as a Technical Architect with Infosys Technologies Ltd, India. He has been working on e-commerce projects leveraging WebSphere commerce server. He leads J2EE COE activities in the manufacturing business unit within Infosys. Ravi has been involved with DB2 UDB data migration from IBM AIX server to Windows server.
- Using Java Data Mining to Develop Advanced Analytics Applications
- JCP Approves Oracle-Led Data Mining Specification, JSR-73
- Bit.ly, Twitter, Security & You
- Lori MacVittie Interview at Cloud Connect
- SAP and Big Data
- It's All Fun and Games (and Data Mining) at JAMDAT Mobile
- Big Data in Telecom: The Need for Analytics
- Algorithms of the Intelligent Web
- The Threat Behind the Firewall
- Data Mining