使用MTK迁移Informix到openGauss/MogDB

-- 一个序列为一个表,此表只含有自增列
-- 表中含有自增列也当作序列进行查询
-- 序列的最后值为最后的cache值
-- 分区表自增列的信息在第一个分区上
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');

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'

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

-- 函数索引没有找到列信息
-- SYSINDICES.indexkeys
-- <procid>(colno,colno)[opclassid],<procid>(colno,colno)[opclassid] --> 5 [1], <661>(2, 4) [1]
-- procid 可为空. 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'

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'

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

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

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

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

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')