Adaptation for Migrating from MySQL to DM8

Driver Download and DEM Tool Usage Guide

1. Download the DM driver from the following link and replace the MySQL JDBC driver mysql-connector-java with the DM8 driver DmJdbcDriver18.
2. Use the DEM tool to migrate data.

   For details, see the official DM data migration document and Migrating from MySQL to DM.

DM Database Compatibility Settings

Before migrating data, set the compatibility parameters to be compatible with the MySQL database:

• Set COMPATIBLE_MODE=4 in the dm.ini configuration file specified by the instance.
• Use the statement ALTER SYSTEM SET 'COMPATIBLE_MODE'=4 SPFILE; for modification, and then restart the database service for the modification to take effect.

For details, see the compatibility parameter settings in Preparations in the DM migration document.

DM Database Character Encoding Settings

In MySQL, the default character-encoding format for table creation is UTF8_GENERAL_CI (case insensitive). Therefore, you are advised to set case insensitive for DM when migrating data from MySQL to DM. Refer to the following measures:

• When initializing an instance, set CASE_SENSITIVE=0 (case insensitive). Note that this setting cannot be modified once it is set.
• If CASE_SENSITIVE=1 (case sensitive) is set during instance initialization, deselect "Retain Object Name Case" when using DEM for migration.

For details, see:

• Initialization parameters: section 3 in Preparations.
• FAQs: How to keep the case of table names unchanged after migrating from MySQL to DM and How to remove double quotation marks from MyBatis objects.

Chinese Character Storage Settings

In MySQL 5.0.3 and later versions, the VARCHAR type is stored by character. VARCHAR(1) stores a Chinese character. In DM, the VARCHAR type is stored by byte by default. VARCHAR(3) stores a Chinese character. During the migration from MySQL 5.0.3 (or later) to DM, character truncation may occur. If a table contains Chinese characters, refer to the following measures:

• Increase the field length. For example, if the field length in MySQL is 10, the field length in DM table needs to be increased to 30 during the table creation.
• Reinitialize the instance and set the length of the VARCHAR type object to characters (LENGTH_IN_CHAR=1).

For details, see the FAQs: Column [NAMES] exceeds the limit during migration from MySQL to DM and Incomplete VARCHAR field and garbled characters contained during migration MySQL to DM8.

Settings of System Reserved Words

If "Syntax analysis error" is reported when creating an object such as a table in DM, the possible cause is that the object name is a system reserved word. Run the SQL statement SELECT * FROM V$RESERVED_WORDS WHERE RESERVED='Y' AND KEYWORD=Object_name; to check whether the object name is a reserved word. If it is, refer to the following measures:

• Use double quotation marks to enclose the object name and use quotation marks during query.
• (Recommended) Configure the dm_svc.conf file on the client. In the corresponding database service configuration section, add the KEYWORDS option to mask keywords: KEYWORDS=Object_name.
• Modify the SQL statements and object names related to system keywords.

For details, see Masking reserved words and Different results of the same SQL statement on two PCs.

Failure to Set the Data Precision in DM

The INT type does not allow setting the data precision in DM. Check whether precision has been set for the INT type in the field definition in the table. If yes, remove the precision, for example, change create table test1(v1 int(10),v2 varchar(20)); to create table test1(v1 int,v2 varchar(20));.

TIMESTAMP Settings

In MySQL, the default setting for the TIMESTAMP type is "0000-00-00 00:00:00." In DM, the setting must range from "0001-01-01 00:00:00.000000" to "9999-12-31 23:59:59.999999." If the error message is reported indicating that the TIMESTAMP type is "0000-00-00 00:00:00" during the migration, change it to a valid date type.

For details, see Is there a tool for converting MySQL syntax to DM syntax and Timestamp error during migration from MySQL to DM.

Single and Double Quotation Marks in DM

In DM, the usages of single and double quotation marks are different. Double quotation marks are used for keywords, object names, field names, and aliases. Single quotation marks are used for character string constants. For the DEFAULT setting of a field, the default character (or character string) value must be enclosed in single quotation marks, for example, char(10) default 'abc'.