Configuration File

An MTK configuration file mainly includes the following content:

{
 "source": {
  },
 "target": {
  },
  "limit": {
  },
  "object": {
  },
  "dataOnly": false,
  "schemaOnly": false,
  "reportFile": "./mtk_report",
  "debug": false
}

• source Database configuration information
• target Database configuration information
• limit Concurrency configuration
• object Object definition
• dataOnly Whether only data is migrated
• schemaOnly Whether only the data structure is migrated
• disableTableDataComp Disabling table data comparison
• disableCollStatistics Disabling collection statistics
• reportFile Migration report directory
• debug debug

Database Configuration Parameters

{
  "type": "oracle",
  "connect": {
   "host": "127.0.0.1",
   "user": "system",
   "port": 1521,
   "password": "oracle",
   "dbName": "orcl"
  },
  "parameter": {
  }
}

• type Database type
• connect Connection information
• parameter Parameter

type

The database type is case-insensitive, which includes:

• MySQL
• Oracle
• Postgres
• openGauss
• MogDB
• DB2
• sqlServer
• file, which is only used in the target database and for exporting data to a file.

connect

{
   "host": "127.0.0.1",
   "user": "system",
   "port": 1521,
   "password": "oracle",
   "dbName": "orcl"
}

Parameter

dropExistingObject

Deletes the existing object. If there is a certain risk, manually delete the existing object.

Default value: false

truncTable

Prevents data conflict when only data is migrated.

Default value: true

caseSensitive

Used for case conversion for the names of the generated objects.

• 0 indicates that no operation is needed.
• 1 indicates lowercase.
• 2 indicates uppercase.
• Others indicates that the names are kept unchanged.

quoteMark

Indicates whether to use double or single quotation marks for an object name.

Default value: false

# Oracle
"a1"
# MySQL
`a1`

path

Indicates the directory of a file to which data is to be exported.

{
  "type": "file",
  "parameter": {
      "fileType": "csv",
      "path": "./data",
      "fileSize": "5000MiB",
      "sqlDBType": "opengauss",
      "csvHeader": true,
      "csvFieldDelimiter": ",",
      "csvOptionallyEnclosed": "\"",
  }
}

fileType

Indicates the type of a file when data is to be exported to a file. The supported file types include:

• csv
• sql

fileSize

Indicates the size of a file when data is to be exported to a file. If this parameter is not configured, the file size is not limited.

The supported formats of the file size includes:

• KiB
• MiB
• GiB
• TiB
• PiB
• KB
• MB
• GB
• TB
• PB

KB == 1000

KiB == 1024

sqlDBType

Indicates the data insert syntax in the target database when data is to be exported to a file.

For example, if data is migrated from Oracle to MySQL, the MySQL data insert syntax is generated by default.

csvHeader

Indicates whether the CSV file header is included when data is to be exported to a CSV file. The CSV header is not exported by default.

Default value: false

csvFieldDelimiter

Indicates the default CSV delimiter.

Default value: ,

csvOptionallyEnclosed

Indicates the data wrapper.

Default value: "

parallelInsert

Indicates the degree of parallelism, which is used for testing the feature and does not need to be configured.

timeFormat

Indicates the time format.

Default value: HH:MI:SS

dateFormat

Indicates the date format.

Default value: YYYY-MM-DD

dateTimeFormat

Indicates the full time format.

Default value: YYYY-MM-DD HH24:MI:SS

noSupportPartTabToNormalTab

• Indicates whether to convert a partition table that is not supported by the target database into a common table.
• Indicates whether to automatically remove the property, of a subpartition table, which is not supported by the target database.

The List and Hash partitioned tables in Oracle are common tables in MogDB.

The composite range-list partitioned table in Oracle is a range partitioned table in MogDB. The subpartition tables will be automatically removed.

mySQLToOgCharExpression

The length of fields in the original database is automatically multiplied by 3. This parameter is valid for only the varchar/char field in the source database of MySQL.

This parameter is already not supported.

mySQLSkipErrorDateTimeData

Automatically skips the wrong time of MySQL.

For example, datetime type: 0000-00-00 13:14:15

autoAddMySQLAutoIncr

Supported after v2.1.0 version

Migrate to MySQL to automatically add MySQL AUTO_INCREMENT column.

If the value is true, when the table does not have a AUTO_INCREMENT primary key column,
automatically add the AUTO_INCREMENT column and change the original primary key to a unique key.

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

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 in conjunction with parameter ignoreTabPartition.

Default: false

Example:

• 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 (
        pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL INVISIBLE, -- add AUTO_INCREMENT PRIMARY KEY
        col1 VARCHAR(10) NOT NULL,
        id BIGINT NOT NULL,
        col2 VARCHAR(10),
        col3 VARCHAR(10),
        UNIQUE KEY ( col1 ) -- primary key to a unique key
    )

autoAddMySQLAutoIncrTabList

[]string

Supported after v2.2.0 version

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

Default: null

Examples:

"autoAddMySQLAutoIncrTabList":[
    "TABLE_1",
    "Schema1.TABLE_1"
]

• "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

ignoreTableDDLCompErr

Indicates whether to ignore the table structure comparison error.

Default value: false

The table structure comparison function is already abandoned.

ignoreDB2PartInclusive

Indicates whether to ignore the INCLUSIVE property of the key value ENDING in the DB2 partition.

Default value: false

For example, in the table creation syntax of the DB2 partition, if ENDING contains the definition of the INCLUSIVE property, the DB2 partition will contain 20180101.

However, the openGauss range partitions do not support the INCLUSIVE but only less than property. In this case, the error "the part DATAMIN is included high value for db2 inclusive option" will occur. You can ignore the error by configuring the ignoreDB2PartInclusive parameter.

CREATE TABLE MTK1.TABLE_TEST_HAOTD
(
    DATADATE VARCHAR(8) NOT NULL,
    DATA1    VARCHAR(10),
    DATA2    VARCHAR(10)
) PARTITION BY RANGE(DATADATE) (
  PART "DATAMIN" STARTING(MINVALUE) ENDING('20180101') INCLUSIVE ,
  PART "P20180101" STARTING('20180101') ENDING('20180102') INCLUSIVE
);

ignoreNotSupportDefault

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 value: false

igNotSupportIntervalPart

Supports ignoring of some unsupported interval partition properties.

Default value: false

ignoreTabPartition

Supports migration to the target database and ignoring of partition syntax. Currently, this parameter supports only migration to MySQL.

Default value: false

For example, the Oracle partitioned table becomes a non-partitioned table after being migrated to MySQL.

Oracle:

CREATE TABLE "MTK"."TAB_PART_LIST" (
  "DEPTNO" NUMBER(10,0) NOT NULL,
  "DEPTNAME" VARCHAR2(20 BYTE),
  "QUARTERLY_SALES" NUMBER(10,2),
  "STATE" VARCHAR2(2 BYTE)
)PARTITION BY LIST ("STATE")
(
  PARTITION "Q1_NORTHWEST" VALUES ('OR', 'WA') TABLESPACE "USERS",
  PARTITION "Q1_SOUTHWEST" VALUES ('AZ', 'CA', 'NM') TABLESPACE "USERS",
  PARTITION "Q1_NORTHEAST" VALUES ('NY', 'VT', 'NJ') TABLESPACE "USERS",
  PARTITION "Q1_SOUTHEAST" VALUES ('FL', 'GA') TABLESPACE "USERS",
  PARTITION "Q1_NORTHCENT" VALUES ('MN', 'WI') TABLESPACE "USERS",
  PARTITION "Q1_SOUTHCENT" VALUES ('OK', 'TX') TABLESPACE "USERS"
)

MySQL:

CREATE TABLE mtk.tab_part_list (
  deptno BIGINT NOT NULL,
  deptname VARCHAR(20),
  quarterly_sales DECIMAL(10,2),
  state VARCHAR(2)
)

autoAddMaxvaluePart

Supported after v0.0.35 version.

The parameter autoAddMaxvaluePart allows to automatically add a maxvalue partition to a partition table where no maxvalue partition exists. For example, DB2 partitioned tables are migrated to openGauss.

DB2 allows defining minvalue partitions, which openGauss does not support.

When DB2 defines the minvalue partition without defining the maxvalue partition, the null value can be inserted into the minvalue partition, and openGauss will report an error.

But openGauss allows null value to be inserted into the maxvalue partition. This parameter can be used to enable automatic addition of maxvalue partitions.

DB2 does not define the maxvalue partition.

CREATE TABLE MTK1.PART_TAB_TEST02 (
    ID INTEGER NOT NULL,
    SALES_PERSON VARCHAR(50),
    REGION VARCHAR(50),
    SALES_DATE DATE
)
PARTITION BY RANGE(SALES_DATE)
(
    PART PJAN STARTING('2017-01-01') ENDING('2017-03-31') INCLUSIVE IN USERSPACE1,
    PART PFEB STARTING('2017-04-01') ENDING('2017-07-31') INCLUSIVE IN USERSPACE1,
    PART PMAR STARTING('2017-08-01') ENDING('2017-12-31') INCLUSIVE IN USERSPACE1,
    PART PAPR STARTING('2018-01-01') ENDING('2018-12-31') INCLUSIVE IN USERSPACE1
) ORGANIZE BY ROW

openGauss

CREATE TABLE DB2_MTK.PART_TAB_TEST02 (
    ID INTEGER NOT NULL,
    SALES_PERSON VARCHAR(50),
    REGION VARCHAR(50),
    SALES_DATE DATE
) PARTITION BY RANGE (SALES_DATE)
(
    PARTITION PJAN VALUES LESS THAN('2017-03-31'),
    PARTITION PFEB VALUES LESS THAN('2017-07-31'),
    PARTITION PMAR VALUES LESS THAN('2017-12-31'),
    PARTITION PAPR VALUES LESS THAN('2018-12-31'),
    PARTITION PART_MAXVALUE VALUES LESS THAN(MAXVALUE)
)

colKeyWords

Indicates the keyword of a column name.

During creation in the target database, "" or '' will be automatically added and the keyword will be operated with case conversion automatically.

Format: Column name : Case identification

• 1 indicates lowercase.
• 2 indicates uppercase.
• Others indicate that the keyword is kept unchanged.

"colKeyWords": {
  "STREAM": 1,
  "TID": 1
}

objKeyWords

Reserves the object keyword.

During creation in the target database, "" or '' will be automatically added and the keyword will be operated with case conversion automatically.

Format: Column name : Case identification

• 1 indicates lowercase.
• 2 indicates uppercase.
• Others indicate that the keyword is kept unchanged.

"objKeyWords": {
  "STREAM": 1,
  "TID": 1
}

virtualColToNormalCol

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

Default value: false

virtualColConv

Indicates the virtual column expression conversion function.

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

replaceZeroDate

openGauss does not support the time format 0000-00-00, which is replaced with 1970-01-01 by default. This parameter can be used for defining the default value with which 0000-00-00 is replaced, such as 2021-01-01.

remapTable

This parameter is already not supported.

Indicates whether to modify the table name during migration.

remapSchema

Indicates whether to modify the name of schema during migration.

Format: Name of the original solution : Name of the new solution

For example, MTK1 is changed to MTK1_NEW, and SOE is changed to SOE_NEW.

"remapSchema":{
  "MTK1":"MTK1_NEW",
  "SOE":"SOE_NEW"
}

remapTablespace

Indicates whether to modify the name of the tablespace during migration.

This parameter is used together with enableSyncTabTbsPro.

Format: Name of the original tablespace : Name of the new tablespace

For example, USERSPACE1 is changed to TBS01.

"remapTablespace":{
  "USERSPACE1":"TBS01",
}

enableSyncTabTbsPro

Indicates whether to generate a DDL with the tablespace.

Default value: false

enableSyncCompTabPro

Indicates whether to generate a DDL with the table compression.

Default value: false

excludeSysTable

Excludes the system object definition. If this parameter is not configured, the default value applies.

• DB2

  • "EXPLAIN_ACTUALS",
  • "ADVISE_TABLE",
  • "ADVISE_PARTITION",
  • "ADVISE_MQT",
  • "ADVISE_WORKLOAD",
  • "ADVISE_INDEX",
  • "ADVISE_INSTANCE",
  • "OBJECT_METRICS",
  • "EXPLAIN_DIAGNOSTIC_DATA",
  • "EXPLAIN_DIAGNOSTIC",
  • "EXPLAIN_STREAM",
  • "EXPLAIN_PREDICATE",
  • "EXPLAIN_OPERATOR",
  • "EXPLAIN_OBJECT",
  • "EXPLAIN_ARGUMENT",
  • "EXPLAIN_STATEMENT",
  • "EXPLAIN_INSTANCE",

enableOgBlobClob

Supported after v2.0.0 version.

Migrate other databases 'blob', 'blob' to openGaus 'blob', 'Clob'..

Deafult convert to openGaus bytea、text

Default value: false

enableConvertSrid

Supported after v2.0.0 version.

This parameter allows you to control the automatically conversion of Oracle SRID to standard EPSG.

If the value is true, all SRID will be forced to defaultSrid .

Default value: false

defaultSrid

Supported after v2.0.0 version.

Use this parameter to override the default EPSG SRID to used: 4326. Can be overwritten by enableConvertSrid, see enableConvertSrid.

Default value: 4326

skipColumnType

Supported after v2.1.3 version.

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

Format: "column type name": int

int

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

Examples:

"skipColumnType":{
  "blob": 1,
  "clob": 2
}

skipColumnName

Supported after v2.1.3 version.

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

Priority over skipColumnType

Format: "schema.table_name.column": int

int

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

Examples:

"skipColumnName":{
  "schema1.tab_01.col1": 1,
  "schema1.tab_01.col2": 2
}

templateSeqName

Supported after v2.1.5 version.

MySQL automatically increments columns convert sequences, templates for sequence names

Default: SEQ_{{.TabName}}_{{.ColName}}

Only the following two variables are supported

• {{.TabName}} 表名
• {{.ColName}} 列名

Examples:

"templateSeqName": "{{.TabName}}_{{.ColName}}_SEQ"

limit

"limit": {
  "parallel": 2,
  "fetchSize": 1000,
  "batchSize": 1000,
  "bufferSize": 4
  "cpBufferSize": 8,
  "oracleSelectParallel": 2,
}

parallel

Indicates the degree of parallelism. This parameter is used only when table data is migrated. It will be later used during index creation.

fetchSize

Indicates the fetch size (number of rows to be fetched from a result set every time)

This parameter can be used when a table contains several millions of rows and you need to avoid memory insufficiency.

batchSize

Indicates the batch size in batch insert.

The default value is 1000. If memory insufficiency occurs, lower the value.

cpBufferSize

Indicates the size of data that can be submitted at a time for a database supporting the COPY function.

oracleSelectParallel

Adds hint /* +parallel(t,n) */ to an Oracle query statement.

object

"object": {
  "tables": [],
  "schemas": [
   "A!"
  ],
  "excludeTable": {
   "MTK": [
    "MTK_TAB1",
    "MTK_TAB2"
   ],
   "SYS": [
    "TAB$",
    "OBJ$"
   ]
  },
  "tableSplit": {
   "MTK": {
    "TAB_1": [
     " rowid between 'AAAeoSAAEAAAACpAAA' and 'AAAeoSAAEAAAA4oEI/'",
     " rowid between 'AAAeoSAAEAAAA4pAAA' and 'AAAeoSAAEAABVSoEI/'",
     " rowid between 'AAAeoSAAEAABVSpAAA' and 'AAAeoSAAFAABOEoEI/'",
     " rowid between 'AAAeoSAAFAABOEpAAA' and 'AAAeoSAAFAABOGIEI/'"
    ],
    "TAB_2": [
     "MOD(\"ID\",4)=0",
     "MOD(\"ID\",4)=1",
     "MOD(\"ID\",4)=2",
     "MOD(\"ID\",4)=3"
    ],
    "TAB_3": [
     "MOD(`ID`,4)=0",
     "MOD(`ID`,4)=1",
     "MOD(`ID`,,4)=2",
     "MOD(`ID`,4)=3"
    ]
   }
  }
 }

Defines migration objects.

• Case-sensitive
• Migration user, database, and schema
• schema = mysql database
• schema = oracle user
• schema = postgres schema
• schema = db2 schema

Parameters schemas and tables are mutually exclusive. If both parameters are configured, the error "mtk-1002 schema and table cannot exist together" will occur during migration.

schema

Defines schema during migration.

"schemas": [
  "SOE",
  "BT_X",
  "CUST_X"
]

tables

Defines which tables are to be migrated, including

• Full table name
• Support for schema.*
• Support for schema.TAB%

"tables": [
  "MTK.TAB1"
  "MTK.TAB111%"
  "MTK1.*"
],

when running a subcommand migration, such as sync-function and configured with tables (MTK.XXX) is the 'XXX' function under Migrating Schema 'MTK'. Except for sequence.

excludeTable

Defines which tables are excluded.

"excludeTable":{
  "MTK":[
    "TABLE_SKIP1",
    "TABLE_SKIP2"
  ]
}

tableSplit

• Defines table granularity parallelism, which can be automatically generated by using mtk show-table-split.
• Defines table migration query conditions.

"tableSplit":{
  "MTK":{
    "TABLE_4":[
      "ID < 10000 ",
      "ID < 20000 AND ID >=10000",
      "ID < 30000 AND ID >=20000",
      "ID < 40000 AND ID >=30000",
      "ID < 50000 AND ID >=40000",
      "ID < 60000 AND ID >=50000",
      "ID < 70000 AND ID >=60000",
      "ID < 80000 AND ID >=70000",
      "ID < 90000 AND ID >=80000",
      "ID >= 90000"
    ]
  }
}

dataOnly

Indicates whether to migrate only data.

Default value: false

schemaOnly

Indicates whether to migration only the data structure.

Default value: false

disableTableDataComp

Disables the table data comparison function.

After data migration is complete, MTK will count and compare the number of rows in the source and target databases.

Default value: false

disableCollStatistics

Disables the collection statistics function.

After data migration is complete, MTK will collect statistics information from the target database.

Default value: false

reportFile

Indicates the migration report directory.

For MTK of a version earlier than v0.0.18, the migration report is shown in HTML.

For MTK v0.0.18 or later, the migration report is stored in a directory.

debug

Indicates whether to enable the log debug mode.

Default value: false

disableSelectPart

Disable the partition table migration data by partition feature.

默认值：false