Data Mining

SOA & WOA Magazine

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 Process
In 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.

1. Open the above file in any Text Editor.
2. Check the line "CONNECT TO DBNAME". DBNAME will be the name of the database from which this sql file was generated.
3. Change DBNAME to the name of your newly created database for the migration.
4. 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.
5. 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')
6. Repeat step 5 for all tablespace-related DDL statements.
7. Save the changes.
8. Open the DB2 command line prompt and change to the folder where you have the above sql file.
9. Run the following command. db2 -tvf db2look_tbs_buf.sql
10. No errors should be generated.
11. To confirm if buffer space and tablespaces are created successfully open Control center and connect to the database which you have created.
12. Now click on Buffer pools and Table Spaces.
13. 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.

1. Go to DB2 CLP and connect to the database.
2. Type db2 on the command prompt and press enter.
3. Db2 prompt will be shown like this db2=>.
4. 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