Developers zone > CUBRID Tools Wiki > CUBRID Migration Toolkit > CUBRID Migration Toolkit Manual > CUBRID Migration Toolkit Concepts

CUBRID Migration Toolkit Concepts

2 Concepts

2.1 CMT

CMT is the abbreviation of CUBRID Migration Toolkit.

CMT includes two parts, one is an application with GUI and another is a console tool.

2.1.1 CMT GUI

CMT GUI can provide some graphic user interfaces to help users finish their migration work.

2.1.2 CMT Console Tool

CMT Console Tool tool can run in linux and windows with command line.

Because of no GUI, it has better performace then CMT GUI.

It helps users finish migration work in some OS without GUI.

2.2 CUBRID UnloadDB File

Text files that can be loaded into offline CUBRID database by CUBRID loaddb command.

2.3 Migration Script

Migration script is a very important concept in CMT.

Migration scripts are written with XML format. A migration script lets the CMT know how to connect source/target databases and how to transform source DB objects to target DB objects during a migration process.

Migration scripts are created by migration wizard.

2.4 Migration Script Explorer

Migration script explorer is a view which is on the left part by default when CMT GUI is started.

All migration scripts will be listed in this explorer, users can edit the scripts by this explorer.

All that users can do with scripts are as follows:

Open with wizard: Open a migration wizard, the settings in the wizard is according to the selected migration script.

Rename: Rename the selected migration script.

Delete: Delete selected migration script.

Copy: Make a copy of current selected migration script.

Import: Import an external migration script file to list.

Export: Save selected migration script to an external XML file.

Execution History: If the migration script was set to a migration resveration, users can view this migration script’s starting histories.

Cancel Reservation: Cancel the migration reservation settings of selected migration script.

2.5 Migration Wizard

Migration wizard is a GUI wizard to help users build a migration script step by step.

There are 4 steps to build a migration script:

Step 1. Select Source Database

Step 2. Select Target Database

Step 3. Database Objects Mapping

Step 4. Confirm

When migration script is built, the migration process can be started.

Users can monitor the migration progress and view the report after migration is finished.

Step1. Select Source Database

The first step is selecting a database as data source. It can be a MySQL database, an Oracle database, a CUBRID database or a MySQL dump file with XML format.

The source database will be connected though JDBC driver.

If you want select a MySQL dump file as your data source, please specify the file charset and timezone.

Note: when you dump Mysql database, please add "--hex-blob" option for binary data, for example:

“mysqldump --xml --databases test -u mydbadmin -pmypass --hex-blob>test.xml”

Step2. Select Target Database

CMT supports exporting source database to an online/offline CUBRID database, CUBRID Unloaddb files, SQL script files or CSV files.

So at this step, please select a CUBRID database you want to import into or a directory you want to save unloaddb/SQL/CSV files.

If you want to import data into an offline CUBRID DB, you should know the CM server’s information of the offline CUBRID DB.

Step3. DB Object Mapping

CMT only supports exporting and importing tables,indexes, FKs, PKs, table partitions, views and serials.

The procedures, functions, triggers in the source database will not be imported into target CUBRID database.

So at this step, users can select which table/index/FK/PK/view/serial they want to import into the target database.

And a mapping between source and target DB objects will be created.

CMT also supports importing result sets of users’ defined SQL statements.

CMT will regard as the SQL statement as a virtual table.

Step4.Migra tion Information Confirm

At this step, users can review all settings abount this migration work and save or export current settings to a migration script.

If all settings are confirmed, pressing the "Start Migration" button, the wizard dialog will be closed and a migration progress view will be shown and migration process will be started.

Some performace settings can be changed in this wizard. Press Advanced Settings, a dialog will be poped as follows:

User can change thread count and commit count and make a decision that getting the total record count of source or not.

Besause if source database is very large, getting total record count may cost much time.
And if source is CUBRID database, an addition option “Page fetch count” can be shown in this dialog.

2.6 SQL Importing Wizard

This wizard can help users import several SQL script files to an online CUBRID database.

When users want import some large SQL script files into a online CUBRID database, it is a good tool.

It includes 3 parts:

Select SQL files

Select Target CUBRID database

Confirm

2.7 CSV Importing Wizard

This wizard can help users import several CSV files to an online CUBRID database.

When users want import some large CSV files into a online CUBRID database, it is a good tool.

It includes 3 parts:

Select Target CUBRID database

Select CSV files

Confirm

2.8 Migration Progress Monitor

If migration is started, users can monitor the migration progress by migration progress monitor.

Users can read how many records were exported and imported, what error was thrown and how much time was cost from migration progress monitor.

Users can also stop the migration process by clicking "Stop" button, and when the migration is finished the "View Report" button will be enabled and users can open the report view by clicking this buttons.

Migration progress Monitor

Users can monitor migration progress with this view. Every table’s migration status will be updated in time.

And users can break the migration if they don’t want the migration continue.

After migration finished, a button "View Report" can lead users to migration report view.

SQL importing progress Monitor

2.9 Migration Report

When migration was finished or interrupted, migration engine will generate a migration report and save it into a local file. Users can review the report by Migration History Manager.

The report includes a lot of information about this migration such as which table was created successfully/failed, how many records were exported and how many records were imported and the error logs during migration process.

Migration report

SQL importing report

2.10 Migration Task Reservation

If users want start a migration process at a specific time, not now, migration task reservation can do this.

Users can make the migration process start at a specific time or at a specific time every day or at a specific time with UNIX Cron pattern.

2.11 Migration History Manager

Every migration work generated a migration report and the report was saved at local.

Migration History manager lists the old migration reports and allows users to review the reports.

Users can delete the migration histories.

And users can import an external migration report file into history list and open it.

This is often used for checking a migration report generated by CMT console tool.

2.12 Data Type Mapping

Different databases have different data type definitions, so if we want do migration work between different database systems, we should build a data type mapping to let the CMT know how to transform the schemas and data from source database to target database. For example: The string definition is “varhcar2” in Oracle but “varchar” in CUBRID, so we should build a mapping between “varchar2” and “varchar”.

CMT provides a default data type mapping in the preference:

MySQL to CUBRID data type mapping

MySQL Type Name	Return value of JDBC	CUBRID Type
BIT(1) (new in MySQL-5.0)	BIT	Smallint
BIT( > 1) (new in MySQL-5.0)	BIT	Bit(n)
TINYINT	TINYINT	Smallint
BOOL, BOOLEAN	TINYINT	Smallint
SMALLINT<(M)> <UNSIGNED>	SMALLINT <UNSIGNED>	Smallint Integer
MEDIUMINT<(M)> <UNSIGNED>	MEDIUMINT <UNSIGNED>	Integer
INT,INTEGER<(M)> <UNSIGNED>	INTEGER <UNSIGNED>	Integer Bigint
BIGINT<(M)> <UNSIGNED>	BIGINT <UNSIGNED>	Bigint Numeric(20)
FLOAT<(M,D)>	FLOAT	FLOAT
DOUBLE<(M,B)>	DOUBLE	DOUBLE
DECIMAL<(M<,D>)>	DECIMAL	DOUBLE
DATE	DATE	DATE
DATETIME	DATETIME	DATETIME
TIMESTAMP<(M)>	TIMESTAMP	TIMESTAMP
TIME	TIME	TIME
YEAR<(2\|4)>	YEAR	Char(4)
CHAR(M)	CHAR	Char(n)
VARCHAR(M) <BINARY>	VARCHAR	Varchar(n)
BINARY(M)	BINARY	Bit Varying(n)
VARBINARY(M)	VARBINARY	Bit Varying(n)
TINYBLOB	TINYBLOB	Bit Varying(255) Blob
TINYTEXT	VARCHAR	Varchar(255)
BLOB	BLOB	Bit Varying(65535)
TEXT	VARCHAR	Varchar(65535) Clob
MEDIUMBLOB	MEDIUMBLOB	Bit Varying(16277215) Blob
MEDIUMTEXT	VARCHAR	Varchar(16277215) Clob
LONGBLOB	LONGBLOB	Bit Varying(1073741823) Blob
LONGTEXT	VARCHAR	Varchar(1073741823) Clob
ENUM('value1','value2',...)	CHAR	Varchar(255)
SET('value1','value2',...)	CHAR	SET( Varchar(255) )

Boolean Type

CUBRID does not have a Boolean type. Statements containing Boolean values are transformed by CMT to replace the value with conditional expressions. CMT emulates stored Boolean data by using the CUBRID smallint type.

Character Data Types

CMT converts the basic character types VARCHAR2 and CHAR to CUBRID varchar and char, correspondingly preserving their length. If it is MySQL longtext which has a constant size greater than 1,073,741,823, CMT maps to varchar(1073741823).

If target CUBRID database is specified to access by “UTF-8” charset, varchar or char size would be specified size multi 3. For example, if source MySQL data type is char(255), then the target CUBRID data type is char(765). If target CUBRID database is specified to access by “Unicode” charset or other not “UTF-8” charset, varchar or char size would be specified size multi 2.

Date and Time

CUBRID does not has year data type, so CMT maps year(2|4) to char(4).

Oracle to CUBRID data type mapping

Oracle Type Name	Return value of JDBC	CUBRID Type
Bfile	InputSteam	Blob
Binary_double	Double	Double
Binary_float	Float	Float
Blob	Blob	Blob
Char(n)	String	Character(n)
Decimal(p,s)	BigDecimal	Numeric(p,s)
Float	Float	Float
Interval day to second	String	Character varying(255)
Interval year to month	String	Character varying(255)
Long	String	Clob
Long raw	Byte[]	Bit varying(1073741823) Blob
NChar(n)	String	Character(n)
Nclob	Clob	Character varying(1073741823)
Number(p,s)	getBigDecimal	Numeric(p,s)
Number(,0)	getBigDecimal	Numeric(38,0)
Number	getBigDecimal	Numeric(38,0)
Nvarchar2(n)	String	Character varying(n)
Raw(n)	Byte[]	Bit Varying(n)
Rowid	Rowid	Character varying(64)
Timestamp	Timestamp	Timestamp
Timestamp with local time zone	Timestamp	Timestamp
Timestamp with time zone	Timestamp	Timestamp
Urowid	Rowid	Character varying(64)
Varchar2(n)	String	Character varying(n)

Character Data Types

CMT converts the basic character types VARCHAR2 and CHAR to CUBRID varchar and char, correspondingly preserving their length. If it is Oracle Long type which has a constant size greater than 1,073,741,823, CMT maps to varchar(1073741823).

If target CUBRID database is specified to access by “UTF-8” charset, varchar or char size would be specified size multi 3. For example, if source Oracle data type is char(255), then the target CUBRID data type is char(765). If target CUBRID database is specified to access by “Unicode” charset or other not “UTF-8” charset, varchar or char size would be specified size multi 2.

Date and Time

CUBRID doesn’t have "Timestamp with local time zone" and "Timestamp with time zone" types, so time zone information may be lost.

CUBRID doesn’t have "Interval day to second" and "Interval year to month", so map them to "Character varying(255)"

CUBRID to CUBRID data type mapping

CUBRID Type Name	Return value of JDBC	CUBRID Type
Bigint	BIGINT	Bigint
Bit(n)	BINARY	Bit(n)
Bit varying(n)	VARBINARY	Bit varying(n)
blob	VARBINARY	Blob
Character(n)	CHAR	Character(n)
Character varying(n)	VARCHAR	Character varying(n)
clob	VARCHAR	Clob
Date	DATE	Date
Datetime	TIMESTAMP	Datetime
Double	DOUBLE	Double
Float	REAL	Float
Glo	Object	Glo
Integer	INTEGER	Integer
Monetary	DECIMAL	Monetary
Set_of(data type)	ARRAY	Set_of(data type)
Multiset_of(data type)	ARRAY	Multiset_of(data type)
Sequence_of(data type)	ARRAY	Sequence_of(data type)
National character(n)	CHAR	National character(n)
National character varying(n)	VARCHAR	National character varying(n)
Numeric(p,s)	NUMERIC	Numeric(p,s)
Smallint	SMALLINT	Smallint
Time	TIME	Time
Timestamp	TIMESTAMP	Timestamp

2.13 Database Explorer

The databases’ connection informations were saved into a local configuration file.

Users can view the informations by Database Explorer. The Database Explorer is not opened by default when CMT is started. Users can open it by click main menu "Windows" >> "Database Explorer".

Pop menus in this view:

New database connection

Delete database connection

Refresh database connection?

View database object