Use DBCAT to migrate table schemas from a DB2 LUW database to OceanBase Database|V4.2.5| docs|Distributed Database

Use DBCAT to migrate table schemas from a DB2 LUW database to OceanBase Database

Last Updated：2025-01-02 01:58:39 Updated

DBCAT is a lightweight command-line tool that provides features such as DDL conversion and schema comparison between databases. This topic describes how to use DBCAT to migrate schemas.

The DBCAT installation package is named in the dbcat-[version number]-SNAPSHOT.tar.gz format. After you download and decompress the installation package, you can directly use it. The executable file is named dbcat.

Notice

DBCAT is a component of OceanBase Migration Service (OMS), and it is recommended to use OMS for exporting in the community edition environment.

Prepare the installation environment

DBCAT can run only on CentOS, macOS, and Windows operating systems. You must first install JDK 1.8 or later. You can also use OpenJDK. Then, specify the environment variable JAVA_HOME.

Here is an example of how to install OpenJDK in a CentOS operating system:

$sudo yum -y install java-1.8.0-openjdk.x86_64

$which java
/usr/local/java/jdk1.8.0_261/bin/java

echo 'export JAVA_HOME=/usr/local/java/jdk1.8.0_261/' >> ~/.bash_profile
. ~/.bash_profile

Decompress the installation package:

tar zxvf dbcat-1.8.0-SNAPSHOT.tar.gz
cd dbcat-1.8.0-SNAPSHOT/
chmod +x bin/dbcat

$tree -L 3 --filelimit 30
.
├── bin
│   ├── dbcat
│   ├── dbcat.bat
│   └── dbcat-debug
├── conf
│   ├── dbcat.properties
│   └── logback.xml
├── docs
│   ├── README.docx
│   ├── README.md
│   └── README.txt
├── LEGAL.md
├── lib [45 entries exceeds filelimit, not opening dir]
├── LICENSE
├── meta
│   └── README
└── NOTICE

5 directories, 12 files

The following table describes the directories in the installation package.

Directory name	Description
bin	The directory where the executable file is located.
conf	The directory where log files are located.
lib	The directory where the libraries required for running are located.
meta	The directory to which dictionary table data is exported in offline conversion scenarios.
~/output	The directory where the SQL file and report file are located. This directory is generated during the running of DBCAT.

Export schemas from the DB2 LUW database

DBCAT supports online conversion. In other words, DBCAT can directly connect to the source database and export the objects from the database. If you have too many objects (such as 10,000 objects), the export may be slow.

Here is the export command of DBCAT:

bin/dbcat convert -H<host> -P<port> -u<user> -p<******>  -D<database> --schema <schema> --from <from> --to <to> --all

You can run the bin/dbcat help convert command to learn more about the command options.

The required options are described as follows:

Option	Has parameters	Description
-H or --host	Y	The IP address of the database server.
-P or --port	Y	The port number of the database server.
-u or --user	Y	The username used to log in to the database.
-t or --tenant	Y	The name of the OceanBase tenant.
-c or --cluster	Y	The name of the OceanBase cluster.
-p or --password	Y	The password used to log in to the database.
-D or --database	Y	The name of the source database. If the source database is a DB2 database, the database name and mode name must be different.
--service-id	Y	The service ID for connecting to the Oracle database.
--service-name	Y	The service name for connecting to the Oracle database.
--as-sysdba	N	Specifies to log in to the Oracle database as the sysdba role.
--sys-user	Y	The username for logging on to the sys tenant of the OceanBase cluster.
--sys-password	Y	The password for logging on to the sys tenant of the OceanBase cluster.
--schema	Y	The schema name of the source database. If the source database is not a DB2 database, the schema name is the same as the database name.
--from	Y	The type of the source database.
--to	Y	The type of the destination database.
--all	N	Specifies to export all database objects.

The optional options are as follows:

Option	Has parameters	Description
-f or --file	Y	The output path of the SQL file.
--offline	N	Specifies to use the offline mode.
--target-schema	Y	The mode name of the destination database.
--table	Y	The table to be exported.
--view	Y	The view to be exported.
--trigger	Y	The trigger to be exported.
--synonym	Y	The synonym to be exported.
--sequence	Y	The sequence to be exported.
--function	Y	The function to be exported.
--procedure	Y	The procedure to be exported.
--dblink	Y	The DBLink to be exported.
--type	Y	The type of objects to be exported.
--type-body	Y	The type body of objects to be exported.
--package	Y	The package to be exported.
--package-body	Y	The package body to be exported.
--no-quote	N	Specifies to generate DDL statements without quotation marks.
--no-schema	N	Specifies to generate DDL statements without schema name.
--target-schema	Y	Specifies to use the specified schema name for the generated DDL statements.
--exclude-type	Y	Specifies to exclude the specified type of objects from the export. You can use this option together with the --all option. For example, --all --exclude-type 'TABLE' specifies to exclude the TABLE type.

The following example shows how to export the schemas of all objects from the TESTDB database in DB2 LUW 11.5.x and migrate the schemas to a MySQL tenant of OceanBase Database V4.0.0.

bin/dbcat convert -H xxx.xxx.xxx.xxx -P 50001 -udb2inst2 -p****** --schema TESTDB -DTESTDB --table bmsql_customer --from db2luw115 --to obmysql32x --all

Some notes about this:

You do not need to directly install DBCAT on the database host. Instead, you can install it on a host that can directly connect to the database host.

In the command, the --from and --to options respectively specify the source and destination database types, which must contain the database version number. The following table describes the source and destination databases supported by DBCAT.

Source database	Destination database
TiDB	OBMYSQL
PG	OBMYSQL
SYBASE	OBORACLE
MYSQL	OBMYSQL
ORACLE	OBORACLE
ORACLE	OBMYSQL
DB2 IBM i	OBORACLE
DB2 LUW	OBORACLE
DB2 LUW	OBMYSQL
OBMYSQL	MYSQL
OBORACLE	ORACLE

In the preceding table, OBMYSQL indicates MySQL tenants of OceanBase Database, and OBORACLE indicates Oracle tenants of OceanBase Database.

The following table describes the versions of supported source and destination databases.

Database	Version
TiDB	tidb4 tidb5
PG	pgsql10
SYBASE	sybase15
DB2 IBM i	db2ibmi71
DB2 LUW	db2luw970 db2luw1010 db2luw1050 db2luw111 db2luw115
MYSQL	mysql56 mysql57 mysql</80>
ORACLE	oracle9i oracle10g oracle11g oracle12c oracle18c oracle19c
OBMYSQL	obmysql14x obmysql21x obmysql22x obmysql200 obmysql211 obmysql2210 obmysql2230 obmysql2250 obmysql2271 ~ obmysql2277 obmysql30x obmysql31x obmysql32x obmysql322 obmysql40
OBORACLE	oboracle2220 oboracle2230 oboracle2250 oboracle2270 ~ oboracle2277 oboracle21x oboracle22x oboracle30x oboracle31x oboracle32x oboracle322 oboracle40

The files generated after the command is executed are stored in the output directory under the home directory.

$tree ~/output/dbcat-20xx-xx-xx-164533/
/home/qing.meiq/output/dbcat-20xx-xx-xx-164533/
├── tpccdb
│   └── TABLE-schema.sql
└── tpccdb-conversion.html

1 directory, 2 files

Import table schemas to OceanBase Database

The file of data exported by using DBCAT is an SQL file. Here you can use the import function of OceanBase Developer Center (ODC) to import the schemas to the OceanBase database. For more information, see Batch export and import.

You can also use the source command to import data from the SQL file into the OceanBase database. Here is an example:

obclient [test]> source TABLE-schema.sql
Query OK, 0 rows affected (0.044 sec)

Notice

If the SQL file is not in the current directory, the absolute path of the SQL file is required.

Result verification

Example: View the schema of a table in DB2 LUW and OceanBase database.

View the SQL statement for creating the bmsql_customer table in the source DB2 LUW database:

db2 => describe table  bmsql_customer

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
C_W_ID                          SYSIBM    BIGINT                       8     0 No
C_D_ID                          SYSIBM    BIGINT                       8     0 No
C_ID                            SYSIBM    BIGINT                       8     0 No
C_DISCOUNT                      SYSIBM    DECIMAL                      4     4 Yes
C_CREDIT                        SYSIBM    CHARACTER                    2     0 Yes
C_LAST                          SYSIBM    VARCHAR                     16     0 Yes
C_FIRST                         SYSIBM    VARCHAR                     16     0 Yes
C_CREDIT_LIM                    SYSIBM    DECIMAL                     12     2 Yes
C_BALANCE                       SYSIBM    DECIMAL                     12     2 Yes
C_YTD_PAYMENT                   SYSIBM    DECIMAL                     12     2 Yes
C_PAYMENT_CNT                   SYSIBM    INTEGER                      4     0 Yes
C_DELIVERY_CNT                  SYSIBM    INTEGER                      4     0 Yes
C_STREET_1                      SYSIBM    VARCHAR                     20     0 Yes
C_STREET_2                      SYSIBM    VARCHAR                     20     0 Yes
C_CITY                          SYSIBM    VARCHAR                     20     0 Yes
C_STATE                         SYSIBM    CHARACTER                    2     0 Yes
C_ZIP                           SYSIBM    CHARACTER                    9     0 Yes
C_PHONE                         SYSIBM    CHARACTER                   16     0 Yes
C_SINCE                         SYSIBM    TIMESTAMP                   10     6 Yes
C_MIDDLE                        SYSIBM    CHARACTER                    2     0 Yes
C_DATA                          SYSIBM    VARCHAR                    500     0 Yes

  21 record(s) selected.

View the schema of the bmsql_customer table in the destination OceanBase database.

obclient [test]> desc bmsql_customer;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| c_w_id         | bigint(20)    | NO   | PRI | NULL    |       |
| c_d_id         | bigint(20)    | NO   | PRI | NULL    |       |
| c_id           | bigint(20)    | NO   | PRI | NULL    |       |
| c_discount     | decimal(4,4)  | YES  |     | NULL    |       |
| c_credit       | char(2)       | YES  |     | NULL    |       |
| c_last         | varchar(16)   | YES  |     | NULL    |       |
| c_first        | varchar(16)   | YES  |     | NULL    |       |
| c_credit_lim   | decimal(12,2) | YES  |     | NULL    |       |
| c_balance      | decimal(12,2) | YES  |     | NULL    |       |
| c_ytd_payment  | decimal(12,2) | YES  |     | NULL    |       |
| c_payment_cnt  | int(11)       | YES  |     | NULL    |       |
| c_delivery_cnt | int(11)       | YES  |     | NULL    |       |
| c_street_1     | varchar(20)   | YES  |     | NULL    |       |
| c_street_2     | varchar(20)   | YES  |     | NULL    |       |
| c_city         | varchar(20)   | YES  |     | NULL    |       |
| c_state        | char(2)       | YES  |     | NULL    |       |
| c_zip          | char(9)       | YES  |     | NULL    |       |
| c_phone        | char(16)      | YES  |     | NULL    |       |
| c_since        | timestamp     | YES  |     | NULL    |       |
| c_middle       | char(2)       | YES  |     | NULL    |       |
| c_data         | varchar(500)  | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
21 rows in set (0.007 sec)

After the table schemas are imported to OceanBase Database, the character types and lengths of table fields may change. For more information about table field conversion, see the data type mappings in Create a project to migrate data from a DB2 LUW database to an Oracle tenant of OceanBase Database.