DB2迁移MySQL工具，DB2迁移KingBase(人大金仓数据库)工具

honey_pie

墨衍会员 · AI 创作全网分发码龄9年

关注

原创 2022-06-13 02:06:28 1.1k 阅读 AI 写同款· GEO 优化 ›

db2迁移到mysql方案 db2迁移mysql遇到的问题和解决阅读详情

编写SQL查询数据字典，生成DDL

输入schema名称，执行SQL会生成该schema下所有表的DDL
迁移数据库是比较繁琐的事，会出现各种问题和差错，需要开发人员熟悉两边数据库的数据类型和语法，以下代码仅供参考，如有不满足需求的地方，请手动调整和完善。

DB2迁移MySQL

-- 生成表字段、主键、索引
WITH param(TABSCHEMA) AS (
    SELECT 'TEST'     -- 输入schema名称，执行SQL会生成该schema下所有表的DDL
    FROM SYSIBM.DUAL
),
     types(DB2_TYPE, MYSQL_TYPE, HAS_LENGTH, HAS_SCALE, HAS_COLLATE) AS (
--  这里只录入了部分类型的对应关系，需要其他类型请自行补充
--  DB2_TYPE、 MYSQL_TYPE 是两个数据库对应的数据类型
--  某些MySQL数据类型没有长度属性，比如 INT、DOUBLE，它们的 HAS_LENGTH 和 HAS_SCALE 都是 FALSE
--  某些MySQL数据类型只有长度属性，比如 CHAR(n)、VARCHAR(n)，它们的 HAS_LENGTH 和 HAS_SCALE 分别是 TRUE、FALSE
--  某些MySQL数据类型有长度属性和标度属性，比如 DECIMAL(m, n)，它们的 HAS_LENGTH 和 HAS_SCALE 都是 TRUE
         VALUES ('BIGINT', 'BIGINT', FALSE, FALSE, FALSE),
                ('BLOB', 'LONGBLOB', FALSE, FALSE, FALSE),
                ('CHARACTER', 'CHAR', TRUE, FALSE, TRUE),
                ('CLOB', 'LONGTEXT', FALSE, FALSE, TRUE),
                ('DECIMAL', 'DECIMAL', TRUE, TRUE, FALSE),
                ('DOUBLE', 'DOUBLE', FALSE, FALSE, FALSE),
                ('INTEGER', 'INT', FALSE, FALSE, FALSE),
                ('SMALLINT', 'SMALLINT', FALSE, FALSE, FALSE),
                ('TIMESTAMP', 'DATETIME', TRUE, FALSE, FALSE),
                ('VARCHAR', 'VARCHAR', TRUE, FALSE, TRUE)
     )
SELECT 'CREATE TABLE `' || param.TABSCHEMA || '`.`' || TABNAME || '`(' || CHR(13) || CHR(10) || (
    SELECT LISTAGG('  `' ||
                   COLNAME || '` ' || NVL(MYSQL_TYPE, 'ERR_TYPE') ||
                   DECODE(HAS_LENGTH, TRUE, '(' || DECODE(columns.TYPENAME, 'TIMESTAMP', 6, LENGTH) || DECODE(HAS_SCALE, TRUE, ', ' || SCALE, '') || ') ', '') ||
                   DECODE(HAS_COLLATE, TRUE, ' COLLATE utf8mb4_general_ci', '') ||
                   DECODE(NULLS, 'N', ' NOT NULL', '') ||
                   NVL2(DEFAULT, ' DEFAULT ' || DEFAULT, DECODE(NULLS, 'Y', ' DEFAULT NULL', '')) ||
                   NVL2(columns.REMARKS, ' COMMENT ''' || columns.REMARKS || '''', ''),
                   ',' || CHR(13)||CHR(10))
                   WITHIN GROUP (ORDER BY COLNO)
    FROM SYSCAT.COLUMNS columns
             LEFT JOIN types
                       ON types.DB2_TYPE = columns.TYPENAME
    WHERE columns.TABNAME = tables.TABNAME
      AND columns.TABSCHEMA = tables.TABSCHEMA
) || (SELECT NVL(LISTAGG((
                 SELECT ',' || CHR(13) || CHR(10) ||
                        '  PRIMARY KEY (' ||
                        LISTAGG('`' || keycoluse.COLNAME || '`', ', ') WITHIN GROUP (ORDER BY keycoluse.COLSEQ) ||
                        ') USING BTREE'
                 FROM SYSCAT.KEYCOLUSE keycoluse
                 WHERE keycoluse.TABSCHEMA = tabconst.TABSCHEMA
                   AND keycoluse.TABNAME = tabconst.TABNAME
                   AND keycoluse.CONSTNAME = tabconst.CONSTNAME
             )) WITHIN GROUP (ORDER BY tabconst.CONSTNAME), '')
      FROM SYSCAT.TABCONST tabconst
      WHERE tabconst.TABSCHEMA = tables.TABSCHEMA
        AND tabconst.TABNAME = tables.TABNAME
        AND tabconst.TYPE = 'P'
) || (SELECT NVL(LISTAGG((
                 SELECT ',' || CHR(13) || CHR(10) || '  ' ||
                        DECODE(UNIQUERULE, 'U', 'UNIQUE ', '') || 'INDEX `' ||
                        indexes.INDNAME || '`(' ||
                        LISTAGG('`' || COLNAME || '`' || DECODE(COLORDER, 'A', 'ASC', 'D', 'DESC', ''), ', ') WITHIN GROUP (ORDER BY COLSEQ) ||
                        ') USING BTREE'
                 FROM SYSCAT.INDEXCOLUSE indexcoluse
                 WHERE indexcoluse.INDSCHEMA = indexes.INDSCHEMA
                   AND indexcoluse.INDNAME = indexes.INDNAME
             )) WITHIN GROUP (ORDER BY indexes.INDNAME), '')
      FROM SYSCAT.INDEXES indexes
      WHERE indexes.TABSCHEMA = tables.TABSCHEMA
        AND indexes.TABNAME = tables.TABNAME
        AND indexes.UNIQUERULE != 'P'
) ||
       CHR(13) || CHR(10) || ') ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic' ||
       NVL2(REMARKS, ' COMMENT = ''' || tables.REMARKS || ''';', ';')
FROM SYSCAT.TABLES tables,param
WHERE tables.TABSCHEMA = param.TABSCHEMA
-- 如果只想生成 T_USER 表的DDL，就使用下面的条件
--   AND TABNAME = 'T_USER'
  AND TYPE = 'T'
ORDER BY TABNAME;

-- 生成外键、约束键、序列、同义词
-- todo

DB2迁移KingBase

--统计schema中所有的数据类型
SELECT DISTINCT columns.TYPENAME
FROM (SELECT TABLEID, TABNAME
      FROM SYSCAT.TABLES
      WHERE TABSCHEMA = 'TEST'
        AND TYPE = 'T'
      ORDER BY TABLEID) tables,
     SYSCAT.COLUMNS columns
WHERE tables.TABNAME = columns.TABNAME;



--表结构同步
WITH param AS (
    SELECT 'TEST' TABSCHEMA, 'SYS_DEFAULT' TBSPACE, 'SYS_DEFAULT' INDEX_TBSPACE FROM SYSIBM.DUAL
),
     types(DB2_TYPE, KB_TYPE, HAS_LENGTH, HAS_SCALE, LENGTH_UNIT, CAST_DEFAULT_VALUE) AS (
         VALUES ('BIGINT', 'BIGINT', 0, 0, '', ''),
                ('BLOB', 'BLOB', 0, 0, '', ''),
                ('CLOB', 'CLOB', 0, 0, '', ''),
                ('DATE', 'DATE', 0, 0, '', ''),--'::DATE'
                ('DECIMAL', 'NUMERIC', 1, 1, '', ''),
                ('DOUBLE', 'FLOAT8', 0, 0, '', ''),
                ('INTEGER', 'INTEGER', 0, 0, '', ''),
                ('SMALLINT', 'SMALLINT', 0, 0, '', ''),
                ('TIMESTAMP', 'TIMESTAMP', 0, 0, '', ''),
                ('VARCHAR', 'CHARACTER VARYING', 1, 0, 'CHAR', ''),
                ('CHARACTER', 'CHAR', 1, 0, 'CHAR', ''),
                ('LONG VARCHAR', 'CHARACTER VARYING', 1, 0, 'CHAR', '')
     ),
     defaultValue(DB2_VALUE, KB_VALUE) AS (
         VALUES ('CURRENT TIMESTAMP', 'NOW()')
     )
SELECT 'CREATE TABLE ' || param.TABSCHEMA || '.' || TABNAME || '(' || (
    SELECT LISTAGG('"' || COLNAME || '" ' || KB_TYPE || DECODE(HAS_LENGTH, 0, '', 1, '(' || LENGTH || DECODE(HAS_SCALE, 0, '', 1, ', ' || SCALE) || ' ' || LENGTH_UNIT || ')') || DECODE(NULLS, 'N', ' NOT NULL', '') || NVL2(DEFAULT, ' DEFAULT ' || NVL(KB_VALUE, DEFAULT) || CAST_DEFAULT_VALUE, ''), ',') WITHIN GROUP (ORDER BY COLNO)
    FROM SYSCAT.COLUMNS columns LEFT JOIN defaultValue ON DEFAULT = DB2_VALUE,types
    WHERE columns.TABNAME = tables.TABNAME
      AND columns.TABSCHEMA = tables.TABSCHEMA
      AND columns.TYPENAME = types.DB2_TYPE
) || ') TABLESPACE ' || param.TBSPACE || ';' ||
       NVL2(REMARKS, 'COMMENT ON TABLE ' || param.TABSCHEMA || '.' || TABNAME || ' IS ''' || REMARKS || ''';', '') ||
       (SELECT LISTAGG(CAST(NVL2(columns.REMARKS, 'COMMENT ON COLUMN ' || param.TABSCHEMA || '.' || TABNAME || '."' || COLNAME || '" IS ''' || columns.REMARKS || ''';', '') AS VARCHAR(10000)),'') WITHIN GROUP (ORDER BY COLNO)
        FROM SYSCAT.COLUMNS columns
        WHERE columns.TABNAME = tables.TABNAME
          AND columns.TABSCHEMA = tables.TABSCHEMA)
FROM SYSCAT.TABLES tables,param
WHERE tables.TABSCHEMA = param.TABSCHEMA
  AND TYPE = 'T'
ORDER BY TABLEID;

--主键、唯一键同步
WITH param AS (
    SELECT 'TEST' TABSCHEMA, 'SYS_DEFAULT' TBSPACE, 'SYS_DEFAULT' INDEX_TBSPACE FROM SYSIBM.DUAL
)
SELECT (SELECT 'ALTER TABLE ' || param.TABSCHEMA || '.' || tabconst.TABNAME || ' ADD CONSTRAINT ' ||
               tabconst.CONSTNAME || DECODE(tabconst.TYPE, 'P', ' PRIMARY KEY (', 'U', ' UNIQUE (') ||
               LISTAGG('"' || keycoluse.COLNAME || '"', ', ') WITHIN GROUP (ORDER BY keycoluse.COLSEQ) || ');'
        FROM SYSCAT.KEYCOLUSE keycoluse
        WHERE keycoluse.TABSCHEMA = param.TABSCHEMA
          AND keycoluse.TABNAME = tabconst.TABNAME
          AND keycoluse.CONSTNAME = tabconst.CONSTNAME)
FROM SYSCAT.TABCONST tabconst,param
WHERE tabconst.TABSCHEMA = param.TABSCHEMA
  AND TYPE IN ('P', 'U');

--索引同步
WITH param AS (
    SELECT 'TEST' TABSCHEMA, 'SYS_DEFAULT' TBSPACE, 'SYS_DEFAULT' INDEX_TBSPACE FROM SYSIBM.DUAL
),
     unique_type(CODE, TYPE) AS (
         VALUES ('D', ''),
                ('U', 'UNIQUE'),
                ('P', 'UNIQUE')
     ),
     colorder_type(CODE, TYPE) AS (
         VALUES ('A', 'ASC'),
                ('D', 'DESC'),
                ('I', ''),
                ('R', '')
     )
SELECT 'CREATE ' || unique_type.TYPE || ' INDEX ' || INDNAME || ' ON ' || param.TABSCHEMA || '.' || TABNAME || ' (' ||
       (SELECT LISTAGG('"' || COLNAME || '" ' || colorder_type.TYPE, ', ') WITHIN GROUP (ORDER BY COLSEQ) FROM SYSCAT.INDEXCOLUSE indexcoluse LEFT JOIN colorder_type ON COLORDER = colorder_type.CODE WHERE indexcoluse.INDSCHEMA = indexes.INDSCHEMA AND indexcoluse.INDNAME = indexes.INDNAME) ||
       ') TABLESPACE ' || param.INDEX_TBSPACE || ';'
FROM SYSCAT.INDEXES indexes LEFT JOIN unique_type ON UNIQUERULE = unique_type.CODE, param
WHERE indexes.TABSCHEMA = param.TABSCHEMA AND indexes.UNIQUERULE != 'P';

--外键同步
WITH param AS (
    SELECT 'TEST' TABSCHEMA, 'SYS_DEFAULT' TBSPACE, 'SYS_DEFAULT' INDEX_TBSPACE FROM SYSIBM.DUAL
),
     foreign_key_rule(CODE, ACTION) AS (
         VALUES ('A', 'NO ACTION'),
                ('C', 'CASCADE'),
                ('N', 'SET NULL'),
                ('R', 'RESTRICT'))
SELECT 'ALTER TABLE ' || param.TABSCHEMA || '.' || TABNAME ||
       ' ADD CONSTRAINT ' || CONSTNAME || ' FOREIGN KEY (' || (SELECT LISTAGG(COLNAME, ', ') WITHIN GROUP (ORDER BY COLSEQ) FROM SYSCAT.KEYCOLUSE keycoluse WHERE keycoluse.TABSCHEMA = param.TABSCHEMA AND keycoluse.TABNAME = references.TABNAME AND CONSTNAME = references.CONSTNAME) ||
       ') REFERENCES ' || REFTABSCHEMA || '.' || REFTABNAME || ' (' || (SELECT LISTAGG(COLNAME, ', ') WITHIN GROUP (ORDER BY COLSEQ) FROM SYSCAT.KEYCOLUSE keycoluse WHERE keycoluse.TABSCHEMA = references.REFTABSCHEMA AND keycoluse.TABNAME = references.REFTABNAME AND CONSTNAME = references.REFKEYNAME) ||
       ') ON DELETE ' || delete_rule.ACTION ||
       ' ON UPDATE ' || update_rule.ACTION || ';'
FROM SYSCAT.REFERENCES references, param
         LEFT JOIN foreign_key_rule delete_rule ON references.DELETERULE = delete_rule.CODE
         LEFT JOIN foreign_key_rule update_rule ON references.UPDATERULE = update_rule.CODE
WHERE references.TABSCHEMA = param.TABSCHEMA;

--约束同步(CHECK)
SELECT * FROM SYSCAT.TABCONST WHERE TABSCHEMA = 'TEST' AND TYPE = 'K';
--序列同步
--todo
--同义词同步
--todo