Db2迁移到oracle的思路

准备工具：db2tooracle,sqldeveloper,dbvisualizer,textdiff

准备脚本：导sequence,查看表记录，收集统计信息

1. 通过db2执行RUNSTATS ON生成最新统计信息，统计每个表的记录数
2. 通过db2tooracle导表快速导表到oracle（试用版每次20个表+不超过1000条记录的表）
3. 通过sqldeveloper导2中大于1000条记录和报错未成功的表
4. 通过dbvisualizer导出所有对象，包括视图、索引、约束外键和触发器等
5.  应用开发验证测试，注意字段类型变化
6. 根据问题排查处理
7. 对比记录数，正式迁移只导变化的表即可，迁移考虑并行。

DB2如何查看当前用户模式及切换用户

1. 连接数据库：db2 connect to phis

2. 查询当前用户模式：select current schema from sysibm.sysdummy1 或 select current schema from sysibm.dual

3. 切换用户模式：set current schema phis

4. 切换用户： db2 connect to cpsdb user phis using 111111

5. 给其他用户授权：db2 grant dbadm on database to user phis

6. 查看当前用户的权限： db2 get authorizations

使用导的脚本参考：

select tabname,card from syscat.tables where tabschema = '用户名' and type='T' order by tabname asc;

--oracle下查询

create or replace function count_rows(table_name in varchar2,

                                      owner      in varchar2 default null)

  return number authid current_user IS

  num_rows number;

  stmt     varchar2(2000);

begin

  if owner is null then

    stmt := 'select count(*) from "' || table_name || '"';

  else

    stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';

  end if;

  execute immediate stmt

    into num_rows;

  return num_rows;

end;

/

执行：select table_name, count_rows(table_name) nrows from user_tables;

select tabname,card from syscat.tables where tabschema = '用户名' and type='T' and card>=1000 order by tabname asc;

select  'CREATE SEQUENCE ' ||SEQNAME||  

        ' MINVALUE ' ||MINVALUE||  

        ' MAXVALUE ' ||MAXVALUE||

         ( case  when LASTASSIGNEDVAL IS NULL  then ' START WITH '||CHAR(BIGINT(START)+1)  else ' START WITH '||CHAR(BIGINT(LASTASSIGNEDVAL)+1) end)||

        ' INCREMENT  BY ' ||INCREMENT||

       ( case  when CYCLE='N'  then ' NOCYCLE ' else ' CYCLE ' end)||

        ( case  when CACHE=0  then ' NOCACHE '  else ' CACHE '|| CACHE end)||

       ( case  when ORDER='N'  then ' NOORDER ' else ' ORDER ' end)||

       ';'

from SYSIBM.SYSSEQUENCES where OWNER= '用户名'; 

导出外键并修改替换ON update CASCADE对应DEFERRABLE;

select TABNAME,COLNAME ,LENGTH from syscat.columns where tabschema='用户名' AND LENGTH>2000 and LENGTH<10000;

select TABLE_NAME,column_name,DATA_TYPE,data_length
from user_tab_columns
where TABLE_NAME in('AOP_LOG'
,'BATCH_JOB_EXECUTION'
,'BATCH_JOB_EXECUTION_CONTEXT'
,'BATCH_STEP_EXECUTION'
,'BATCH_STEP_EXECUTION_CONTEXT'
,'ETL_RECORD'
,'PSI_BAS_DRUG'
,'PSI_BAS_DRUG_SPLITPACKING'
,'PSI_OUTP_INJURY_BASE'
,'PSI_OUTP_RCPT_INJURY'
,'PSI_OUTP_REGIST_INJURY'
,'YX_DRUG') and column_name in('CONTENT'
,'SHORT_CONTEXT'
,'EXIT_MESSAGE'
,'REMARK'
,'INJURY_PROCES'
,'AMOUNT')
order by TABLE_NAME,column_name;

修改字段类型：

--alter table      表名        modify      CHECK_RESULT      varchar2(3000) ;

alter table        表名                     rename column        CHECK_RESULT               TO    CHECK_RESULT_BK        ;

alter table        表名                     add  CHECK_RESULT               VARCHAR2(3000)    ;

update     表名                     set   CHECK_RESULT               =       dbms_lob.substr(    CHECK_RESULT_BK        ,3000)         ;

commit;

alter table        表名                     drop column     CHECK_RESULT_BK        ;

查看无主键表:

db2:
select tabname,card from syscat.tables a where a.tabschema = 'PHIS' and a.type='T'
and not exists(select * from syscat.tabconst b where TYPE='P' and a.tabname=b.tabname) order by tabname asc;

oracle:
select table_name,num_rows
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name) order by table_name asc;

查询主键：

SELECT K.COLNAME AS 主键名 FROM SYSCAT.KEYCOLUES K WHERE TABSCHEMA='SCHEMA' AND TABNAME='TABNAME';

select A.TABNAME, B.COLNAME  from syscat.tabconst A ,SYSCAT.KEYCOLUSE B WHERE A.CONSTNAME = B.CONSTNAME AND A.TYPE='P';

select constname, tabname, type from syscat.tabconst  where TYPE='P';

  查询索引

SELECT I.INDNAME AS 索引名,I.COLNAMES AS 索引字段 FROM SYSCAT.INDEXES I WHERE TABSCHEMA='SCHEMA' AND TABNAME='TABNAME';

查询所有的表

SELECT T.NAME AS 表英文名,T.REMARKS AS 表中文,T.TYPE AS 表类型 FROM SYSIBM.SYSTABLES T WHERE CREATOR='SCHEMA';

查询表约束

SELECT INDSCHEMA,INDNAME AS 约束名,COLNAMES AS 约束字段名,UNIQUERULE AS 约束状态 FROM SYSCAT.INDEXS WHERE TABSCHEMA='SCHEMA_NAME' AND TABNAME='TABNAME' AND UNIQUERULE IN ('U','P');

 查询表字段

SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='TABNAME';

SELECT C.COLNAME AS 字段英文名,C.REMARKS AS 字段中文注释,C.TYPENAME AS 字段类型,C.LENGTH AS 字段长度,C.NULLS AS 是否允许为空  FROM SYSCAT.COLUMNS C  WHERE TABNAME='TABNAME';

所有涉及double字段的表：

select TABSCHEMA,TABNAME,COLNAME,LENGTH,TYPENAME from syscat.COLUMNS WHERE TYPENAME = 'DOUBLE' and TABSCHEMA ='XXX' order by tabname asc;

double类型导number（*,2)对应精度调整：db2->oracle

alter table xxx    modify (xxx    NUMBER(*,2));

 查询存储过程

SELECT P.PROCNAME AS 存储过程名,P.SPECIFICNAME AS 存储过程别名 FROM SYSCAT.PROCEDURES P WHERE PROCSCHEMA='SCHEMA_NAME';

查看表空间占用：

select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,
      sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE 
    from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_content_type,tbsp_page_size
order by 1