MTK Parameter

Parameter

autoAddMySQLAutoIncr

Type: bool

Desc: Migrate to MySQL to automatically add MySQL AUTO_INCREMENT column.

Before v2.8.0, the parameter is true, when there is no auto-increment column in the original table, the AUTO_INCREMENT column is automatically added, and the new column is set as the primary key, and the original primary key is changed to a unique key.

After v2.8.0, the parameter is true, when there is no auto-increment column in the original table, the AUTO_INCREMENT column is automatically added, and the new column is set as a UNIQUE key, and the original primary key remains unchanged.

After v2.9.6, the parameter is true

When there is no auto-increment column in the original table
- There is PK_ID column in the original table, it is not being processed
- Automatically add the PK_ID AUTO_INCREMENT column, and set PK_ID as primary key, and the original primary key changed to unique key.
When the original table has an auto-increment column
- The first column of the source primary key remains unchanged as the auto-increment column
- The first column of the source unique key becomes the auto-increment column and becomes the primary key, and the primary key changed to unique key.
- The source table has an auto-increment single column index that becomes the primary key, and the primary key changed to unique key.
- There is no need to add new primary key in any of the above, the source primary key changed to unique key, and everything else remains unchanged.

PK_ID BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL [INVISIBLE]

Prior to MySQL 8.0.23 column are visible
MySQL 8.0.23 column are invisible column
There is PK_ID column in the original table, it is not being processed

MySQL supports invisible columns as of MySQL 8.0.23.
An invisible column is normally hidden to queries, but can be accessed if explicitly referenced.
Prior to MySQL 8.0.23, all columns are visible.

Used with ignoreTabPartition parameter.

Mig DB2 to MySQL

DB2

CREATE TABLE MTK1.TAB_PK_TEST (
    COL1 VARCHAR(10) NOT NULL,
    ID BIGINT NOT NULL,
    COL2 VARCHAR(10),
    COL3 VARCHAR(10)
) IN USERSPACE1 ORGANIZE BY ROW

MySQL

CREATE TABLE db2_mtk.tab_pk_test (
    -- auto increment primary key column
    pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL INVISIBLE,
    col1 VARCHAR(10) NOT NULL,
    id BIGINT NOT NULL,
    col2 VARCHAR(10),
    col3 VARCHAR(10),
    UNIQUE KEY ( col1 )
)

Default: false

Add: v2.1.1

autoAddMySQLAutoIncrTabList

Type: []string

Desc: Used with the AutoAddMySQLAutoIncr parameter.

Configure which tables automatically increment the auto_incr column. Ignored if the table already has a self-incrementing column. The default is empty to represent all tables.

Parameters are not case-sensitive

"TABLE_1" Allows all tables in the object named TABLE_1 to add auto_incr column
"Schema1.TABLE_1" Allows the object to add auto_incr column to TABLE_1 tables under Schema1

Default: null

Example:

autoAddMySQLAutoIncrTabList Example

{
  "autoAddMySQLAutoIncrTabList": [
    "TABLE_1",
    "SCHEMA1.TABLE_1"
  ]
}

Add: v2.2.1

ignoreNotSupportDefault

Type: bool

Desc: Supports ignoring of some default values of some unsupported columns. For example, the sys_guid. MTK of Oracle is embedded with a whitelist that will be ignored.

Default: false

replaceZeroDate

Type: string

Desc: openGauss/MogDB/PostgreSQL time format does not support 0000-00-00. Configuring this parameter will perform corresponding replacement. The replacement range is as follows:

Column default value
SQL statements in stored procedures/functions

When find a "zero" date: 0000-00-00 00:00:00 it is replaced by a NULL.

This could be a problem if your column is defined with NOT NULL constraint. If you can not remove the constraint,

use this directive to set an arbitral date that will be used instead.

You can also use -INFINITY if you don't want to use a fake date.

Example:

Replace Zero Date Example

{
  "replaceZeroDate": "1970-01-01 00:00:00"
}

virtualColToNormalCol

Type: bool

Desc: Indicates whether to convert virtual columns in the source database into normal columns in the target database.

Default: false

Option:

true
false

virtualColConv

Type: map[string]string

Desc: Indicates the virtual column expression conversion function.

Example:

virtualColConv example

{
  "virtualColConv": {
    "LEFT(HOST,POSSTR(HOST,':')-1)": "SPLIT_PART(HOST,':',1)",
    "TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'"
  }
}

mySQLToOgCharExpression

Type: string

Desc: MySQL migrated to openGauss to calculate the column length expression for char/varchar columns. default, it is not calculated. For example, * 3 means that the original length is *3

Deprecated: mtk scales up the length based on the db type,column type, db compatibility mode

convertOracleIntegerToNumeric

Type: bool

Desc: Convert Oracle Integer type to NUMERIC. Support openGauss/MogDB/PostgreSQL

Default: false

Option:

true
false

enableOgBlobClob

Type: bool

Desc: Migrate other databases Blob、Clob to openGauss Blob、Clob.

Default: false

Option:

true
false

skipColumnType

Type: map[string]int

Desc: This parameter allows you to control skip a data type column during migration.

Format: "column type name": option

1 Do not create columns and do not migrate data
2 Create columns but do not migrate data

Option:

Example:

Skip ColumnType Example

{
  "skipColumnType": {
    "COL_TYPE_1": 1,
    "COL_TYPE_2": 2
  }
}

skipColumnName

Type: map[string]int

Desc: This parameter allows you to control skip a table column name during migration.

Priority over skipColumnType

Format "schema.table_name.column": option

1 Do not create columns and do not migrate data
2 Create columns but do not migrate data

Option:

Example:

Skip ColumnName Example

{
  "skipColumnName": {
    "SCHEMA1.TAB_01.COL1": 1,
    "SCHEMA1.TAB_01.COL2": 2
  }
}

charLengthChangeExclude

Type: []string

Desc: When migrating across character sets, specify an expression for the CHAR type, match column names without length expansion. Be careful to ignore capitalization issues.

^update_time* ignore column name starts with update_time
^update_time ignore column name for update_time
(?i)^update_time$ ignore column name for update_time.

Example:

Char Length Change Exclude Example

{
  "charLengthChangeExclude": [
    "^update_time*",
    "^visit_date$",
    "(?i)^visit_date$"
  ]
}

Add: v2.5.2

enableCharTrimRightSpace

Type: bool

Desc: For char migration to varchar, it is allowed to intercept the space on the right side of the char type. Only supports openGauss/MogDB on the target side

Default: false

Option:

true
false

Add: v2.7.1

customColMap

Type: map[string]SQLType

Desc: Custom column type mapping. only Support openGauss/MogDB

Example:

Custom Column Type Map

{
  "customColMap": {
    "col_type_char": {
      "defaultLength2": 0,
      "defaultLength": 0,
      "name": "col_type_varchar"
    }
  }
}

Add: v2.9.3