Migrating Informix to openGauss/MogDB Using MTK

Download the package with the word DB2 for migration

Supported Versions

• 12.10

Drivers

Informix ODBC(CSDK)

Not supported. Unsuccessfully adapted.

DB2 ODBC

Supported. See DB2。

Port allow remote connections from DRDA clients

Precautions

Empty string and NULL problems

Empty string and NULL problems, Informix NULL != ''

• openGauss A mode is different from Informix, and data needs to be processed. When the attribute of the column is NOT NULL, it is converted to" ", and when it is allowed to be NULL, it is converted to NULL

• openGauss PG mode is the same as Informix, NULL != '' No need to deal with

• openGauss B mode is the same as Informix, NULL != '' No need to deal with

Sequence

The Informix sequence is a table, except that there is only one self-incrementing column on the table

• Final value

  Cannot get the last value in batch. To ensure performance there is no iterative sequence to get the current value. Batch gets the last cache value, so it will be bigger than before after migration.

Table

• Partitioned tables are not supported, only migration to regular tables is supported.
• External tables are not supported.
• Uniformly handle as bigint type plus default sequence and adjust the sequence start value to the last value of the source database.

Table Data

• Support Chinese, UTF8 character set

  # jdbc Chinese character test
  jdbc:informix-sqli://localhost:19088/sysadmin:INFORMIXSERVER=dev;NEWCODESET=utf8,8859-1,819;CLIENT_LOCALE=en_US.utf8;DB_LOCALE=en_US.utf8;

Column

Column Correspondence

There are various combinations of datetime/interval types. Some of these combinations have data problems.

The datetime/interval specific type is calculated backwards according to the formula (length * 256) + (first_qualifier * 16) + last_qualifier. syscolumns-storing-column-length

The precision of the interval first_qualifier is calculated backwards from the length of the specific Field qualifier.

BIGSERIAL

openGauss unified processing as bigint type plus default sequence

DATETIME

• The exact millimeter accuracy cannot be calculated. A solution will be given in future versions.

• month/day/hour/minute/second/fraction to x, non-complete time type

  • datetime hour to second 02:00:00

  • datetime month to day

    In Informix you can store year data as 05-27 and migrate to openGauss timestamp type will become 1200-05-27 00:00:00

DECIMAL

• DECIMAL(p) Floating Point

  The default is 16 when no precision is specified. Floating point numbers of total length 16 can be inserted. openGauss/MogDB does not support this.

  The default value of scale is 255. It is a special data.

  Specifying precision is optional.

  If you specify no precision (p), DECIMAL is treated as DECIMAL(16), a floating-point decimal with a precision of 16 places.

  DECIMAL(p) has an absolute exponent range between 10-130 and 10124.

  DECIMAL --> DECIMAL(16) --> DECIMAL(32,16) ?

  • informix

    drop table tab_decimal;
    create table tab_decimal (
        col_decimal decimal,
        col_decimal_16 decimal(16),
        col_decimal_17 decimal(17),
        col_decimal_16_0 decimal(16,0),
        col_decimal_17_0 decimal(17,0),
        col_decimal_30_20 decimal(30,20),
        col_decimal3_2 decimal(3,2)
    );
    insert into tab_decimal values (1234567891011121.12345678910111213,1234567891011121.12345678910111213,1234567891011121.12345678910111213,1234567891011121.12345678910111213,1234567891011121.12345678910111213,1234567890.12345678910111213,1.23);
    insert into tab_decimal values (1.12345678910111213,1.12345678910111213,1.12345678910111213,1234567890.12345678910111213,1.12345678910111213,1234567890.12345678910111213,1.23);
    insert into tab_decimal values (12345678.12345678,12345678.12345678,12345678.12345678,1234567890.12345678910111213,12345678.12345678,1234567890.12345678910111213,1.23);
    insert into tab_decimal values (123456789.12345678,123456789.12345678,123456789.12345678,1234567890.12345678910111213,123456789.12345678,1234567890.12345678910111213,1.23);
    select * from tab_decimal;

  • openGauss

    CREATE TABLE mtk.tab_decimal (
        col_decimal DECIMAL(32,16),
        col_decimal_16 DECIMAL(32,16),
        col_decimal_17 DECIMAL(34,17),
        col_decimal_16_0 DECIMAL(16),
        col_decimal_17_0 DECIMAL(17),
        col_decimal_30_20 DECIMAL(30,20),
        col_decimal3_2 DECIMAL(3,2)
    )

• DECIMAL (p,s) Fixed Point

  Consistent with openGauss/MogDB.

INTERVAL

• Scenarios exist where the interval type is not displayed accurately in the report

  For example, interval day(8) to day may be displayed inaccurately.

• interval second to fraction

  The interval type will become 00:02:00.010 after migrating to openGauss, which is displayed as 120.010 in Informix.

  MTK converts the data according to the standard interval day to second format 00 00:00:00.000.

MONEY

Default is 16.2 when precision is not specified. Supports inserting floating point numbers of total length 16. openGauss/MogDB does not support specified precision. DECIMAL can be used instead, but there are rounding problems.

• informix

  create table tab_money (
      col_money      money,
      col_money_30_20 money(30,20),
      col_money_2    money(3,2)
  );

  Unlike the DECIMAL data type, the MONEY data type is always treated as a fixed-point decimal number.

  The database server defines the data type MONEY(p) as DECIMAL(p,2).

  If the precision and scale are not specified, the database server defines a MONEY column as DECIMAL(16,2).

• openGauss

  CREATE TABLE mtk.tab_money (
      col_money      money NULL,
      col_money_30_20 money NULL,
      col_money_2    money NULL
  )

Column default value

• Columns have problems by default. Support for conversion migration partial syntax only.

• interval column default value

• datetime column default value

  • current --> current_timestamp

  • datetime hour to second '08:00:00' Irregular time is not migrated by default.

    tk_start_time datetime hour to second default  datetime (08:00:00) hour to second  --> tk_start_time timestamp

Index

• Function indexing is not supported

Constraint

• Checking constraints for syntax conversion problems

  ALTER TABLE mtk.tab_ph_task1 ADD CONSTRAINT CHECK ((tk_name [1,1] != ' ' )) CONSTRAINT c271_149;
  -- (?is)interval\s*\(\s*([^\(\),]+)\)\s+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|FRACTION)\s+TO\s+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|FRACTION\(\d+\))
  ALTER TABLE mtk.tab_ph_task1 ADD CONSTRAINT CHECK ((tk_frequency > interval(  0 00:00:00) day to second )) CONSTRAINT tab_ph_task1_constr2

View

• No syntax conversion migration

Function

• Only support query , not support syntax conversion

Stored Procedure

• Only support query , not support syntax conversion

Trigger

• Only support query , not support syntax conversion

Synonym

Partial SQL statements

System Views

• systables Tables / Sequences / Views
  • The value of a partitioned table self-increment column is recorded on the first partition, not on the table
• syscolumns Column Information
• syssequences Sequence
• sysmaster:sysactptnhdr Last value of the sequence
• sysmaster:sysptnhdr Last value of the sequence, table contains information about partition headers
• sysfragments Partition
• sysdbspaces Tablespace
• sysmaster:sysptnext Tablespace-related
• sysconstraints Constraint
• sysobjstate Object Status
• sysreferences Foreign Key Constraints
• syschecks Checking constraints
• sysviews Views
• SYSSYNTABLE Synonym relationship table
• systriggers Triggers
• systrigbody Trigger Definition Table
• sysprocedures Stored procedures / functions
• sysprocbody Procedure/Function Definition

Sequence Information

-- A sequence is a table, which contains only self-incrementing columns
-- Tables containing self-incrementing columns are also queried as sequences
-- The last value of the sequence is the last cache value
-- Partition table self-incrementing column information on the first partition
SELECT T.OWNER
    , T.TABNAME
    , T.TABID
    , C.COLNAME
    , T.PARTNUM
    , INFORMIX.SCHEMA_COLTYPENAME(C.COLTYPE, C.EXTENDED_ID)::VARCHAR(10) COLTYPENAME
    , NVL(S.SEQID, 0) as SEQID
    , NVL(S.START_VAL, 1) as START_VAL
    , NVL(S.INC_VAL, 1)   as INC_VAL
    , NVL(S.MIN_VAL, 1)   as MIN_VAL
    , nvl(S.MAX_VAL, '')  as MAX_VAL
    , NVL(S.cycle, 0)     as cycle
    , NVL(S.cache, 1)     as cache
    , NVL(S.order, 1) as order
    -- D.CUR_SERIAL4
    , D.SERIALV as CUR_SERIAL4
    , D.CUR_SERIAL8
    , D.CUR_BIGSERIAL
    --,T.PARTNUM,F.PARTN,DECODE(T.PARTNUM,0,F.PARTN,T.PARTNUM)
FROM
  INFORMIX.SYSCOLUMNS C
  JOIN INFORMIX.SYSTABLES T ON C.TABID = T.TABID
LEFT JOIN INFORMIX.SYSFRAGMENTS F ON F.TABID = T.TABID AND F.EVALPOS=0 AND F.FRAGTYPE='T'
LEFT JOIN INFORMIX.SYSSEQUENCES S ON T.TABID=S.TABID
-- LEFT JOIN SYSMASTER:SYSACTPTNHDR D ON DECODE(T.PARTNUM,0,F.PARTN,T.PARTNUM) =D.PARTNUM
LEFT JOIN SYSMASTER:SYSPTNHDR D ON DECODE(T.PARTNUM,0,F.PARTN,T.PARTNUM) =D.PARTNUM
WHERE
  INFORMIX.SCHEMA_ISAUTOINCR(C.COLTYPE)= 'YES'
  and t.tabtype in ('T', 'Q');

Table Information

select t.tabid,
       t.owner,
       t.tabname,
       c.name,
       --t.partnum,
       --t.rowsize,
       --t.ncols,
       t.nrows,
       --t.npused,
       -- d.cur_serial4,
       d.serialv as cur_serial4,
       d.cur_serial8,
       d.cur_bigserial,
       f.strategy,
       f1.exprtext,
       f2.strategy,
       f2.exprtext
from INFORMIX.SYSTABLES t
left join informix.sysfragments f on f.tabid = t.tabid and f.evalpos = -3 and F.FRAGTYPE = 'T'
left join informix.sysfragments f1 on f1.tabid = t.tabid and f1.evalpos = -2 and F1.FRAGTYPE = 'T'
left join informix.sysfragments f2 on f2.tabid = t.tabid and f2.evalpos = 0 and F2.FRAGTYPE = 'T'
-- left join sysmaster:sysactptnhdr d on d.partnum = DECODE(t.partnum,0,f2.partn,t.partnum)
left join sysmaster:sysptnhdr d on d.partnum = DECODE(t.partnum,0,f2.partn,t.partnum)
-- left join sysmaster:sysptnext pt on t.partnum=pt.pe_partnum
left join sysmaster:sysdbspaces c on sysmaster:partdbsnum(t.partnum)= c.dbsnum
where
  t.tabtype = 'T'

Column Information

select
    t.owner,
    t.tabname,
    c.colno,
    c.colname,
    informix.schema_isautoincr(coltype) auto_increment,
    informix.schema_nullable(coltype)   nullable,
    informix.schema_coltypename(c.coltype, c.extended_id)::varchar(10) coltypename, informix.schema_precision(c.coltype,c.extended_id,c.collength) precision,
    informix.schema_numscale(c.coltype,c.collength) numscale,
    -- informix.schema_datetype(c.coltype,c.collength) datetype,
    -- pt.cur_serial4,
    pt.serialv as cur_serial4,
    pt.cur_serial8,
    pt.cur_bigserial,
    d.type,
    d.default
from
  informix.syscolumns c
join informix.systables t on c.tabid = t.tabid
left join informix.sysdefaults d on c.tabid = d.tabid and c.colno = d.colno
left join informix.sysfragments f on f.tabid = t.tabid and f.evalpos=0 and f.fragtype='T'
-- left join sysmaster:sysactptnhdr pt on decode(t.partnum,0,f.partn,t.partnum) =pt.partnum
left join sysmaster:sysptnhdr pt on decode(t.partnum,0,f.partn,t.partnum) =pt.partnum
where c.colno > 0

Index Information

-- The function index did not find the column information
-- SYSINDICES.indexkeys
-- <procid>(colno,colno)[opclassid],<procid>(colno,colno)[opclassid] --> 5 [1], <661>(2, 4) [1]
-- procid can be empty. colno [opclassid] --> 2 [1], 1 [1]
-- desc (tk_name desc,tk_type desc); -2 [1], -4 [1]
-- (tk_name desc,toupper1(tk_name,tk_type) desc  -2 [1], <661>(-2, 4) [1]
select t.tabid,t.owner,t.tabname,i.owner as idxowner,i.idxname,
    i.idxtype,
    i.clustered,
    c.colname,
    i.part1,
    i.part2,
    i.part3,
    i.part4,
    i.part5,
    i.part6,
    i.part7,
    i.part8,
    i.part9,
    i.part10,
    i.part11,
    i.part12,
    i.part13,
    i.part14,
    i.part15,
    i.part16
from sysindexes i join systables t on t.tabid = i.tabid
left join syscolumns c on c.tabid = i.tabid
and c.colno in (abs(part1), abs(part2), abs(part3), abs(part4), abs(part5), abs(part6), abs(part7), abs(part8), abs(part9), abs(part10),abs(part11), abs(part12), abs(part13), abs(part14), abs(part15), abs(part16))
where not exists (
    select
        c.idxname
    from
        sysconstraints c
    where
        c.tabid = i.tabid
        and c.idxname = i.idxname
        and c.idxname is not null
    )
and t.owner = 'mtk'

Constraint Information

select tab.tabid,
    tab.owner,
    tab.tabname,
    constr.constrname,
    constr.constrtype,
    -- chk.seqno,
    chk.checktext,
    c1.colname,
    -- constr.constrid,
    constr.idxname,
    rt.owner as r_owner,
    rt.tabname as r_cons_tab,
    rc.constrname as r_cons_name,
    r.updrule AS update_rule,
    r.delrule AS delete_rule,
    s.state,
    i.part1,
    i.part2,
    i.part3,
    i.part4,
    i.part5,
    i.part6,
    i.part7,
    i.part8,
    i.part9,
    i.part10,
    i.part11,
    i.part12,
    i.part13,
    i.part14,
    i.part15,
    i.part16
from sysconstraints constr
join sysobjstate s on s.tabid=constr.tabid and s.name=constr.constrname  and s.objtype='C'
join systables tab on tab.tabid = constr.tabid
left outer join syschecks chk on chk.constrid = constr.constrid and chk.type = 'T'
left outer join sysreferences r on constr.constrid=r.constrid
left outer join sysconstraints rc on rc.constrid=r.primary
left outer join systables rt on r.ptabid = rt.tabid
left outer join sysindexes i on i.idxname = constr.idxname
left outer join syscolumns c1 on c1.tabid = tab.tabid
    and c1.colno in (abs(part1), abs(part2), abs(part3), abs(part4), abs(part5), abs(part6), abs(part7), abs(part8), abs(part9), abs(part10),
                                          abs(part11), abs(part12), abs(part13), abs(part14), abs(part15), abs(part16))
WHERE constr.constrtype != 'N'

View Information

select
    t.owner,
    t.tabname,
    v.tabid,
    v.seqno,
    v.viewtext
from
    informix.sysviews v
join informix.systables t on
    v.tabid = t.tabid
order by v.tabid,v.seqno

Function Information

select
 sp.procid,
 sp.owner,
 sp.procname,
 sb.datakey,
 sb.seqno,
 sb.data
from
 informix.sysprocedures sp
 left join sysprocbody sb on sp.procid = sb.procid
where sb.datakey IN ('D','T')
  and sp.isproc='f'
order by sb.procid, sb.seqno

Stored Procedure Information

select
 sp.procid,
 sp.owner,
 sp.procname,
 sb.datakey,
 sb.seqno,
 sb.data
from
 informix.sysprocedures sp
 left join sysprocbody sb on sp.procid = sb.procid
where sb.datakey IN ('D','T')
  and sp.isproc='t'
order by sb.procid, sb.seqno

Trigger Information

select tr.owner,tr.trigname,tr.trigid,tr.tabid,tr.event,tr.old,tr.new,
     ta.owner,ta.tabname,
    tb.seqno, tb.data
from
    systables ta
    join systriggers tr on tr.tabid = ta.tabid
    join systrigbody tb on tb.trigid = tr.trigid
where  ta.tabtype = 'T'
  and tb.datakey IN ('A', 'D')
order by tr.trigname, datakey desc, seqno

Synonym

select t.owner,t.tabname,t1.owner,t1.tabname,t.tabtype from informix.systables t
left join informix.SYSSYNTABLE s on t.tabid=s.tabid
left join  informix.systables t1 on s.btabid=t1.tabid
where t.tabtype in ('S','P')