MogDB
Ecological Tools

Overview of MTK

MTK (Database Migration Toolkit) is a tool used for quickly importing the data structure and full data of Oracle/DB2/MySQL/openGauss into MogDB. MTK of the latest version also supports the compatibility of rewriting and importing stored procedures, functions, triggers and other program segments in Oracle database.

Support for Multiple Database Types

  • Data migration between Oracle, DB2, openGauss, SQL Server, and MySQL is supported. These databases act as the source or target database of each other.
  • Database content can be exported as an executable SQL script.

Migration Performance Adjustment

  • During data migration, such parameters as batch query and batch insert size can be modified to adjust the data migration performance.
  • During data migration, multiple concurrency, parallelism, and data fragmentation are supported.

Structures Separated from Data

  • Both the structure and data of the migration object can be migrated simultaneously. Additionally, migrating only the structure or only the data is also supported. Generally, data migration is later than structure migration.
  • The migration range can be specified, such as table-level or schema-level migration. You can specify whether to migrate all objects or some objects under a schema.
  • Schema remapping is supported during the migration. In other word, objects under the source schema can be migrated to a different schema in the target database.

Procedure Migration from Oracle/MySQL to openGauss

Storage procedures, functions, triggers, and packages can be migrated from Oracle/MySQL to openGauss.

According to the openGauss syntax rules, Oracle/MySQL procedures are automatically rewritten and then created in the target openGauss database.


Supported Database Objects

For details, run the show-type command.

Name Remarks Supported or not
Schema Schema Oracle Users
MySQL Database
DB2 Schema
SqlServer Database Schema
ObjectType Type Supported
Domain Domain Not supported
CustomType Not supported
Sequence Sequence Supported
Queue Queue Not supported
Table Table OBJECT TYPE TABLE is not included.
TableData TableData Supported
Index Index Supported
Constraint Constraint Supported
View View Supported
Trigger Trigger Supported
Function Function Supported
Procedure Storage procedure Supported
Package Package Supported
DBLink Database connection Support only the query function.
Rule Rule supported
Synonym Synonym Supported
TableDataCom Comparison of table row count Supported
AlterSequence Synchronizing the last value of a sequence Supported
CollStatistics Collection of statistics Supported

Precautions

  • '' and null

    • Oracle '' = null
    • DB2 '' = null
    • MySQL '' != Null
    • PostgreSQL '' != Null
    • openGauss

      • Compatibility type A: '' = null
      • Compatibility type PG: '' != null
  • Time zone problem

    • Make sure that the migration server is in the same time zone as the application server.
    • Make sure that the database nodes are in the same time zone.
  • CharSet

Storage Procedure Conversion

MTK supports the following storage procedure conversion.

  • Type conversion of input and output parameters
  • Function overloading
  • "FROM dual" is removed.
  • Syntax conversion

    Before Conversion After Conversion
    connect by CTE rewriting
    EXECUTE IMMEDIATE EXECUTE
    Partial syntax for EXIT WHEN NOT FOUND
    interval syntax
    mod syntax: counter mod 1000 = 0 mod(counter,4) == 0
    null related syntax: !=|<> null is not null
    PIPE ROW RETURN NEXT
    select 1,2 into select 1,2 into STRICT
    select unique SELECT DISTINCT
    sequence nextval/currval nextval(xxx)
    SQLCODE SQLSTATE
    Partial syntax for truncate table
  • Function conversion

    Before conversion After conversion
    ADD_YEARS
    DBMS_OUTPUT.PUT_LINE RAISE NOTICE
    DBMS_LOB.GETLENGTH octet_length
    DBMS_LOB.SUBSTR substr
    DBMS_STANDARD.RAISE EXCEPTION
    decode case when
    empty_blob/empty_clob null
    from_tz
    listagg string_agg
    months_between
    NUMTODSINTERVAL
    NUMTOYMINTERVAL
    nvl2 case when
    raise_application_error RAISE EXCEPTION
    SYS_CONTEXT
    SYSTIMESTAMP CURRENT_TIMESTAMP
    TO_CHAR ::varchar
    to_char(l_curr_time, 'TZH') EXTRACT( timezone_hour from l_curr_time)
    to_char(l_curr_time, 'TZM') EXTRACT( timezone_minute from l_curr_time)
    TO_CLOB ""
    TO_TIMESTAMP_TZ
    trunc date_trunc
    UTL_ROW.CAST_TO_RAW encode