IBM Data Movement Tool (IDMT)

Information Management Tapas Gupta I Information Management © 2011 IBM Corporation

Information Management

Introduction •

IBM Data Movement Tool (IDMT) is a powerful tool that allows the movement of data from various sources easily to DB2 for Linux, UNIX, and Windows and DB2 for z/OS.

•

The tool also enables applications from Oracle and Sybase to run on IBM® DB2®, Version 9.7 for Linux, UNIX, and Windows as is, or with very little or no changes.

•

IDMT replaces the MTK functionality.

•

Supports moving data from a source database to DB2 in a pureScale environment.

•

This tool has a GUI interface and also supports the command line instructions for data movement.

2

© 2011 IBM Corporation

Information Management

Contents •

Prerequisite

•

Launching IDMT

•

Data Movement

•

Extraction -DDL and Objects

•

Deployment – DDL and Objects

•

Extraction and Deployment - Data

•

Interactive Deploy

•

Generated Scripts

•

Configuration Parameters

•

Pipes for Data Movement

•

Synchronize – Load & Unload

•

Other Available Options

•

Downloading and Installing IDMT

•

Information and Queries

3

© 2011 IBM Corporation

Information Management

Prerequisites •

•

4

Tool runs in Java version 1.5 or higher

DB2 V9.7 should be installed on your target server if you are enabling an Oracle application to be run on DB2 for Linux, UNIX, and Windows.

JDBC Drivers required for database connectivity Database

JDBC Driver

SQL Server

sqljdbc5.jar or sqljdbc.jar

Oracle

ojdbc5.jar or ojdbc6.jar or ojdbc14.jar, xdb.jar, xmlparserv2.jar or classes12.jar or classes111.jar for Oracle 7 or 8i

Sybase

conn3.jar and antsjconn2.jar for DB2 SKIN feature

Teradata

terajdbc4.jar and tdgssconfig.jar

MS Access

Access_JDBC30.jar

PostgreSQL

postgresql-8.1-405.jdbc3.jar or latest driver

MySQL

mysql-connector-java-5.0.8-bin.jar or latest driver

DB2 for i

jt400.jar

DB2 for z

db2jcc.jar, db2jcc_license_cisuz.jar or db2jcc4.jar, db2jcc4_license_cisuz.jar

DB2 for LUW

db2jcc.jar, db2jcc_license_cu.jar or db2jcc4.jar, db2jcc4_license_cu.jar

© 2011 IBM Corporation

Information Management

Launching IDMT IDMT can run on both Windows and Linux environments. IDMT package consists of IDMT jar and scripts to run the IDMT.

Windows 

Launch a DB2 command window.



Change to the IBMDataMovementTool directory.



Run the command IBMDataMovementTool.cmd.

Linux 

Login to your server as DB2 instance owner.



Change to the IBMDataMovementTool directory .



Run the command ./IBMDataMovementTool.sh.

5

© 2011 IBM Corporation

Information Management

Extract / Deploy Screen

6

© 2011 IBM Corporation

Information Management

Check for new version of IDMT

7

© 2011 IBM Corporation

Information Management

8

© 2011 IBM Corporation

Information Management

Checks for new features in IDMT

9

© 2011 IBM Corporation

Information Management

10

© 2011 IBM Corporation

Information Management

Data Movement

Data movement involves two process - extraction and deployment

Extraction 

Involves the extraction of DDLs, Objects and Data from source database.



IDMT generates SQL scripts during extraction which are being used during deployment.



IDMT also generates few scripts which can be modified so that user can customize the deployment.

Deployment 

Deployment process involves loading of all extracted DDLs, Objects and Data to the target DB2 database.



IDMT uses generated scripts for the deployment.



IDMT also runs sanity and integrity checks scripts.

11

© 2011 IBM Corporation

Information Management

Extraction -DDL and Objects Steps involved in extraction of DDL and Objects 

Select the source database vendor.



Connect to source database.



Connect to target DB2. If DB2 is not installed select 'DB2 is not installed' option.



Select source schema for which migration needs to be done.



Select the DDL and Object option.



Directory name to where objects will be extracted



Start extraction using 'Extract DDL / Data' button.



GUI switches to the View File tab when extraction process starts and it shows the progress of extraction process



When the extraction process is complete, there is a Work Completed message at the end of the output.



12

© 2011 IBM Corporation

Information Management

Extraction Process

13

© 2011 IBM Corporation

Information Management

14

© 2011 IBM Corporation

Information Management

15

© 2011 IBM Corporation

Information Management

16

© 2011 IBM Corporation

Information Management

17

© 2011 IBM Corporation

Information Management

18

© 2011 IBM Corporation

Information Management

19

© 2011 IBM Corporation

Information Management

20

© 2011 IBM Corporation

Information Management

21

© 2011 IBM Corporation

Information Management

Deployment - DDL and Objects

Deployment of DDL and Objects involve following steps 

Deploy' button will deploy the ddls and objects.



Deployment can also be done through interactive deploy option.



GUI switches to the View File tab when the deployment process starts and it shows the progress of deployment process.



When the deployment process is complete, a Work Completed message is shown at the end of the output.

22

© 2011 IBM Corporation

Information Management

Deployment Process

23

© 2011 IBM Corporation

Information Management

24

© 2011 IBM Corporation

Information Management

25

© 2011 IBM Corporation

Information Management

26

© 2011 IBM Corporation

Information Management

27

© 2011 IBM Corporation

Information Management

Extraction and Deployment - Data

Extraction and Deployment process for Data are similar to that for DDL and Objects.

Extraction 

Select the data option and uncheck the ddl and object option.



Follow the same process which was followed for DDL and Objects.

Deployment 

Interactive Deploy does not deploy the data.



Select the data option and uncheck the ddl and object option.



Follow the same process which was followed for DDL and Objects.

28

© 2011 IBM Corporation

Information Management

Data Extraction

29

© 2011 IBM Corporation

Information Management

30

© 2011 IBM Corporation

Information Management

Data Deployment

31

© 2011 IBM Corporation

Information Management

32

© 2011 IBM Corporation

Information Management

33

© 2011 IBM Corporation

Information Management

34

© 2011 IBM Corporation

Information Management

35

© 2011 IBM Corporation

Information Management

Interactive deploy

Deploy button only deploys DDL where as interactive deploy, deploys the PL/SQL procedures, triggers and functions.

Steps to be followed for interactive deployment. 

Connect to target db2.



Traverse to interactive deploy.



Click on the Open icon on the tool bar. IDMT will ask to specify output directory where objects are extracted.



List of objects will be populated on left pane.



Select the object and press deploy button.

36

© 2011 IBM Corporation

Information Management

Interactive Deployment for DDL and Objects

37

© 2011 IBM Corporation

Information Management

Populating Extracted DDLs and Objects

38

© 2011 IBM Corporation

Information Management

39

© 2011 IBM Corporation

Information Management

40

© 2011 IBM Corporation

Information Management

41

© 2011 IBM Corporation

Information Management

Excluding objects from Interactive deploy

42

© 2011 IBM Corporation

Information Management

43

© 2011 IBM Corporation

Information Management

Deploying selected Objects

44

© 2011 IBM Corporation

Information Management

45

© 2011 IBM Corporation

Information Management

Interactive deploy .....

For the Failed Deployment 

Objects deployed successfully are marked with a check mark sign and failed objects are marked with a red stop icon.



Sometimes deployment of object fails because of unresolved dependencies.



Objects failed can be redeployed by viewing the source of object and removing errors if any.



Detailed error message for the failed objects can be viewed using 'Get Detailed Error Message' option.

•

46

© 2011 IBM Corporation

Information Management

Failed Deployment

47

© 2011 IBM Corporation

Information Management

48

© 2011 IBM Corporation

Information Management

49

© 2011 IBM Corporation

Information Management

Error details for failed object

50

© 2011 IBM Corporation

Information Management

51

© 2011 IBM Corporation

Information Management

Generated Scripts

The following list contains important generated scripts and files.



unload.cmd is used to unload the data from source database.



db2load.cmd is used to load data to target DB2 database.



rowcount.cmd is used to count the rows on source database and target database and to compare.



DataCheck.cmd is used to check data from source and target DB server to compare.



Logs and error are logged in IBMDataMovementTool.log and IBMDataMovementToolError.log respectively.



There are few custom mapping files which can be modified in order to customize the DDLs and Objects while extraction.

52

© 2011 IBM Corporation

Information Management

Configuration Parameters

IDMT utilizes various configuration parameters which makes the tool more flexible.

User can set the important parameters through following ways Set Params 

'Set Params' can be used to save these parameters. These parameters will get updated in IBMExtract.properties file.



User can load the updated configuration parameters into the tool using the load option.



IBMExtract.properties File 

This file has a complete list of configuration parameters.



This file gets regenerated everytime a user runs the tool.



User can directly update the file and load it into the tool using 'Load' option in Set Params tab.

53

© 2011 IBM Corporation

Information Management

Set Params

54

© 2011 IBM Corporation

Information Management

Option to select the various configuration options

55

© 2011 IBM Corporation

Information Management

Profile Selection and updating the configuration parameters

56

© 2011 IBM Corporation

Information Management

57

© 2011 IBM Corporation

Information Management

58

© 2011 IBM Corporation

Information Management

Loading IBMExtract.properties file

59

© 2011 IBM Corporation

Information Management

60

© 2011 IBM Corporation

Information Management

61

© 2011 IBM Corporation

Information Management

Configuration Parameters .... Few important parameters and their description

Parameter

Description

encoding

Used to specify the multibyte character encoding format.

graphic

If set false, NVARCHAR, NCHAR and NTEXT columns will be treated as VARCHAR, CHAR and CLOB.

RetainColName

If set true, tool will not truncate column name to 30 chars

db2_compatibility

If true, use Oracle compatibility features in DB2.

caseSensitiveTabColN If set to true, table and column name's case sensitivity will ame remain preserved. If set false, names will be converted to uppercase.

62

dbclob

If set false, CLOBS are retained as it is. If set true, CLOB is converted to DBCLOB in DB2.

customMapping

Only use if you are doing FileNet migration. The default is false and use either PE or CE for FileNet

© 2011 IBM Corporation

Information Management

Pipes for Data Movement

Large sized database can be moved using pipes.



Pipe is recommended when there is not enough space to hold large intermediate data files.



In Windows IDMT uses Pipe.dll to create the pipes. Pipe.dll should be present in the same directory where IBMDataMovementTool.jar file is placed.



On UNIX IDMT uses mkfifo command for data movement.



It is necessary to have table definitions created before using pipe.

63

© 2011 IBM Corporation

Information Management

Pipes for Data Movement ....

Following steps to be followed for using pipes



Ensure that ddl and objects are already extracted.



Check Data option and uncheck DDL and Object option.



Specify the # Extract rows = 1



Select 'Unload / Load through Pipe'.

64

© 2011 IBM Corporation

Information Management

Data Movement using pipe

65

© 2011 IBM Corporation

Information Management

Synchronize – Load & Unload

Sync is an another option for data movement



Sync and Pipes are very similar.



Data is first stored in intermediate file and then gets loaded into target DB.



No time is lost in starting Load explicitly.



Load files can be reused.

66

© 2011 IBM Corporation

Information Management

Data Movement using sync

67

© 2011 IBM Corporation

Information Management

Other Available Options

IDMT provides an option to perform extract and deployment on selected tables. IDMT also support Auto Fix feature.

Auto Fix 

Using this option user can the Oracle PL/SQL code compatible to DB2.



Can convert Oracle DDL and other SQL scripts to DB2.

Select Table 

68

Using this option user can extract and deploy selected tables.

© 2011 IBM Corporation

Information Management

Auto Fix for Oracle PL/SQL

69

© 2011 IBM Corporation

Information Management

Data Movement for selected tables

70

© 2011 IBM Corporation

Information Management

71

© 2011 IBM Corporation

Information Management

Downloading and Installing IDMT

It is strongly recommended to install the tool on a target server to achieve best data performance

•

IDMT can be downloaded from the following link https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=idmt

•

After downloading IBMDataMovementTool.zip file, extract the files into a directory called IBMDataMovementTool on your target DB2 server.

•

IDMT package consists of following files

•

IBMDataMovementTool.cmd - Command script to run the tool on Windows.

•

IBMDataMovementTool.sh - Command script to run the tool on UNIX.

•

IBMDataMovementTool.jar - JAR file of the tool.

•

Pipe.dll - A DLL required on Windows x86 if pipe option is used.

•

Pipe64.dll - A DLL required on Windows x64 if pipe option is used.

• 72

© 2011 IBM Corporation

Information Management

Information and Queries



IDMT has a support forum where queries, bugs and suggestions can be reported.



For additional information on IDMT http://www.ibm.com/developerworks/data/library/techarticle/dm-0906datamovement/



Or email

•

Sabyasachi Routray - [email protected]

•

Tapas Gupta - [email protected]

•

Vikram Khatri - [email protected]

● ●

73

© 2011 IBM Corporation