문서유형ㅣ기술정보
분야ㅣ마이그레이션
적용제품버전ㅣTibero7.2.4
문서번호ㅣTMITI036
개요
Table Migrator를 활용하여 DB2 데이터를 Tibero로 이관하는 절차를 기술합니다.
구성 환경
- DB2 : 11.5.9.0
- Tibero : 7.2.4
- Table migrator : 305455
방법
1. 이관 대상 확인
DB2는 스키마(Schema)와 OS 사용자 계정이 직접 연결되는 구조입니다.
DB2에서 스키마는 동일한 이름의 OS 사용자 계정이 DB에 연결될 때 자동으로 생성되며, 스키마 소유권도 해당 계정에 귀속됩니다.
Tibero로 이관할 경우 스키마 단위로 객체를 매핑해야 하며, OS 계정 기준으로 매핑할 경우 객체 조회 오류 또는 접근 권한 오류가 발생할 수 있습니다.
# su - db2
$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 11.5.9.0
SQL authorization ID = DB2
Local database alias = TESTDB
1) 버전
$ db2ls
Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V11.5 11.5.9.0 0 Wed Dec 10 14:13:22 2025 KST
2) 스키마별 용량
$ db2 "SELECT
tabs.TABSCHEMA AS schema_name, SUM(tabs.DATA_OBJECT_P_SIZE + tabs.INDEX_OBJECT_P_SIZE) / 1024 AS size_kb
FROM SYSIBMADM.ADMINTABINFO tabs
GROUP BY tabs.TABSCHEMA
ORDER BY
size_kb DESC"
SCHEMA_NAME SIZE_KB
------------------------------ -----------------------------
SYSIBM 23
APP 0
DB2 0
SYSTOOLS 0
4 record(s) selected.
3) 테이블 목록
$ db2 "list tables for schema APP"
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CUSTOMER APP T 2025-12-10-14.45.08.156662
MIG_TEST APP T 2025-12-10-15.22.29.381155
2 record(s) selected.
4) 테이블 정보
$ db2 "SET CURRENT SCHEMA = APP"
DB20000I The SQL command completed successfully.
$ db2 "describe table mig_test"
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
A SYSIBM INTEGER 4 0 No
B SYSIBM CHARACTER 25 0 Yes
C SYSIBM VARCHAR 25 0 Yes
D SYSIBM DATE 4 0 Yes
E SYSIBM DECIMAL 10 2 Yes
F SYSIBM CLOB 1048576 0 Yes
6 record(s) selected.
2. DDL 이관
데이터 이관 전 DB2과 동일한 구조(DDL)를 Tibero에 생성합니다.
1. DB2
1) 스크립트 덤프
$ db2look -d testdb -t mig_test -z app -e -o mig_test.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2
-- Specified SCHEMA is: APP
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- Output is sent to file: mig_test.sql
2) 스크립트 확인
$ cat mig_test.sql
...
CONNECT TO TESTDB;
------------------------------------------------
-- DDL Statements for Table "APP "."MIG_TEST"
------------------------------------------------
CREATE TABLE "APP"."MIG_TEST" (
"A" INTEGER NOT NULL ,
"B" CHAR(25 OCTETS) ,
"C" VARCHAR(25 OCTETS) ,
"D" DATE ,
"E" DECIMAL(10,2) ,
"F" CLOB(1048576 OCTETS) LOGGED NOT COMPACT )
;
2. Tibero
1) tibero에서 테이블 생성
SQL> CREATE TABLE nam.MIG_TEST(
a NUMBER NOT NULL,
b CHAR(25),
c VARCHAR(25),
d DATE,
e NUMBER(10,2),
f CLOB
);
Table 'APP.MIG_TEST' created.
참고
DB2 스키마.테이블 단위 덤프
db2look -d <DB명> -t <테이블명> -z <스키마명> -e -o <출력파일.sql>
DB2 스키마 단위 덤프
db2look -d <DB명> -z <스키마명> -e -o <출력파일.sql>※ Tibero에 맞게 DDL 변환 필요
참고
DB2/ Tibero Data type 비교
카테고리 DB2 Tibero 비고 숫자 SMALLINT NUMBER(5) 작은 정수 INTEGER / INT NUMBER(10) 가장 일반적인 정수 BIGINT NUMBER(19) 큰 정수 DECIMAL(p,s) / NUMERIC(p,s) NUMBER(p,s) 정밀도 지정 가능 IDENTITY (자동 증가) NUMBER + SEQUENCE AUTO_INCREMENT 직접 구성 필요 문자 CHAR(n) CHAR(n) 고정 길이 문자열 VARCHAR(n) VARCHAR2(n) 가변 길이 문자열 CLOB CLOB 큰 문자열, LOB 타입 날짜/시간 DATE DATE 날짜만 저장 TIMESTAMP TIMESTAMP 날짜 + 시간 Boolean BOOLEAN CHAR(1) 또는 NUMBER(1) Tibero에는 Boolean 없음, 0/1 또는 Y/N으로 구현 이진 데이터 / LOB BLOB BLOB 바이너리 데이터 저장 URL : https://www.ibm.com/docs/en/db2/11.5.x?topic=elements-data-types&utm_source 참고
3. Table migrator 설정
DB2 JDBC Driver를 준비하여 Table migrator 폴더에 복사합니다.
1. JDBC 준비 (/opt/ibm/db2/V11.5/java 위치에 존재) $ cp /opt/ibm/db2/V11.5/java/db2jcc4.jar /mig/ 2. migrator.sh 수정 $ cat migrator.sh java -classpath migrator_cli.jar:ojdbc7.jar:db2jcc4.jar:mariadb-java-client-3.0.7.jar:mysql-connector-java-8.0.25.jar:osdt_core.jar:osdt_cert.jar:oraclepki.jar:postgresql-42.6.2.jar:msllogger-14.jar:toolcom.jar:internal-jdbc-16.jar:antlr-4.3-complete.jar com.m.migrator.Main "$@" 3. 환경 설정 $ migrator.properties ####################### # Source DB Connection ####################### SOURCE_TYPE=GENERIC SOURCE_DRIVER=com.ibm.db2.jcc.DB2Driver SOURCE_URL=jdbc:db2://192.168.10.210:25010/testdb:progressiveStreaming=2; SOURCE_USER=db2 SOURCE_PASSWORD=db2 SOURCE_SCHEMA=app SOURCE_TABLE=mig_test ####################### # Target DB Connection ####################### TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@localhost:21724:tb724 TARGET_USER=sys TARGET_PASSWORD=tibero TARGET_SCHEMA=app TARGET_TABLE=mig_test INSERT_METHOD=DPL CHECK_COLUMN_METADATA=N # 이외의 파라미터는 사이트 상황에 맞게 설정
4. 데이터 이관
Table Migrator를 이용하여 DB2 데이터를 Tibero로 이관합니다.
이관 완료 후에는 로그 및 테이블 건수 비교 등을 통해 데이터 검증을 수행합니다.
1. migrator.sh 수행
$ sh migrator.sh PROPERTY_FILE=migrator.properties
********************************************************************************
*
* Start Log for table migrator.
* Vendor: tibero
* Product: table migrator
* Version: 305455
*
* Trace is started on 17:36:03.399
* Log level: INFO
*
********************************************************************************
17:36:03.418 [main] INFO - PROPERTY_FILE=migrator.properties
SOURCE_TYPE=GENERIC
SOURCE_DRIVER=com.ibm.db2.jcc.DB2Driver
SOURCE_URL=jdbc:db2://192.168.10.210:25010/testdb:progressiveStreaming=2;
SOURCE_USER=db2
SOURCE_PASSWORD=************
SOURCE_SCHEMA=app
SOURCE_SCHEMAORIGN=app
SOURCE_TABLE=mig_test
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:21724:tb724
TARGET_USER=sys
TARGET_PASSWORD=************
TARGET_SCHEMA=app
TARGET_TABLE=mig_test
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=DPL
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N
17:36:03.426 [main] INFO - connect to the source - url : jdbc:db2://192.168.10.210:25010/testdb:progressiveStreaming=2; id : db2
17:36:03.581 [main] INFO - connect to the target - url : jdbc:internal:thin:@localhost:21724:tb724 id : sys
17:36:03.659 [main] INFO - connect to the target - url : jdbc:internal:thin:@localhost:21724:tb724 id : sys
SOUCE_SCEMA : app
SOUCE_TABLE : mig_test
TARGET_SCEMA : app
TARGET_TABLE : mig_test
17:36:03.688 [main] INFO - extract thread count : 1
17:36:03.688 [main] INFO - start extracting thread...
17:36:03.688 [main] INFO - start loading thread...
[E0]1765355763688 - STARTED
[L0]1765355763692 Loader started
17:36:03.692 [L0] INFO - [L0]1765355763692 Loader started
17:36:03.692 [L0] INFO - DPL Loading Information
17:36:03.692 [L0] INFO - table name : app.mig_test
17:36:03.692 [L0] INFO - columns [6] - "A" "B" "C" "D" "E" "F"
[L0] 500
[E0]1765355763726 - TOTAL Extracted ROWS: 1002 (source schema = app, source table = mig_test)
[L0] 1000
[L0]1765355763855 TOTAL Loaded ROWS: 1002 (target schema = app, target table = mig_test)
17:36:03.855 [L0] INFO - [L0]1765355763855 TOTAL Loaded ROWS: 1002 (target schema = app, target table = mig_test)
17:36:03.856 [L0] INFO - Loader finally
17:36:03.856 [L0] INFO - Loader complete
17:36:03.856 [main] INFO - extract conneciton close
17:36:03.856 [main] INFO - loading data commit and connection close
Loading is ended.
17:36:03.856 [main] INFO - Loading is ended.
Elapsed Time (milliseconds) : 438
17:36:03.856 [main] INFO - Elapsed Time (milliseconds) : 438
ADD BATCH : 0
17:36:03.856 [main] INFO - ADD BATCH : 0
EXECUTE BATCH : 0
17:36:03.870 [main] INFO - EXECUTE BATCH : 0
[VERIFICATION] Source Info : app.mig_test : 1002 Rows, Target Info : app.mig_test : 1002 Rows [OK]
2. 데이터 검증
1) db2
$ db2 "select count(*) from app.mig_test"
1
-----------
1002
1 record(s) selected
2) Tibero
SQL> select count(*) from app.mig_test;
COUNT(*)
----------
1002
1 row selected.
5. Error Case
A. failed to check column metadata type 오류 발생
원인 : Metadata Type 체크 에러 발생
해결 : CHECK_COLUMN_METADATA=N 명시
Error occured: failed to check column metadata type - src 0 target :1 17:42:18.969 [main] ERROR - Error occured: failed to check column metadata type - src 0 target :1 java.lang.Exception: failed to check column metadata type - src 0 target :1 at com.m.migrator.TableMigrator.checkColumnMetaData(TableMigrator.java:714) at com.m.migrator.TableMigrator.migrate(TableMigrator.java:394) at com.m.migrator.TableMigrator.main(TableMigrator.java:1759) at com.m.migrator.Main.main(Main.java:15) 17:42:18.969 [main] ERROR - Terminating Extractor threads because of error java.lang.InterruptedException at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireInterruptibly(AbstractQueuedSynchronizer.java:1220) at java.util.concurrent.locks.ReentrantLock.lockInterruptibly(ReentrantLock.java:335) at java.util.concurrent.ArrayBlockingQueue.put(ArrayBlockingQueue.java:350) at com.m.migrator.DataExtractor.run(DataExtractor.java:255)
B. java.lang.NoClassDefFoundError: oracle/sql/STRUCT 에러 발생
원인 : CPL로 이관할 경우 에러 발생
해결 : INSERT_METHOD=DPL 명시
java.lang.NoClassDefFoundError: oracle/sql/STRUCT at com.m.migrator.CPLDataLoader.run(CPLDataLoader.java:502) Caused by: java.lang.ClassNotFoundException: oracle.sql.STRUCT at java.net.URLClassLoader.findClass(URLClassLoader.java:387) at java.lang.ClassLoader.loadClass(ClassLoader.java:418) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:352) at java.lang.ClassLoader.loadClass(ClassLoader.java:351) ... 1 more 17:55:21.233 [main] INFO - loading data commit and connection close java.lang.NullPointerException at com.ibm.db2.jcc.t4.c.b(c.java:1747) at com.ibm.db2.jcc.am.bg.a(bg.java:1085) at com.ibm.db2.jcc.am.bg.W(bg.java:2195) at com.ibm.db2.jcc.am.ResultSet.getClob(ResultSet.java:1453) at com.m.migrator.DataExtractor.run(DataExtractor.java:228) Loading is ended. 17:55:21.233 [main] INFO - Loading is ended. Elapsed Time (milliseconds) : 257 17:55:21.233 [main] INFO - Elapsed Time (milliseconds) : 257 ADD BATCH : 0 17:55:21.233 [main] INFO - ADD BATCH : 0 EXECUTE BATCH : 0 17:55:21.233 [main] INFO - EXECUTE BATCH : 0 [VERIFICATION] Source Info : app.mig_test : 59 Rows, Target Info : app.mig_test : 0 Rows [Not-OK]
C. Invalid operation: Lob is closed 오류 발생
원인 : LOB 데이터를 이관할 경우 에러 발생
해결 : SOURCE_URL 항목에 progressiveStreaming=2; 옵션 추가
ERROR - [jcc][10120][11936][4.33.31] Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null com.ibm.db2.jcc.am.SqlException: [jcc][10120][11936][4.33.31] Invalid operation: Lob is closed. ERRORCODE=-4470, SQLSTATE=null at com.ibm.db2.jcc.am.b4.a(b4.java:794) at com.ibm.db2.jcc.am.b4.a(b4.java:66) at com.ibm.db2.jcc.am.b4.a(b4.java:107) at com.ibm.db2.jcc.am.ck.c(ck.java:67) at com.ibm.db2.jcc.am.ck.q(ck.java:56) at com.ibm.db2.jcc.am.ck.length(ck.java:41) at com.m.migrator.DPLDataLoader.run(DPLDataLoader.java:325) 18:04:13.608 [L0] INFO - Loader finally 18:04:13.609 [L0] INFO - Loader complete 18:04:13.609 [main] WARN - Terminating threads because of error 18:04:13.609 [main] WARN - Breaking main loop 18:04:13.609 [main] INFO - extract conneciton close 18:04:13.609 [main] INFO - loading data commit and connection close Loading is ended. 18:04:13.609 [main] INFO - Loading is ended. Elapsed Time (milliseconds) : 294 18:04:13.609 [main] INFO - Elapsed Time (milliseconds) : 294 ADD BATCH : 0 18:04:13.609 [main] INFO - ADD BATCH : 0 EXECUTE BATCH : 0 18:04:13.609 [main] INFO - EXECUTE BATCH : 0 [VERIFICATION] Source Info : app.mig_test : 1002 Rows, Target Info : app.mig_test : 0 Rows [LOAD_FAIL]
참고
IMS-110762. ACTION NO. 718032
progressive streaming은 result set에서 커서가 다음 row를 가리키기 위해 이동할 때 이전에 참조하던 LOB, XML에 대한 stream을 종료합니다.이로 인해 특정 row를 읽은 이후 모든 작업을 수행해야 하지만, table_migrator에서는 해당 stream을 유지한 상태로 INSERT 시점에 사용하기 때문에 stream이 종료되면 정상적으로 동작하지 않습니다.
따라서 DB2 이관 시 LOB, XML 데이터가 존재하는 경우에는 반드시 해당 설정을 적용해야 합니다. 그 외 다른 Type에 대해서는 영향이 없습니다.