db2至金仓迁移问题总结

描述

列CTID与系统名重复

解决方法

CTID为金仓关键字，将列CTID修改为其他名称

描述

CREATE TRIGGER "DELETESECTIONOPENINFO" AFTER UPDATE OF DELETESTATUS ON SECTION REFERENCING OLD AS oldrow NEW AS newrow OLD_TABLE AS oldtable NEW_TABLE AS newtable FOR EACH ROW MODE DB2SQL WHEN (newrow.deletestatus='C') BEGIN ATOMIC delete from sectionopen where sectionid=oldrow.sectionid ; END;

解决方法

CREATE TRIGGER "DELETESECTIONOPENINFO"
    AFTER UPDATE OF DELETESTATUS
    ON SECTION
   -- REFERENCING OLD AS oldrow NEW AS new
    FOR EACH ROW
    WHEN (new.deletestatus = 'C')
BEGIN
    delete from sectionopen where sectionid = old.sectionid;
END;

描述

CREATE OR REPLACE PROCEDURE "PSCT_IMPORT"
 (IN NPSCTMSPN CHARACTER(6),
  IN NKEY_ID CHARACTER(14),
  IN NTABLE_NAME CHARACTER(4)
 )
  SPECIFIC PSCT_IMPORT
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  begin
    declare nIBSDRQIS varchar(16);--
    declare nIRSDRQIS varchar(16);--
    declare currentdate varchar(8);--
    declare sysdate varchar(10);--
    SELECT current date into sysdate FROM sysibm.sysdummy1;--
    set currentdate = substr(sysdate,1,4)||substr(sysdate,6,2)||substr(sysdate,9,2);--
    if nTABLE_NAME='IBSD' then
--得到IBSD的主键
        call PK_IBSD(nKEY_ID,nIBSDRQIS);--
--播入到IBSD表中
        insert into IBSD(IBSDRQIS,IBSDIRSN,IBSDSPCD,IBSDSPNM,IBSDCOST,IBSDSNDT,IBSDDTOP,IBSDSTCD,IBSDLPID,IBSDLPNM,IBSDLPTP,IBSDYQXS)
         select nIBSDRQIS,nKEY_ID,nPSCTMSPN,MIFEMENA,'0',currentdate,currentdate,'A',UINFUSAC,UINFUSNA,UINFUSMO,'0'
         from MIFE
         left join UINF on UINFUSAC=MIFEMEAC||'000'
         where MIFEMEAC= nPSCTMSPN;--
--修改IBMD表
--        update IBMD set IBMDIFPD= currentdate,IBMDFMST='1' where IBMDORQS=nKEY_ID;--
    elseif nTABLE_NAME='IRSD' then
        call PK_IRSD(nKEY_ID,nIRSDRQIS);--
--播入到IBSD表中
        insert into IRSD(IRSDRQIS,IRSDIRSN,IRSDSPCD,IRSDSPNM,IRSDCOST,IRSDSNDT,IRSDDTOP,IRSDSTCD,IRSDLPID,IRSDLPNM,IRSDLPTP)
         select nIRSDRQIS,nKEY_ID,nPSCTMSPN,MIFEMENA,'0',currentdate,currentdate,'A',UINFUSAC,UINFUSNA,UINFUSMO
         from MIFE,UINF
         where MIFEMEAC= nPSCTMSPN and UINFUSAC = CONCAT(nPSCTMSPN,'000');--
--修改IRMD表

    end if;--
end;

解决方法

CREATE OR REPLACE PROCEDURE "PSCT_IMPORT"(NPSCTMSPN CHARACTER(6),
                                                    NKEY_ID CHARACTER(14),
                                                    NTABLE_NAME CHARACTER(4))
as
    nIBSDRQIS   varchar(16);--
    nIRSDRQIS   varchar(16);--
    currentdate varchar(8);--
    sysdate     varchar(10);--
begin
    SELECT current date
    into sysdate
    FROM sysibm.sysdummy1;--
        currentdate = substr(sysdate, 1, 4) || substr(sysdate, 6, 2) || substr(sysdate, 9, 2);--
    if nTABLE_NAME = 'IBSD' then
        --得到IBSD的主键 call PK_IBSD(nKEY_ID,nIBSDRQIS);
        --
--播入到IBSD表中
        insert into IBSD(IBSDRQIS, IBSDIRSN, IBSDSPCD, IBSDSPNM, IBSDCOST, IBSDSNDT, IBSDDTOP, IBSDSTCD,
                                 IBSDLPID,
                                 IBSDLPNM, IBSDLPTP, IBSDYQXS)
        select nIBSDRQIS,
               nKEY_ID,
               nPSCTMSPN,
               MIFEMENA,
               '0',
               currentdate,
               currentdate,
               'A',
               UINFUSAC,
               UINFUSNA,
               UINFUSMO,
               '0'
        from MIFE
                 left join UINF on UINFUSAC = MIFEMEAC || '000'
        where MIFEMEAC = nPSCTMSPN;
        --
--修改IBMD表
--        update IBMD set IBMDIFPD= currentdate,IBMDFMST='1' where IBMDORQS=nKEY_ID;--
    elsif nTABLE_NAME = 'IRSD' then
        PK_IRSD(nKEY_ID, nIRSDRQIS);
        --
--播入到IBSD表中
        insert into IRSD(IRSDRQIS, IRSDIRSN, IRSDSPCD, IRSDSPNM, IRSDCOST, IRSDSNDT, IRSDDTOP, IRSDSTCD,
                                 IRSDLPID,
                                 IRSDLPNM, IRSDLPTP)
        select nIRSDRQIS,
               nKEY_ID,
               nPSCTMSPN,
               MIFEMENA,
               '0',
               currentdate,
               currentdate,
               'A',
               UINFUSAC,
               UINFUSNA,
               UINFUSMO
        from MIFE,
             UINF
        where MIFEMEAC = nPSCTMSPN
          and UINFUSAC = CONCAT(nPSCTMSPN, '000');
        --
--修改IRMD表

    end if;--
end;
 

描述

CREATE OR REPLACE FUNCTION "EXPLAIN_GET_MSGS"( EXPLAIN_REQUESTER VARCHAR(128), EXPLAIN_TIME TIMESTAMP, SOURCE_NAME VARCHAR(128), SOURCE_SCHEMA VARCHAR(128), SOURCE_VERSION VARCHAR(64), EXPLAIN_LEVEL CHAR(1), STMTNO INTEGER, SECTNO INTEGER, INLOCALE VARCHAR(33) ) RETURNS TABLE ( EXPLAIN_REQUESTER VARCHAR(128), EXPLAIN_TIME TIMESTAMP, SOURCE_NAME VARCHAR(128), SOURCE_SCHEMA VARCHAR(128), SOURCE_VERSION VARCHAR(64), EXPLAIN_LEVEL CHAR(1), STMTNO INTEGER, SECTNO INTEGER, DIAGNOSTIC_ID INTEGER, LOCALE VARCHAR(33), MSG VARCHAR(4096) ) SPECIFIC EXPLAIN_GET_MSGS LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA RETURN SELECT A.A_EXPLAIN_REQUESTER, A.A_EXPLAIN_TIME, A.A_SOURCE_NAME, A.A_SOURCE_SCHEMA, A.A_SOURCE_VERSION, A.A_EXPLAIN_LEVEL, A.A_STMTNO, A.A_SECTNO, A.A_DIAGNOSTIC_ID, F.LOCALE, F.MSG FROM EXPLAIN_DIAGNOSTIC A( A_EXPLAIN_REQUESTER, A_EXPLAIN_TIME, A_SOURCE_NAME, A_SOURCE_SCHEMA, A_SOURCE_VERSION, A_EXPLAIN_LEVEL, A_STMTNO, A_SECTNO, A_DIAGNOSTIC_ID, A_CODE ), TABLE( SYSPROC.EXPLAIN_GET_MSG2( INLOCALE, A.A_CODE, ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME AND A.A_SOURCE_NAME = B.SOURCE_NAME AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA AND A.A_SOURCE_VERSION = B.SOURCE_VERSION AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL AND A.A_STMTNO = B.STMTNO AND A.A_SECTNO = B.SECTNO AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID AND B.ORDINAL=1 ), ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME AND A.A_SOURCE_NAME = B.SOURCE_NAME AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA AND A.A_SOURCE_VERSION = B.SOURCE_VERSION AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL AND A.A_STMTNO = B.STMTNO AND A.A_SECTNO = B.SECTNO AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID AND B.ORDINAL=2 ), ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME AND A.A_SOURCE_NAME = B.SOURCE_NAME AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA AND A.A_SOURCE_VERSION = B.SOURCE_VERSION AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL AND A.A_STMTNO = B.STMTNO AND A.A_SECTNO = B.SECTNO AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID AND B.ORDINAL=3 ) ) ) F WHERE ( EXPLAIN_REQUESTER IS NULL OR EXPLAIN_REQUESTER = A.A_EXPLAIN_REQUESTER ) AND ( EXPLAIN_TIME IS NULL OR EXPLAIN_TIME = A.A_EXPLAIN_TIME ) AND ( SOURCE_NAME IS NULL OR SOURCE_NAME = A.A_SOURCE_NAME ) AND ( SOURCE_SCHEMA IS NULL OR SOURCE_SCHEMA = A.A_SOURCE_SCHEMA ) AND ( SOURCE_VERSION IS NULL OR SOURCE_VERSION = A.A_SOURCE_VERSION ) AND ( EXPLAIN_LEVEL IS NULL OR EXPLAIN_LEVEL = A.A_EXPLAIN_LEVEL ) AND ( STMTNO IS NULL OR STMTNO = A.A_STMTNO ) AND ( SECTNO IS NULL OR SECTNO = A.A_SECTNO );

解决方法

CREATE OR REPLACE FUNCTION EXPLAIN_GET_MSGS(EXPLAIN_REQUESTER VARCHAR(128), EXPLAIN_TIME TIMESTAMP,
                                                    SOURCE_NAME VARCHAR(128), SOURCE_SCHEMA VARCHAR(128),
                                                    SOURCE_VERSION VARCHAR(64), EXPLAIN_LEVEL CHAR(1),
                                                    STMTNO INTEGER, SECTNO INTEGER, INLOCALE VARCHAR(33))
    RETURNS TABLE
            (
                EXPLAIN_REQUESTER VARCHAR(128),
                EXPLAIN_TIME      TIMESTAMP,
                SOURCE_NAME       VARCHAR(128),
                SOURCE_SCHEMA     VARCHAR(128),
                SOURCE_VERSION    VARCHAR(64),
                EXPLAIN_LEVEL     CHAR(1),
                STMTNO            INTEGER,
                SECTNO            INTEGER,
                DIAGNOSTIC_ID     INTEGER,
                LOCALE            VARCHAR(33),
                MSG               VARCHAR(4096)
            )
    LANGUAGE sql
as
$$
SELECT A.A_EXPLAIN_REQUESTER,
       A.A_EXPLAIN_TIME,
       A.A_SOURCE_NAME,
       A.A_SOURCE_SCHEMA,
       A.A_SOURCE_VERSION,
       A.A_EXPLAIN_LEVEL,
       A.A_STMTNO,
       A.A_SECTNO,
       A.A_DIAGNOSTIC_ID,
       F.LOCALE,
       F.MSG
FROM EXPLAIN_DIAGNOSTIC A(A_EXPLAIN_REQUESTER, A_EXPLAIN_TIME, A_SOURCE_NAME, A_SOURCE_SCHEMA,
                          A_SOURCE_VERSION, A_EXPLAIN_LEVEL, A_STMTNO, A_SECTNO, A_DIAGNOSTIC_ID, A_CODE),
     TABLE(SYSPROC.EXPLAIN_GET_MSG2(INLOCALE, A.A_CODE, (SELECT TOKEN
                                                         FROM EXPLAIN_DIAGNOSTIC_DATA B
                                                         WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                                                           AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME
                                                           AND A.A_SOURCE_NAME = B.SOURCE_NAME
                                                           AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA
                                                           AND A.A_SOURCE_VERSION = B.SOURCE_VERSION
                                                           AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL
                                                           AND A.A_STMTNO = B.STMTNO
                                                           AND A.A_SECTNO = B.SECTNO
                                                           AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID
                                                           AND B.ORDINAL = 1), (SELECT TOKEN
                                                                                FROM EXPLAIN_DIAGNOSTIC_DATA B
                                                                                WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                                                                                  AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME
                                                                                  AND A.A_SOURCE_NAME = B.SOURCE_NAME
                                                                                  AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA
                                                                                  AND A.A_SOURCE_VERSION = B.SOURCE_VERSION
                                                                                  AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL
                                                                                  AND A.A_STMTNO = B.STMTNO
                                                                                  AND A.A_SECTNO = B.SECTNO
                                                                                  AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID
                                                                                  AND B.ORDINAL = 2),
                                    (SELECT TOKEN
                                     FROM EXPLAIN_DIAGNOSTIC_DATA B
                                     WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                                       AND A.A_EXPLAIN_TIME = B.EXPLAIN_TIME
                                       AND A.A_SOURCE_NAME = B.SOURCE_NAME
                                       AND A.A_SOURCE_SCHEMA = B.SOURCE_SCHEMA
                                       AND A.A_SOURCE_VERSION = B.SOURCE_VERSION
                                       AND A.A_EXPLAIN_LEVEL = B.EXPLAIN_LEVEL
                                       AND A.A_STMTNO = B.STMTNO
                                       AND A.A_SECTNO = B.SECTNO
                                       AND A.A_DIAGNOSTIC_ID = B.DIAGNOSTIC_ID
                                       AND B.ORDINAL = 3))) F
WHERE (EXPLAIN_REQUESTER IS NULL OR EXPLAIN_REQUESTER = A.A_EXPLAIN_REQUESTER)
  AND (EXPLAIN_TIME IS NULL OR EXPLAIN_TIME = A.A_EXPLAIN_TIME)
  AND (SOURCE_NAME IS NULL OR SOURCE_NAME = A.A_SOURCE_NAME)
  AND (SOURCE_SCHEMA IS NULL OR SOURCE_SCHEMA = A.A_SOURCE_SCHEMA)
  AND (SOURCE_VERSION IS NULL OR SOURCE_VERSION = A.A_SOURCE_VERSION)
  AND (EXPLAIN_LEVEL IS NULL OR EXPLAIN_LEVEL = A.A_EXPLAIN_LEVEL)
  AND (STMTNO IS NULL OR STMTNO = A.A_STMTNO)
  AND (SECTNO IS NULL OR SECTNO = A.A_SECTNO);
$$
;

描述

select listagg(pname,',') as biddername,batchid from Participator

解决方法

select listagg(pname,',') within group (order by pname) as biddername,batchid from Participator

描述

select xmlxshare,gsname,LOCATE('*',gsname)+1 as gsname1 from mb

解决方法

select xmlxshare,gsname,INSTR('*',gsname)+1 as gsname1 from mb