DBCAT is a lightweight command-line tool that can be used to convert DDL statements between databases and compare table schemas. This topic describes how to use DBCAT to migrate table schemas.
The DBCAT installation package is named in the format of dbcat-[version number]-SNAPSHOT.tar.gz. After you download the installation package, decompress the package. The name of the executable file is dbcat.
Notice
DBCAT is a component of OceanBase Migration Service (OMS). You can obtain the installation package from OceanBase Technical Support.
Prepare the installation environment
DBCAT can run in CentOS, macOS, and Windows. You must install JDK 1.8 or later. You can also use OpenJDK. After the installation, configure the environment variable JAVA_HOME.
Take the installation of OpenJDK in CentOS as an example:
$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 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
Directories in the installation file:
| Directory | Description |
|---|---|
| bin/ | The directory of the executable files. |
| conf | The log file configuration directory. |
| lib | The directory of runtime dependent packages. |
| meta | Specifies to export dictionary table data in offline conversion scenarios. |
| ~/output | The SQL files and report files generated in runtime. |
Export table schemas from a MySQL database
DBCAT provides the online conversion feature, which allows the tool to directly connect to the source database and export objects in the database. When a large number of objects exist in the database, for example, more than 10,000, the export can be slow.
Run the following DBCAT export command:
bin/dbcat convert -H<host> -P<port> -u<user> -p<password> -D<database> --schema <schema> --from <from> --to <to> --all
You can run the bin/dbcat help convert command to view more parameters.
Required parameters:
| Parameter | Variables involved | Description | |
|---|---|---|---|
| -H/--host | Y | The host on which the database is located. | |
| -P/--port | Y | The TCP/IP port number for connection. | |
| -u or --user | Y | The logon username of the database. | |
| -t or --tenant | Y | The logon username of the database. | |
| -c/--cluster | Y | The name of the OceanBase cluster. | |
| -p or --password | Y | The logon password of the database. | |
| -D or --database | Y | The name of the source database. The database name and the schema name are different for a Db2 database. | |
| --service-id | Y | The service ID for connecting to Oracle Database. | |
| --service-name | Y | The service name for connecting to Oracle Database. | |
| --as-sysdba | N | The sysdba role for connecting to Oracle Database. | |
| --sys-user | Y | The username for connecting to the sys tenant of the OceanBase cluster. | |
| --sys-password | Y | The password for connecting to the sys tenant of the OceanBase cluster. | |
| --schema | Y | The schema name of the source 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 | All database objects. Default value: TABLE, VIEW. |
Optional parameters:
| Parameter | Variables involved | Description | |
|---|---|---|---|
| -f/--file | Y | The output path of SQL files. | |
| --offline | N | Specifies to use the offline mode. | |
| --target-schema | Y | The schema name of the destination database. | |
| --table | Y | The tables to export. | |
| --view | Y | The views to export. | |
| --trigger | Y | The triggers to export. | |
| --synonym | Y | The synonyms to export. | |
| --sequence | Y | The sequences to export. | |
| --function | Y | The functions to export. | |
| --procedure | Y | The stored procedures to export. | |
| --dblink | Y | The database links to export. | |
| --type | Y | The types to export. | |
| --type-body | Y | The type bodies to export. | |
| --package | Y | The packages to export. | |
| --package-body | Y | The package bodies to export. | |
| --no-quote | N | Specifies to generate DDL statements without quotation marks. | |
| --no-schema | N | Specifies to generate DDL statements without schema names. | |
| --target-schema | Y | Specifies to use a specified schema name in the generated DDL statements. | |
| --exclude-type | Y | This parameter is used with the --all option. For example, --all --exclude-type 'TABLE' specifies to exclude the TABLE type. |
The following example exports the schemas of all objects from the TESTDB database in DB2 LUW 11.5.x and migrates the schemas to a MySQL tenant in a cluster 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
Notice:
You do not need to install DBCAT on the database server. You can install it on a server that can be directly connected to the database server.
The
-- fromand-- toparameters specify the types and versions of the source and destination databases. The source and destination database types supported by DBCAT are as follows:Source database type Destination database type 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 OBMYSQL indicates a MySQL tenant of OceanBase Database, and OBORACLE indicates an Oracle tenant of OceanBase Database.
The following table describes the versions of the supported source and destination database types.
Database type Version TiDB tidb4
tidb5PG pgsql10 SYBASE sybase15 DB2 IBM i db2ibmi71 DB2 LUW db2luw970
db2luw1010
db2luw1050
db2luw111
db2luw115MYSQL mysql56
mysql57
mysql</80>ORACLE oracle9i
oracle10g
oracle11g
oracle12c
oracle18c
oracle19cOBMYSQL obmysql14x
obmysql21x
obmysql22x
obmysql200
obmysql211
obmysql2210
obmysql2230
obmysql2250
obmysql2271 ~ obmysql2277
obmysql30x
obmysql31x
obmysql32x
obmysql322
obmysql40OBORACLE oboracle2220
oboracle2230
oboracle2250
oboracle2270 ~ oboracle2277
oboracle21x
oboracle22x
oborcle30x
oboracle31x
oboracle32x
oboracle322
oboracle40
The output files are stored in the output folder of the home directory of the user.
$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
DBCAT exports SQL files. You can use the import feature of OceanBase Developer Center (ODC) to import table schemas to OceanBase Database in batches. For more information, see Batch export and import.
To import table schemas to a MySQL tenant of OceanBase Database, you can also use the source command. For example:
obclient [test]> source TABLE-schema.sql
Query OK, 0 rows affected (0.044 sec)
Notice
If the SQL files are not in the current directory, you must use an absolute address.
Result verification
Example: View the writing mode of a table schema in DB2 LUW and the table schema in OceanBase Database.
View the table creation SQL statement of 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 fields 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.