GBK库中汉字占两位,UTF-8中一个汉字占3位,迁移主要工作变成了修改字段为char,varchar类型的长度为原来的1.5倍。
1、将原来GBK数据库db2look成ddl语句,在utf-8库中执行
2、编写存储过程ALTER_GBK_UTF,入参为schema名称
CREATE PROCEDURE ALTER_GBK_UTF(@TABSCHEMA VARCHAR(128))
BEGIN
DECLARE @SQL VARCHAR(3000);
DECLARE @STMT STATEMENT;
FOR v1 AS
SELECT A.TABSCHEMA, A.TABNAME, A.COLNAME,A.TYPENAME,A.LENGTH
FROM syscat.columns A,SYSCAT.TABLES B
WHERE A.TABSCHEMA =@TABSCHEMA AND A.TYPENAME IN ('CHARACTER','VARCHAR')
AND B.TABSCHEMA =A.TABSCHEMA AND A.TABNAME =B.TABNAME AND B.TYPE ='T'
ORDER BY COLNO FETCH FIRST 100 ROWS ONLY DO
SET @SQL='ALTER TABLE '||TRIM(TABSCHEMA)||'.'||TABNAME||' ALTER COLUMN '||COLNAME||' SET DATA TYPE '||TYPENAME||'('||VARCHAR(CEIL(LENGTH*1.5))||')';
INSERT INTO TEST1(COL1) VALUES(@SQL);
PREPARE @STMT FROM @SQL;
EXECUTE @STMT ;
CALL SYSPROC.ADMIN_CMD('REORG TABLE '||TRIM(TABSCHEMA)||'.'||TABNAME );
END FOR ;
END
BEGIN
DECLARE @SQL VARCHAR(3000);
DECLARE @STMT STATEMENT;
FOR v1 AS
SELECT A.TABSCHEMA, A.TABNAME, A.COLNAME,A.TYPENAME,A.LENGTH
FROM syscat.columns A,SYSCAT.TABLES B
WHERE A.TABSCHEMA =@TABSCHEMA AND A.TYPENAME IN ('CHARACTER','VARCHAR')
AND B.TABSCHEMA =A.TABSCHEMA AND A.TABNAME =B.TABNAME AND B.TYPE ='T'
ORDER BY COLNO FETCH FIRST 100 ROWS ONLY DO
SET @SQL='ALTER TABLE '||TRIM(TABSCHEMA)||'.'||TABNAME||' ALTER COLUMN '||COLNAME||' SET DATA TYPE '||TYPENAME||'('||VARCHAR(CEIL(LENGTH*1.5))||')';
INSERT INTO TEST1(COL1) VALUES(@SQL);
PREPARE @STMT FROM @SQL;
EXECUTE @STMT ;
CALL SYSPROC.ADMIN_CMD('REORG TABLE '||TRIM(TABSCHEMA)||'.'||TABNAME );
END FOR ;
END
3、执行ALTER_GBK_UTF
缺陷:如果碰到更改字段后原有表空间放不下原来表没有处理。留待后续完成