Data Migration from MS SQL to DB2

How to import a MS Sql .bak file to DB2

Abstract:

This document helps to Import MS SQL Server Database Backup (.bak file) to IBM DB2 using IBM Migration Tool Kit 2.0.

Requirements:

1. MS SQL Server Database Backup (.bak) file.
2. Microsoft SQL Desktop Engine (MSDE2000A.exe) helps you to create virtual MS SQL Server 2000 at your Desktop/TP.
3. ODBC SQL Server connection to access the data.
4. IBM Migration Toolkit Software.

Process:

Step1: To Create MS SQL Virtual Server download MSDE2000A.exe (for windows) from http://www.microsoft.com/downloads/details.aspx?familyid=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en and install using default options.

MSDE will install at default location: ‘C:\Program Files\Microsoft SQL Server\80\Tools\Binn\’.

MSDE2000A.exe will install the “osql utility”. Use the osql utility to restore the Microsoft SQL .bak file to an operational ODBC datasource.

Step2: Create Local MS Sql database using .bak file on MSDE2000:

1. Open a DOS Command window and set the PATH variable as:

“set PATH=%PATH%; C:\Program Files\Microsoft SQL Server\80\Tools\Binn\”

2. To create a database at MSDE, on the same command window

i.                    osql –E

ii.                  CREATE DATABASE SAMPLE

iii.                GO

DOS Console will display the SAMPLE database created as successfully with the following lines:

The CREATE DATABASE process is allocating 0.63 MB on disk 'SAMPLE'.

The CREATE DATABASE process is allocating 0.49 MB on disk 'SAMPLE_log'.

3. To verify the SAMPLE database was created successfully, use the following commands on the same DOS console.

i.                    SELECT name FROM sysdatabases

ii.                  GO

DOS Console will display SAMPLE database entry.

4. To restore the contents from the .bak file into the newly created database, the database recovery mode should be simple:

i.                    ALTER DATABASE SAMPLE SET RECOVERY SIMPLE

ii.                  GO

5. By default the database state will be Offline.  We need to set the database state to online before importing the .bak.

i.                    ALTER DATABASE SAMPLE SET ONLINE

ii.                  GO

6. To restore the contents from your .bak file:

i.                    RESTORE FILELISTONLY FROM DISK=’<PATH>/pubs.bak’

ii.                  GO

This will restore the .bak file as local file system in the form of .mdf and .ldf files.

DOS Console wills displays the Output is in the form: Physical Name, FileGroupName, Type, Size, and Max Size.

You will probably find that your FileGroupNames are ‘pubs’ and ‘pubs_log’.  (If you see FileGroupNames are ‘PRIMARY’ and ‘NULL’ instead of ‘pubs’ and ‘pubs_log’ ignore that).

Note: <PATH> should be replaced with .bak files location.

7. Restore the database (using .mdf and .ldf files):

i.                    RESTORE DATABASE SAMPLE FROM DISK='<PATH>\pubs.bak' WITH MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SAMPLE.mdf', MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SAMPLE_log.LDF', REPLACE

ii.                  GO

DOS Console will display the SAMPLE database is processed successfully (Check for the below statements)

Processed 216 pages for database 'SAMPLE', file 'pubs' on file 1.

Processed 1 page for database 'SAMPLE', file 'pubs_log' on file 1.

RESTORE DATABASE successfully processed 217 pages in 0.055 seconds (32.190 MB/sec).

Note: <PATH> should be replaced with .bak files location.

8. The SQL Service Manager will display at your services section on desktop (find at bottom right corner).

Step3: Create ODBC Datasource connection to access data from SAMPLE Database:

1. Open Data Sources (ODBC) from Administrative Tools under Control Panel.

2. Click Add button and select ‘SQL Server’ option at Create New Data Source pop-up window.

3. Enter ODBC name and Server in Create a new Data Source to SQL Server pop-up window.

4.Click next -> next, now select ‘Change the default database to’ Check box and select SAMPLE from list.

5. Click next -> Finish, Click ‘Test Data Source’ button on ODBC Microsoft SQL Server Setup pop-up window.

  

Step4: Migrate MS SQL Data to DB2 using IBM Migration Toolkit.

1. Download IBM Migration Toolkit 2.0 from the following site,

http://www-306.ibm.com/software/data/db2/migration/mtk/ and install on your system with default settings.

2. Open Migration Toolkit wizard from Start -> All Programs -> Toolkit, select ’Quickly convert database using the wizard’

3. Provide information for Project Name, Source database type, Target database type at Project Information pop-up window.

i.                    Enter Project Name as SAMPLEPROJ,

ii.                  Select Source database type as ‘Microsoft SQL Server’ from drop-down list.

iii.                Select Target database as DB2 Version respect to version, which you are using.

iv.                Click on Next button.

4. Select Extract from a Server radio button on Source Database pop-up window, it will display Connect to Database pop-up window.

i.                    Enter JDBC/ODBC Alias as SAMPLEDB (Created in Step3)

ii.                  Provide information for User ID (must have administrative access), Password fields.

iii.                Click on OK button.

5. On successful database connection, you will able to see Extract pop-up window.

i.                    Select the tables, views, procedures and triggers that you want to transfer to DB2 database.

            

ii.                  Click on Extract button.

iii.                Click on Next button.

        

6. To change target data types for your convenience at Global Type Mapping pop-up window using Edit Field option (In this we suggest you to change the following data types).

i.              Change GRAPHIC to CHAR.

ii.            Change VARGRAPHIC to VARCHAR.

iii.          Click on Apply button

Before                                                 After

        

iv.                Click on Next button on Global Type Mapping pop-up window.

Modified Global Type Mapping window:

7. To Extract the data and generate scripts for DB2 movement:

i.                          Select Extract the source data and generate the data movement scripts radio button at Data Movement pop-up window

ii.                        Click on Next button.

8. To specify the DB2 target database:

i.                          Select deploy radio button on Target Database pop-up window, it will pop-up Target Database window. 

ii.                        Provide database information, database name as ‘SAMPLE’ (the entered database must be created in DB2 before moving the data using this Migration Toolkit)

iii.                      Provide information for User ID (which has database access privilege), and Password; (If you want to specify particular user name, Otherwise select ‘Use your system current User ID and Password’ check box),

iv.                      Select ‘Load the Data into database’ check box (this option will load the data of selected tables in DB2 database).

v.                        Click on Ok button

vi.                      Click on Next button.

9. Verify summary details on Migration summary window Click on Finish button (It will take some time to finish the migrate data to DB2.

10. If you see any errors on Translation section ignore it.

11. Verify SAMPLE database with tables, views, procedures and triggers at DB2 Control Center.

Note: IBM Migration Toolkit will store the SAMPLEPROJ at ‘C:\MTK\projects\’ folder and you can find the DB2 script that will contain the entire script how the data is created to DB2 database respect to MS Sql database.

Reference- some useful links:

1. http://www.ibm.com/developerworks/db2/library/techarticle/dm-0411yip/
2. http://www.ibm.com/developerworks/edu/dm-dw-dm-0209jarzebowicz-i.html?S_TACT=104AHW11&S_CMP=LIB