IBM Data Movement Tool

The tool described in this article has been replaced with a new IBM Data Movement Tool. Please click this link to get the new tool .

Back to top

Architectural overview and comparison

You'll find many differences between DB2, MySQL, and PostgreSQL. We'll start by looking at some of the fundamental architectural ways that these 3 database servers are different or similar.

• Architectural model
  
  • MySQL database server uses a dedicated server thread-based architecture.
  • PostgreSQL and DB2 use a dedicated server process model architecture.
• Storage engine
  
  • MySQL database uses pluggable storage engine architecture.
  • PostgreSQL and DB2 use a dedicated storage engine.
• Table space model
  
  • MySQL uses table space for InnoDB and NDB storage engines.
  • PostgreSQL table spaces can span multiple databases.
  • DB2 table spaces are specific to a database.
• Schema support
  
  • PostgreSQL and DB2 have true schema support.
  • MySQL does not have true schema support. A schema in MySQL can be thought as a MySQL database.
• Case sensitivity for database object names
  
  • MySQL uses case-sensitive names for database, tables, and columns on Linux. PostgreSQL table names are case-insensitive on both UNIX and Windows and are stored in lower case for versions 8.1.3 and 8.1.0.
  • DB2 stores object names in upper case by default on all platforms. However, DB2 object names can be stored in mixed or lower case if placed within double quotes in DDL CREATE statements.
• Array columns
  
  • MySQL and DB2 do not support array columns.
  • PostgreSQL does support array columns.
• Authentication
  
  • DB2 performs authentication using various external security schemes such as the operating system, PAM, Kerberos, Active Directory, LDAP, and so on. It also allows third party security modules to be plugged-in.
  • PostgreSQL performs authentication in one of many different ways. It can use database users/roles, the operating system, PAM, Kerberos, and so on, based upon settings in its host based configuration (pg_hba.conf) file.
  • MySQL implements authentication at the database level and passwords are encrypted.
• Instance architecture
  
  • DB2 instance manages different databases and there could be many DB2 instances on the same machine.
  • PostgreSQL concept of instance is similar to database clustering.
  • MySQL instance is similar to a DB2 database.

It is true that MySQL and PostgreSQL databases can post feature changes almost weekly, whereas feature implementation and changes in DB2 occur after a very careful planning as there are innumerable dependencies using DB2 products. Please note that we used MySQL 5.1, PostgreSQL 8.0.3, and DB2 V8.2 for comparison purposes at the time of writing, so take that into consideration when you read this article.

Figure 1, Figure 2, and Figure 3 are architectural diagrams of MySQL, PostgreSQL and DB2. We drew the architectural diagrams for MySQL and PostgreSQL to the best of our ability after reading through several documents. If you find a discrepancy, please let us know and we will correct it.

MySQL

MySQL uses a thread-based architecture, whereas PostgreSQL and DB2 are both based on a process-based architecture. A MySQL instance, as you see in Figure 1, can manage many databases. There is a common system catalog named INFORMATION_SCHEMA shared by all MySQL databases in an instance.

A database in DB2 is a separate entity by itself having its own system catalog, table spaces, buffer pools, and so on. The DB2 instance manages different databases, and they do not share table spaces, logging, system catalogs, or temporary table spaces.

A single instance of MySQL with many databases can be visualized as a single database in DB2 with each MySQL database represented as a schema in DB2. If you have several MySQL instances running on your server and each instance managing several databases, you can take one of the following migration paths:

• Migrate each MySQL instance as a DB2 database under a single DB2 instance.
• Migrate each MySQL instance as a DB2 instance with one DB2 database having a different schema for each MySQL database.

Note: When we refer to process-based architecture, we are referring to UNIX® platforms, as the same model on Windows will be a thread-based architecture. This holds true for both DB2 and PostgreSQL.

You can only access a single database resource within the scope of a DB2 connection whereas MySQL allows you to access multiple database resources within the scope of the same connection.

One of the interesting feature of MySQL is its pluggable storage engines. You can choose either MyISAM, InnoDB, Archive, Federated, Memory, Merge, Cluster, NDB or Custom storage engine. Each storage engine has different characteristics, and one can choose a particular storage engine based upon the specific requirements. For comparison purposes, we find InnoDB to be closest to a relational database.

A MySQL server process (mysqld) can create a number of threads:

• A global thread (per server process) is responsible for creating and managing each user connection thread.
• A thread is created to handle each new user connection.
• Each connection thread also performs authentication and query execution.
• On Windows, there is a named pipe handler thread that does the same work as the connection thread for named pipe connection requests.
• A signal thread handles alarms and forces timeouts on connections that have been idle too long.
• A thread is allocated to handle shutdown events.
• There are threads for handling synchronization of master and slave servers for replication.
• Threads are used for table flushing, maintenance tasks, and so on.

MySQL uses a data cache, record cache, key cache, table cache, hostname cache, and privilege cache for caching and retrieval of different types of data used by all the threads executing within the server process.

In addition, the MySQL main process (mysqld) has threads to handle database management activities such as backup, restore, concurrency control, and so on.

PostgreSQL

A PostgreSQL instance, shown in Figure 2, can manage a cluster of databases. Each database has its own set of system catalogs known as INFORMATION_SCHEMA and pg_catalog. All databases share pg_databases as a common system table. Each database is a separate entity and the collection of databases is called a cluster. A PostgreSQL instance can be thought as managing a cluster of databases. A server can run more than one such instance of database clusters.

A PostgreSQL database can be migrated logically to a DB2 database. Both the databases support the schema object type. You can not access other databases from a named connection.

The most significant difference between PostgreSQL and DB2 is related to table spaces. A PostgreSQL table space can span one or more database whereas DB2 table spaces are specific to a database.

A PostgreSQL session consists of several main processes:

• A postmaster process serves as a supervisory process that spawns other processes and listens for user connections.
• A user process such as psql is used for interactive SQL queries.
• One or more server processes named postgres are spawned by postmaster to handle users' requests for data.
• The server processes communicate with each other through semaphores and shared memory.

DB2

Figure 3 shows DB2's architecture. This figure explains how DB2 handles data between disks (files, raw devices, directories, and so on) using buffer pools. DB2 uses a connection concentrator to handle large number of connections. DB2 page cleaners and pre-fetchers do the work asynchronously and separate processes handle redo log activity. Please refer to the Resources for a detailed description of how locking and processes work in DB2.

A DB2 session consists of several processes:

• db2sysc, the main DB2 system controller or engine process
• Listener processes such as db2tcpcm and db2ipccm that listen for a user's request for connection
• One or more agents that work on behalf of an application. Agents can be either of two types:
  • db2agent works on behalf of an application and communicates to other agents using inter-process communication or remote communication protocol.
  • db2agntp works to fulfill client requests on the database when intra-parallel is turned on.
• A user's process such as db2 which is used for interactive querying from the command line
• db2bp - a persistent background process for DB2 Command Line Processor (CLP)
• db2disp - an agent dispatcher process to assign connections to available coordinating agents when connection concentrator is enabled
• db2fmcd - a fault monitor coordinating daemon per server
• db2fmd - a fault monitor daemon for each instance
• db2resyn - a resync manager process to handle two-phase commit
• db2dlock - a db2 dead lock detector
• db2loggr - the database log reader
• db2loggw - the database log writer
• db2pclnr - the buffer pool page cleaner
• db2pfchr - the buffer pool pre-fetcher
• db2fmp - to run user code on the server outside db2 address space
• ... and many more

DB2 server processes communicate with each other through memory areas known as Database Manager Memory and Database Shared Memory as shown in Figure 4.

Feature comparison

Table 1 compares MySQL, PostgreSQL and DB2 features. This is not an all inclusive list, but a comparison of the most commonly used features.

So far, we have seen some architectural and feature differences between MySQL, PostgreSQL and DB2 in this section. Let us now explore the data type differences in each of the database servers.

Back to top

Data type comparisons between MySQL, PostgreSQL and DB2

The SQL ANSI standard lays out rules for data types used in relational database systems. However, not every database platform supports each and every data type defined by the standards committees. Furthermore, vendor data types of a given name may be implemented differently from what the standard may specify and even differently from all other database vendors. As a result, although many MySQL, PostgreSQL and DB2 data types are similar in either their name or meaning or both, many differences can be noted as well.

Table 2 lists the most commonly used DB2 data types. We provide the closest match to DB2 for MySQL and PostgreSQL data types in subsequent sections.

As far as DB2 SQL limits (such as length limits on constraint names, data types limits, and so on) are concerned, these limits are being taken away systematically with each new release.

MySQL and DB2

The definitions and differences between the MySQL and DB2 data types are described in the following tables. Table 3 describes the most commonly used MySQL data types. Table 4 maps the MySQL data type to the closest DB2 equivalent.

MySQL might use a SERIAL alias for as a data type which translates to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

BOOL or BOOLEAN is a synonym for TINYINT(1). The maximum digit for DECIMAL in MySQL is 65 and maximum number of supported decimal is 30. If UNSIGNED is specified for DECIMAL, negative values are not allowed.

The timestamp column does not support microseconds.

PostgreSQL and DB2

The definitions and differences between the DB2 and PostgreSQL data types are described in the following two tables. Table-4 describes the most commonly used PostgreSQL data types. Table-5 maps the PostgreSQL data type to the closest DB2 equivalent.

PostgreSQL uses special network address types such as inet, cidr, macaddr. These data types are migrated to the VARCHAR data type in DB2.

PostgreSQL supports geometric data types as well. The tool does not handle geometric data types. Currently, we are assuming that there is not a great need to support the conversion of such data types. If you do use geometric data type, please send us a note and we will provide a fix in the tool.

Bit string data types in PostgreSQL can be handled with some modifications in your application. At this time, this is not supported in the tool. If you have a need for this, please let us know about it.

PostgreSQL also supports multi-dimensional arrays and they are best migrated in DB2 as child tables. However, the tool does not support multi-dimensional arrays at this time.

You can create a foreign key constraint in PostgreSQL even if the data type is different in the referenced table. For example, if parent table's unique key has a data type as integer, you can create a foreign key on child table for a column using a data type as char(10). The tool will convert the constraint but it will fail as DB2 does not allow data type to be different.

Now, we have seen the data type differences between MySQL, PostgreSQL and DB2 along with some high level feature differences. Let us now discuss migration approach to DB2 in three easy steps.

• Step 1: Install DB2 Express/Express-C
• Step 2: Run the migration tool
• Step 3: Migrate to DB2

Back to top

Step 1: Install DB2 Express/Express-C

Both DB2 Express and Express-C can be installed on Linux or Windows systems running with 32 or 64 bit hardware with up to 2 processors and 4GB of addressable memory. DB2 Express-C can easily be upgraded to DB2 Express, Workgroup and Enterprise Server Editions without modifying the database or applications such as C/C++, Java, .NET, and PHP.

The installation process is largely the same for Linux or Windows. Installation of DB2 Express involves performing the following easy steps:

1. Log in to your system using a local administrator account (on Windows) or as the root user (on Linux)
2. Execute the setup.exe on Windows and the Setup launch pad appears
3. Execute setup on Linux to do GUI installation or run db2install for command line install.
• If you use db2install, you will need to create DB2 instances and so forth manually.
4. In GUI install, select install product to launch the Setup wizard
5. Follow the installation wizard and supply input when prompted
6. For products embedded with DB2 Express-C, you can choose to perform a silent install using a response file.

A few additional notes:

• Setting up the DB2 instance owner
• The default user id on Windows is db2admin and db2inst1 on Linux.
• On Linux, one difference is that besides the instance owner user id you will also be prompted to provide an additional user id for the fenced user. The fenced user id will be used to run external C or Java stored procedures and user-defined functions.
• If the specified user does not exist, it will be created and granted the necessary privileges
• If an existing user id is used, it must already have administrator privileges (Windows).
• During installation a default instance is created
• On Windows it is called DB2.
• On Linux, it is called db2inst1.

By default, the DB2 server is configured to use TCPIP on port 50000. This setting can be optionally changed using the protocols button.

After the installation First Steps will be launched and can be used to help you create your first database called SAMPLE

You can download DB2 Express / Express-C from Resources.

Back to top

Step 2: Migrate DDL and data using the tool

Modifications to PostgreSQL setup

If you will be connecting to a PostgreSQL database from a remote machine, enable the remote client connections to the PostgreSQL server as follows:

1. Modify the pg_hba.conf to allow remote connections.

   Find the pg_hba.conf file in the postgres database directory. Add a line in the pg_hba.conf file to allow remote TCPIP connections to PostgreSQL database as follows:

   host all all 9.0.0.0 255.0.0.0 trust

   
   

   The 4th parameter specifies the IP address range and the 5th parameter specifies the subnet mask. In above example, we exposed all ip’s starting with 9 to be able to connect to PostgreSQL database.

2. Start the Postgres database server from the command line.

   $ pg_ctl -D /home/postgres/testdb -o -i -l logfile start

   
   

Required software for migration

• JDBC driver PostgreSQL

  To connect to PostgreSQL, use the JDBC driver to connect to the database. At the time of this writing, we used PostgreSQL 8.0.3 with JDBC driver 8.0.315. You can download the JDBC driver for PostgreSQL from resources. Please note that the tool provided in this paper does not include the PostgreSQL JDBC driver.

• JDBC Driver MySQL

  We used MySQL Connector/J 3.1 V 3.1.12 to connect to MySQL database. You can download the JDBC driver for the MySQL database from resources. The tool provided does not include the JDBC driver.

  After installing the JDBC driver, modify the CLASSPATH parameter to include the JDBC driver.

• IBM JDK 5.0

  The tool has been tested only with Java JDK 5.0. You can use either a Sun or IBM Java JDK 5.0 to run the tool. Download the IBM 5.0 JDK from resources

Setup migration tool

On UNIX systems, you can either install the tool under the DB2 instance user home directory or some other user who has the necessary permissions to run the DB2 LOAD utility.

After you download the tool from download section, unzip the file in a directory. The tool is provided in IBMExtract.jar file. Update the CLASSPATH variable in your profile to include the tool and other necessary JDBC drivers. For example, the following example shows how to include IBMExtract.jar, PostgreSQL and MySQL JDBC drivers in your CLASSPATH.

export JAVA_HOME=/opt/ibm/java2-i386-50
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=$HOME/java/lib/IBMExtract.jar:$CLASSPATH
export CLASSPATH=$HOME/java/lib/postgresql-8.0-315.jdbc3:$CLASSPATH
export CLASSPATH=$HOME/java/lib/mysql-connector-java-3.1.12-bin.jar:$CLASSPATH
		 

On Windows systems, update the CLASSPATH environment variable through Control Panel -> System -> Advanced -> Environment Variables.

Tool structure

There are two components of the tool. The first component (ibm.GenInput) generates the input file for use by the second component (ibm.GenerateExtract). The input file generated by the first component can be modified by the user to remove the tables that are not needed for the migration. Optionally, you can also edit the input file to specify the tables that might be created in DB2 as a result of custom query.

ibm.GenInput

The script to run the first component is a geninput shell script on Linux and geninput.cmd on Windows. You will need to specify correct parameters for your environment to connect to MySQL/PostgreSQL database. You will need to change DBVENDOR, SERVER, DATABASE, PORT, DBUID and DBPWD parameters in the following script. The script is shown below:

						
@echo off
cls

ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% %TIME%
ECHO.
ECHO -------------------------------------------------------------------
ECHO Program to perform MySQL/PostgreSQL to DB2 Migration
ECHO -------------------------------------------------------------------
ECHO.


if "%1" == ""  (
  echo Usage : geninput.cmd dbname
  goto end
)

SET DBVENDOR=postgres

SET DB2SCHEMA=%1
SET SERVER=server.ibm.com
SET DATABASE=%1
SET PORT=5432
SET DBUID=postgres
SET DBPWD=pwd
%JAVA_HOME%\bin\java -DINPUT_DIR=.\migr -cp %CLASSPATH% ibm.GenInput 
  %DBVENDOR% %DB2SCHEMA% %SERVER% %DATABASE% %PORT% %DBUID% %DBPWD%
:end		       

						
#!/bin/bash
if [ "$1" = ""  ] ; then
  echo Usage : geninput dbname
  exit 1
fi

DBVENDOR=postgres
DB2SCHEMA=$1
SERVER=server.ibm.com
DATABASE=$1
PORT=5432
DBUID=postgres
DBPWD=pwd

java -DINPUT_DIR=$PWD -cp $CLASSPATH ibm.GenInput $DBVENDOR $DB2SCHEMA 
     $SERVER $DATABASE $PORT $DBUID $DBPWD 				   
		       

The INPUT directory to store the input files for the tool is specified through VM parameter as -DINPUT_DIR. In above script, it is specified as current directory. The program will create an input directory in current working directory.

Input file creation parameters

Structure of the input file

The first component of the tool ibm.GenInput generates an input file to be used by second component ibm.GenerateExtract. The structure of the input file is provided below in case you need to modify the query part of the file to create a table based upon a query on source database.

DB2_Schema_Name.Actual_Table_Name:Table Query
		 

Sample content of the file is below:

ama.ama_msa:SELECT * FROM public.ama_msa
ama.ama_mti:SELECT * FROM public.ama_mti
ama.ama_pe:SELECT * FROM public.ama_pe
ama.ama_pmsa:SELECT * FROM public.ama_pmsa
ama.ama_schools:SELECT * FROM public.ama_schools
ama.ama_specialties_group:SELECT * FROM public.ama_specialties_group
		   

ibm.GenerateExtract

The script to unload the data and to generate DDL for DB2 is an unload shell script on Linux and an unload.cmd script on Windows. You will need to modify the following connection parameters for the MySQL/PostgreSQL databases; DBVENDOR, SERVER, PORT, DBUID and DBPWD. The unload script is shown in the following tables for Windows and Linux environment.

						
@echo off
cls

ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% %TIME% 
ECHO.
ECHO -------------------------------------------------------------------
ECHO Program to perform MySQL/PostgreSQL to DB2 Migration
ECHO -------------------------------------------------------------------
ECHO.


if "%1" == ""  (
  echo Usage : unload.cmd dbname
  goto end
)

SET TABLES=input\%1.tables
SET COLSEP=~
SET DBVENDOR=postgres
SET NUM_THREADS=5
SET SERVER=server.ibm.com
SET DATABASE=%1
SET PORT=5432
SET DBUID=postgres
SET DBPWD=pwd
SET GENDDL=true
SET UNLOAD=true
SET FETCHSIZE=100
%JAVA_HOME%\bin\java -DOUTPUT_DIR=output\%1 -cp %CLASSPATH% 
  ibm.GenerateExtract %TABLES% %COLSEP% %DBVENDOR% %NUM_THREADS% %SERVER% 
  %DATABASE% %PORT% %DBUID% %DBPWD% %GENDDL% %UNLOAD% %FETCHSIZE%
:end
				

						
#!/bin/bash
if [ "$1" = ""  ] ; then
  echo Usage : unload dbname
  exit 1
fi

TABLES=$PWD/input/$1.tables
COLSEP=\~
DBVENDOR=postgres
NUM_THREADS=5
SERVER=db2lab9.dfw.ibm.com
DATABASE=$1
PORT=5432
DBUID=postgres
DBPWD=db2mig
GENDDL=true
UNLOAD=true
FETCHSIZE=100

java -DOUTPUT_DIR=$PWD/output/$1 -cp $CLASSPATH ibm.GenerateExtract 
   $TABLES $COLSEP $DBVENDOR $NUM_THREADS $SERVER $DATABASE 
   $PORT $DBUID $DBPWD $GENDDL $UNLOAD $FETCHSIZE
		       

We specify OUTPUT_DIR to the java program through a -D switch to the JVM. In this case, it is defined as output/$1, which will be the output/database name that you specify through the unload command.

Unload parameters

Driver properties

The tool uses a driver properties file to read JDBC driver information for MySQL / PostgreSQL database. The properties file is included in the JAR file.

postgres=org.postgresql.Driver
mysql=com.mysql.jdbc.Driver			 
		 

URL properties

The tool uses the URL properties file to read JDBC driver URL information for MySQL / PostgreSQL database. This poperties file is included in the JAR file.

postgres=jdbc:postgresql://
mysql=jdbc:mysql://			 
		 

Mapping properties

Data type mapping between MySQL / PostgreSQL and DB2 is controlled by using a data type mapping properties file. This file is included in the JAR file. If there is a need to change the data type mapping properties between MySQL / PostgreSQL and DB2, you can just modify this file and do not have to modify the program.

POSTGRES.INT=INTEGER
POSTGRES.INT2=SMALLINT
POSTGRES.INT4=INTEGER
POSTGRES.INT8=INTEGER
POSTGRES.SERIAL4=INTEGER
POSTGRES.SERIAL8=INTEGER
POSTGRES.BOOLEAN=SMALLINT
POSTGRES.BYTEA=BLOB
POSTGRES.VARCHAR=VARCHAR;VARLENGTH=TRUE;DEFAULT=255
POSTGRES.CHARACTER=CHAR;VARLENGTH=TRUE
POSTGRES.BPCHAR=CHAR;VARLENGTH=TRUE
POSTGRES.DATE=DATE
POSTGRES.FLOAT4=REAL
POSTGRES.FLOAT8=DOUBLE PRECISION
POSTGRES.INTEGER=INTEGER
POSTGRES.NUMERIC=NUMERIC;VARLENGTH=TRUE
POSTGRES.TEXT=VARCHAR;VARLENGTH=TRUE;DEFAULT=255;USEACTUALDATA=TRUE
POSTGRES.TIME=TIME
POSTGRES.TIMESTAMP=TIMESTAMP
POSTGRES.OID=INTEGER
MYSQL.BOOLEAN=SMALLINT
MYSQL.BIT=SMALLINT
MYSQL.TINYBLOB=VARCHAR(255) FOR BIT DATA
MYSQL.BLOB=BLOB;VARLENGTH=TRUE
MYSQL.MEDIUMBLOB=BLOB;VARLENGTH=TRUE
MYSQL.LONGBLOB=BLOB;VARLENGTH=TRUE
MYSQL.CHAR=CHAR;VARLENGTH=TRUE
MYSQL.CHARACTER=CHAR;VARLENGTH=TRUE
MYSQL.DATE=DATE
MYSQL.DATETIME=TIMESTAMP
MYSQL.YEAR=SMALLINT
MYSQL.NUMERIC=NUMERIC;VARLENGTH=TRUE
MYSQL.DECIMAL=NUMERIC;VARLENGTH=TRUE
MYSQL.FLOAT=REAL
MYSQL.DOUBLE=DOUBLE
MYSQL.REAL=DOUBLE
MYSQL.TINYINT=SMALLINT
MYSQL.SMALLINT=SMALLINT
MYSQL.MEDIUMINT=INT
MYSQL.INTEGER=INT
MYSQL.BIGINT=BIGINT
MYSQL.BIT_UNSIGNED=SMALLINT
MYSQL.TINYINT_UNSIGNED=SMALLINT
MYSQL.SMALLINT_UNSIGNED=SMALLINT
MYSQL.MEDIUMINT_UNSIGNED=INT
MYSQL.INTEGER_UNSIGNED=INT
MYSQL.BIGINT_UNSIGNED=BIGINT
MYSQL.DECIMAL_UNSIGNED=NUMERIC;VARLENGTH=TRUE
MYSQL.NUMERIC_UNSIGNED=NUMERIC;VARLENGTH=TRUE
MYSQL.TINYTEXT=VARCHAR;VARLENGTH=TRUE;DEFAULT=255
MYSQL.TEXT=VARCHAR;VARLENGTH=TRUE;DEFAULT=65535
MYSQL.MEDIUMTEXT=CLOB(16M)
MYSQL.LONGTEXT=CLOB(2G)
MYSQL.TIME=TIME
MYSQL.TIMESTAMP=TIMESTAMP
MYSQL.VARCHAR=VARCHAR;VARLENGTH=TRUE;DEFAULT=255
MYSQL.BINARY=CHAR FOR BIT DATA;VARLENGTH=TRUE
MYSQL.VARBINARY=VARCHAR FOR BIT DATA;VARLENGTH=TRUE		 
		 

How to run the tool on Windows or Linux

To use this tool, there are only two commands that you need to run. The first command is named geninput.cmd on Windows and geninput on Linux. The second command is named unload.cmd on Windows and unload on Linux. The example shown here is for Linux but it is the same for both platforms.

First - Generate input file

If you do not specify an argument for the geninput script, a message will be shown stating that a MySQL/PostgreSQL database name is required (shown below). Re-run the script and specify the name of the MySQL/PostgreSQL database; the input file for the given database will be generated.

db2@db2lab9:~/migr> ./geninput
Usage : geninput dbname

db2@db2lab9:~/migr> ./geninput ama
[2006-05-23 09.35.54.563] dbSourceName:postgres
[2006-05-23 09.35.54.564] db2SchemaName:ama
[2006-05-23 09.35.54.565] server:server.ibm.com
[2006-05-23 09.35.54.565] dbName:ama
[2006-05-23 09.35.54.565] port:5432
[2006-05-23 09.35.54.565] uid:postgres
[2006-05-23 09.35.54.566] INPUT Directory = /home/db2/migr/input
[2006-05-23 09.35.54.575] Configuration file loaded: 'driver.properties'
[2006-05-23 09.35.54.576] Configuration file loaded: 'url.properties'
[2006-05-23 09.35.54.599] Driver org.postgresql.Driver loaded
[2006-05-23 09.35.54.960] ama.ama_addresstype:SELECT * FROM public.ama_addresstype
ama.ama_country_codes:SELECT * FROM public.ama_country_codes
ama.ama_hosp_affil:SELECT * FROM public.ama_hosp_affil
ama.ama_msa:SELECT * FROM public.ama_msa
ama.ama_mti:SELECT * FROM public.ama_mti
ama.ama_pe:SELECT * FROM public.ama_pe
ama.ama_physicians:SELECT * FROM public.ama_physicians
ama.ama_pmsa:SELECT * FROM public.ama_pmsa
ama.ama_schools:SELECT * FROM public.ama_schools
ama.ama_specialties_group:SELECT * FROM public.ama_specialties_group
ama.ama_top:SELECT * FROM public.ama_top
ama.ama_type_of_practice:SELECT * FROM public.ama_type_of_practice
ama.calculation:SELECT * FROM public.calculation
ama.calculation_group:SELECT * FROM public.calculation_group
ama.category:SELECT * FROM public.category
ama.code_lookup:SELECT * FROM public.code_lookup
ama.physician_calculation:SELECT * FROM public.physician_calculation
ama.physician_calculation_group:SELECT * FROM public.physician_calculation_group
ama.physician_category:SELECT * FROM public.physician_category
ama.possible_answer:SELECT * FROM public.possible_answer
ama.question:SELECT * FROM public.question
ama.topic:SELECT * FROM public.topic
		 

The input file will be created in the input directory of your current working directory. You can modify this file to remove tables that you do not want to migrate by removing the SQL query which would unload the data.

Second - Generate DDL and unload the data

You are now ready to run the extract program that generates the DDL for DB2 and unloads the data from the MySQL / PostgreSQL database. You will need to specify the name of the database as an argument for this program.

			
db2@db2lab9:~/migr> ./unload ama
[2006-05-23 09.40.43.157] TABLES_PROP_FILE:/home/db2/migr/input/ama.tables
[2006-05-23 09.40.43.159] DRIVER_PROP_FILE:driver.properties
[2006-05-23 09.40.43.160] URL_PROP_FILE:url.properties
[2006-05-23 09.40.43.161] DATAMAP_PROP_FILE:datamap.properties
[2006-05-23 09.40.43.162] colsep:~
[2006-05-23 09.40.43.162] dbSourceName:postgres
[2006-05-23 09.40.43.163] threads:5
[2006-05-23 09.40.43.164] server:server.ibm.com
[2006-05-23 09.40.43.165] dbName:ama
[2006-05-23 09.40.43.166] port:5432
[2006-05-23 09.40.43.167] uid:postgres
[2006-05-23 09.40.43.168] fetchSize:100
[2006-05-23 09.40.43.186] Configuration file loaded: '/home/db2/migr/input/ama.tables'
[2006-05-23 09.40.43.188] query size 22 schemaName size = 22
[2006-05-23 09.40.43.387] Configuration file loaded: 'driver.properties'
[2006-05-23 09.40.43.389] Configuration file loaded: 'url.properties'
[2006-05-23 09.40.43.398] Configuration file loaded: 'datamap.properties'
[2006-05-23 09.40.43.414] Driver org.postgresql.Driver loaded
[2006-05-23 09.40.43.606] Starting Blades
[2006-05-23 09.40.43.607] Starting Blade_1
[2006-05-23 09.40.43.613] Starting Blade_0
[2006-05-23 09.40.43.613] Starting Blade_3
[2006-05-23 09.40.43.615] Starting Blade_2
[2006-05-23 09.40.43.615] Starting Blade_4
[2006-05-23 09.40.43.883] Blade_3 unloaded 21 rows in 269 ms for ama.ama_pe
[2006-05-23 09.40.44.218] Blade_4 unloaded 5 rows in 603 ms for ama.ama_addresstype
[2006-05-23 09.40.44.273] Blade_3 unloaded 0 rows in 390 ms for ama.possible_answer
[2006-05-23 09.40.44.560] Blade_1 unloaded 10 rows in 952 ms for ama.ama_top
[2006-05-23 09.40.44.569] Blade_3 unloaded 0 rows in 296 ms for ama.category
[2006-05-23 09.40.44.687] Blade_2 unloaded 0 rows in 1072 ms for ama.physician_calculation
[2006-05-23 09.40.44.718] Blade_4 unloaded 0 rows in 500 ms for ama.question
[2006-05-23 09.40.44.881] Blade_3 unloaded 0 rows in 312 ms for ama.calculation_group
[2006-05-23 09.40.44.914] Blade_2 unloaded 384 rows in 227 ms for ama.ama_pmsa
[2006-05-23 09.40.44.984] Blade_4 unloaded 493 rows in 266 ms for ama.ama_country_codes
[2006-05-23 09.40.45.076] Blade_2 unloaded 13 rows in 162 ms for ama.ama_type_of_practice
[2006-05-23 09.40.45.343] Blade_4 unloaded 201 rows in 359 ms for 
ama.ama_specialties_group
[2006-05-23 09.40.45.451] Blade_1 unloaded 7141 rows in 891 ms for ama.ama_hosp_affil
[2006-05-23 09.40.45.691] Blade_0 unloaded 6102 rows in 2078 ms for ama.ama_mti
[2006-05-23 09.40.45.869] Blade_1 unloaded 0 rows in 418 ms for ama.code_lookup
[2006-05-23 09.40.46.024] Blade_0 unloaded 0 rows in 333 ms for ama.calculation
[2006-05-23 09.40.46.236] Blade_0 unloaded 0 rows in 212 ms for 
ama.physician_calculation_group
[2006-05-23 09.40.46.380] Blade_0 unloaded 0 rows in 144 ms for ama.physician_category
[2006-05-23 09.40.46.405] Blade_1 unloaded 1863 rows in 536 ms for ama.ama_schools
[2006-05-23 09.40.46.539] Blade_1 unloaded 4 rows in 134 ms for ama.ama_msa
[2006-05-23 09.40.46.917] Blade_0 unloaded 0 rows in 537 ms for ama.topic
[2006-05-23 09.40.48.931] ama_physicians 10000 rows unloaded in 3.835 sec
[2006-05-23 09.40.52.048] ama_physicians 10000 rows unloaded in 3.117 sec
........
[2006-05-23 09.44.21.891] ama_physicians 10000 rows unloaded in 2.152 sec
[2006-05-23 09.44.24.200] ama_physicians 10000 rows unloaded in 2.309 sec
[2006-05-23 09.44.26.670] Blade_2 unloaded 969995 rows in 221594 ms for ama.ama_physicians
[2006-05-23 09.44.26.671] ==== Total time: 223.0 sec 
[2006-05-23 09.44.26.923] done Blade_0
[2006-05-23 09.44.27.175] done Blade_1
[2006-05-23 09.44.27.427] done Blade_2
[2006-05-23 09.44.27.679] done Blade_3
[2006-05-23 09.44.27.931] done Blade_4
		 

Tool output

After the tool is successfully run against your database, navigate to the output directory (in our example, output/ama) to view the output which will appear as shown below:

			
db2@db2lab9:~/migr> ls -l output/ama
total 76
drwxr-xr-x  2 db2 db2  4096 2006-05-23 06:16 ama_data
-rw-r--r--  1 db2 db2  1212 2006-05-23 09:44 ama_db2checkpending.sql
-rw-r--r--  1 db2 db2  2687 2006-05-23 09:44 ama_db2cons.sql
-rw-r--r--  1 db2 db2   662 2006-05-23 09:44 ama_db2drop.sql
-rw-r--r--  1 db2 db2  1378 2006-05-23 09:44 ama_db2fkdrop.sql
-rw-r--r--  1 db2 db2  3523 2006-05-23 09:44 ama_db2fkeys.sql
-rw-r--r--  1 db2 db2 13190 2006-05-23 09:44 ama_db2load.sql
-rw-r--r--  1 db2 db2  3148 2006-05-23 09:44 ama_db2runstats.sql
-rw-r--r--  1 db2 db2  1143 2006-05-23 09:44 ama_db2.sh
-rw-r--r--  1 db2 db2  1192 2006-05-23 09:44 ama_db2tabcount.sql
-rw-r--r--  1 db2 db2  7099 2006-05-23 09:44 ama_db2tables.sql
-rw-r--r--  1 db2 db2  4336 2006-05-23 09:44 ama_db2tabstatus.sql
drwxr-xr-x  2 db2 db2  4096 2006-05-23 06:06 ama_dump
drwxr-xr-x  2 db2 db2  4096 2006-05-23 06:06 ama_msg			 
		 

The explanation of each file generated is given in following table.

Due to how different databases implement constraints, there a few points to note:

• In MySQL/PostgreSQL, you can create a unique constraint (or index) index on a nullable column however DB2 requires that a column with a unique constraint is defined as NOT NULL.
• In MySQL/PostgreSQL, a foreign key can be created for a primary key even if the primary key is null. DB2 does not allow primary keys with nulls.
• The tool handles these differences by automatically generating the required DDL for DB2 and will define unique or primary key columns as NOT NULL. As a result, some data may require modifications in order to be migrated.

Back to top

Step 3: Migrate to DB2

Now, you are ready to run the migration. You have generated all the scripts that you need to create DB2 objects. Let us walk you through some of the best practices that can make your DB2 experience as simple as possible.

Create database in DB2

You can create DB2 database with the command CREATE DB dbname. When you create a DB2 database, it creates three table spaces for you - System, Temporary and User. If you use DB2 Viper, it will create the USER table space as a database managed (DMS) using automatic storage. The DMS table spaces give you the best performance along with a good sized buffer pool. Your temporary table space should be created as system managed (SMS).

Run Autoconfigure command for tuning

After you created the database, run DB2 AUTOCONFIGURE command as shown in Listing 9 to tune your database upfront. Answer 10 questions and run the command against your database. Your best bet is to run this command from Control Center as it is very intuitive to run it through GUI.

Run autoconfigure command against your database or use DB2 Control Center to run it interactively through the GUI.

					
$ db2 connect to yourdbname
$ db2 autoconfigure using 
   mem_percent      85 
   workload_type    simple 
   num_stmts        20 
   tpm              3000 
   admin_priority   performance 
   is_populated     yes 
   num_local_apps   0 
   num_remote_apps  500 
   isolation        cs 
   bp_resizeable    yes 
apply db and dbm;
$ db2 connect reset			 			 
		 

Create table spaces and buffer pools before running the migration scripts

One of the outputs from the tool is the approximate maximum size of the row in each table. The maximum row size will determine if you need to use a 4K, 8K, 16K or 32K page size for your table space. After going through the output of this file, decide how many table spaces you need for each size. Use the DB2 Control Center to create these table spaces (with automatic storage) before you create the objects. If you have at least one table space for each page size, all of your table creation scripts will run as they should without the need to specify which table space they should be created in. If you want to make life even easier for yourself, create one 32K page size DMS table space and one 4K size DMS table space before running the table creation script.

If you have been using LOBS in MySQL/PostgreSQL database, you will need to also create LARGE table space. Again do it through the Control Center as it is easily done that way.

Run migration scripts in your DB2 database

After performing the above steps, run script <dbname>_db2.sh on Linux or <dbname>_db2.cmd on Windows to create all the objects and to load data into DB2. A sample script is shown below;

					
#!/bin/bash
if [ "$1" = "" ] ; then
   echo To run this script, specify name of the db2 database
   echo For example, \"./ama_db2 sample\"
   echo where sample is the name of the db2 database
   echo 
   exit 1
fi
OUTPUT=ama_db2.log
echo Executing Script ama_db2.sh > $OUTPUT
echo Connecting to $1
db2 connect to $1 >> $OUTPUT
echo Running ama_db2tables.sql script to create all tables
db2 -tvf ama_db2tables.sql >> $OUTPUT
echo Running ama_db2cons.sql script to create primary keys and indexes
db2 -tvf ama_db2cons.sql >> $OUTPUT
echo Running ama_db2load.sql script to create to load the data
db2 -tvf ama_db2load.sql >> $OUTPUT
echo Running ama_db2fkeys.sql script to create all foreign keys
db2 -tvf ama_db2fkeys.sql >> $OUTPUT
echo Running ama_db2tabcount.sql script to count rows from all tables
db2 -tvf ama_db2tabcount.sql >> $OUTPUT
echo Running ama_db2tabstatus.sql script to show status of tables after load
db2 -tvf ama_db2tabstatus.sql >> $OUTPUT
db2 connect reset >> $OUTPUT
echo 
echo Check the log file $OUTFILE for any errors or issues
echo
		 

The migration script performs following tasks.

• Creates all tables in DB2 database
• Creates all primary keys and indexes
• Loads data using the DB2 LOAD utility. This utility will load the data and also generate statistics for the data in your tables.
• After loading the data, creates the foreign key constraints.
• Performs the row counts for all DB2 tables so that you can verify the completeness of data movement
• Reports the availability status of the tables after the LOAD

If you have clean data, the migration should go smoothly. When you create foreign key constraints, check the log file for the errors and fix it in the source database before attempting a new migration

You have additional scripts that you might need for following possible purposes.

• To drop all tables in DB2
• To drop all foreign key constraints. It's a good idea to drop all foreign key constraints before you drop the tables.
• If your migration process went smoothly, you might not need to run the runstats command again (it was specified to run with the load) but it is provided in case you need to run it as a separate step.

Enable automatic maintenance on DB2 database

After your successful migration to DB2, it is highly recommended that you enable automatic maintenance for your database. Through the Control Center, you can configure DB2 to automate the management of backups, runstats and reorgs. You will need to specify a maintenance window so that DB2 knows when it can perform these jobs automatically. Again, this is not a job scheduler but you can also schedule maintenance jobs through DB2 Task Center.

Run autoconfigure again

After you have migrated the data, run the configuration advisor so that DB2 is optimally tuned for your work load.

Back to top

Frequently asked questions

Question: How do I get the source code of this tool?

Answer: First of all, we would like to know about the bugs / issues. We do not mind sharing the code with you if you promise to share with us the enhancements that you make. Please send a note to one of the authors if you would like to have the source.

Question: I am getting the errors when I create the tables.

Answer: You might be missing a table space of required page size. Create a table space of required page size like 8K, 16K or 32K.

Question: I get an error Missing data map for ... and application quits.

Answer: We must have missed adding the data type in the mapping.properties file. Extract mapping.properties file from the JAR file and add the missing data type and rebuild the JAR file. If it is too much of a problem, send a note to one of the authors.

Question: What are the limitations of the tool?

Answer: The tool does not handle column default values, check constraints, stored procedures or functions from MySQL / PostgreSQL.

Question: Does this tool handle sequence object of PostgreSQL?

Answer: The tool converts sequences to identity attribute. However, DB2 does allow use of sequence objects.

Question: Can I run this tool to do the migration from other databases?

Answer: Yes, you can. The tool has been developed as a generic tool of migration from any database that has a universal JDBC driver. We have tested this tool with Oracle database and it works well. But we have not done enough testing. This tool will also work with Microsoft SQL Server and Sybase. For migration from other well known databases to DB2, it is however recommended to use more versatile and free IBM tool Migration Toolkit also known as MTK. Use resources to download this tool.

Back to top

Conclusion

While we were developing this tool, our focus was to develop a very small footprint program for best performance and by using a multi-threaded approach we hoped to speed up the migration. This is not a fool-proof program and if you get errors or issues, please let us know. While we may not consider enhancement requests, we will be very receptive to fix the bugs.

There is no guarantee that this program will meet all your requirements, so please read the official disclaimer carefully before you begin.

It is our goal for you to start considering the use of this powerful database which until recently was largely found within the domain of large corporations. Start using DB2 Express-C and leverage the advantages of a world-class database to meet your own database needs.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Download

Information about download methods

Resources

Learn

• Porting to DB2 Universal Database The developerWorks DB2 Universal Database porting site gives you the information you need to port an application and its data from other database management systems to DB2 UDB.
  
  
• Visit the developerWorks DB2 zone to expand your DB2 skills.
  
  
• Stay current with DeveloperWorks technical events and Webcasts
  
  
• IBM Data Movement Tool UDFs to ease migration Developerworks Apr 2005
  
  
• Leverage PostgreSQL skills to learn DB2 Express Developerworks Mar 2006
  
  
• Everything You Wanted to Know About DB2 Universal Database Processes Developerworks Apr 2003
  
  
• Leverage MySQL skills to learn DB2 Express Developerworks Feb 2006
  
  
• DB2 Express-C, the developer-friendly alternative Developerworks Feb 2006
  
  
• Log DB2 UDB stored procedure messages Developerworks Jan 2006
  
  
• Getting to know the DB2 UDB command line processor Developerworks March 2005
  
  
• The IBM DB2 Universal Database for Linux, UNIX, and Windows Backup Utility Developerworks Jan 2005
  
  
• Lock avoidance in DB2 UDB V8 Developerworks Sep 2005
  
  
• MySQL to DB2 UDB Conversion Guide Redbook May 2004
  
  
• Migrating to IBM database servers gets easier with the latest MTK release Developerworks Mar 2006
  
  

Get products and technologies

• Download DB2 Express-C Absolutely Free
  
  
• Download DB2 Express
  
  
• Download Download IBM MTK
  
  
• Download Software Evaluation Kits on DVDs
  
  
• Download Featured product trials
  
  
• Download PostgreSQL JDBC Driver
  
  
• Download MySQL JDBC Driver
  
  
• Download IBM JDK 5.0
  
  

Discuss

• Participate in DB2 Express-C Forum
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• IBM Data Movement Tool
• Architectural overview and comparison
• Data type comparisons between MySQL, PostgreSQL and DB2
• Step 1: Install DB2 Express/Express-C
• Step 2: Migrate DDL and data using the tool
• Step 3: Migrate to DB2
• Frequently asked questions
• Conclusion
• Disclaimer
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Express-C
• Demo: Use Ajax with DB2
• Webcast: A smarter way to successful SOA – Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Express-C

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers