Document Type | Technical Information
Category | Migration
Applicable Product Version | Tibero7.2.4
Document Number | TMITI036
Overview
This document describes the procedure to migrate DB2 data to Tibero using Table Migrator.
Environment Configuration
- DB2 : 11.5.9.0
- Tibero : 7.2.4
- Table migrator : 305455
Method
1. Confirm Migration Target
DB2 has a structure where schema and OS user accounts are directly linked.
In DB2, a schema is automatically created when an OS user account with the same name connects to the database, and schema ownership is attributed to that account.
When migrating to Tibero, objects must be mapped on a schema basis. Mapping based on OS accounts may cause object lookup errors or access permission errors.
# 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) Version
$ 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) Size by schema
$ 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) Table list
$ 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) Table information
$ 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 Migration
Before migrating data, create the same structure (DDL) in Tibero as in DB2.
1. DB2
1) Script dump
$ 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) Check script
$ 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) Create table in 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.
Note
DB2 schema.table level dump
db2look -d <DB name> -t <table name> -z <schema name> -e -o <output file.sql>
DB2 schema level dump
db2look -d <DB name> -z <schema name> -e -o <output file.sql>โป DDL conversion needed to fit Tibero
Note
DB2/ Tibero Data Type Comparison
Category DB2 Tibero Remarks Number SMALLINT NUMBER(5) Small integer INTEGER / INT NUMBER(10) Most common integer BIGINT NUMBER(19) Large integer DECIMAL(p,s) / NUMERIC(p,s) NUMBER(p,s) Precision can be specified IDENTITY (auto increment) NUMBER + SEQUENCE AUTO_INCREMENT must be manually configured Character CHAR(n) CHAR(n) Fixed length string VARCHAR(n) VARCHAR2(n) Variable length string CLOB CLOB Large string, LOB type Date/Time DATE DATE Stores only date TIMESTAMP TIMESTAMP Date + time Boolean BOOLEAN CHAR(1) or NUMBER(1) No Boolean in Tibero; implemented as 0/1 or Y/N Binary Data / LOB BLOB BLOB Stores binary data URL: https://www.ibm.com/docs/en/db2/11.5.x?topic=elements-data-types&utm_source reference
3. Table Migrator Configuration
Prepare the DB2 JDBC Driver and copy it to the Table Migrator folder.
1. Prepare JDBC (located at /opt/ibm/db2/V11.5/java) $ cp /opt/ibm/db2/V11.5/java/db2jcc4.jar /mig/ 2. Modify 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. Environment settings $ 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 # Other parameters should be set according to site conditions
4. Data Migration
Migrate DB2 data to Tibero using Table Migrator.
After migration, perform data verification by comparing logs and table row counts.
1. Run 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. Data Verification
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 Cases
A. failed to check column metadata type error occurs
Cause: Error occurs when checking Metadata Type
Solution: Specify 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 error occurs
Cause: Error occurs when migrating with CPL
Solution: Specify 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 error occurs
Cause: Error occurs when migrating LOB data
Solution: Add progressiveStreaming=2; option to SOURCE_URL
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]
Note
IMS-110762. ACTION NO. 718032
Progressive streaming closes the stream for LOB, XML that was previously referenced when the cursor moves to the next row in the result set.Because all operations must be done after reading a specific row, but table_migrator uses the stream at the insert point while maintaining it, if the stream is closed, it does not work properly.
Therefore, when migrating DB2 with LOB or XML data, this setting must be applied. There is no effect on other types.