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 table structures.
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.
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 table structures 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 schema name must be distinguished. |
| --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 AI Database cluster. |
| --sys-password | Y | The password for logging on to the sys tenant of the OceanBase AI Database 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. |
| --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 | N | The function to be exported. |
| --procedure | N | 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 | The schema name of the destination database to be used in 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 structures of all objects from the test database in DB2 LUW 11.5.x and migrate them to a user tenant of OceanBase AI Database.
bin/dbcat convert -H xxx.xxx.xxx.xxx -P 50001 -udb2inst2 -p****** --schema TESTDB -DTESTDB --table bmsql_customer --from db2luw115 --to obmysql40 --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
--fromand--tooptions 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 databaseDestination databaseTiDB OBMYSQL PG OBMYSQL MYSQL OBMYSQL ORACLE OBMYSQL DB2 LUW OBMYSQL OBMYSQL MYSQL In the preceding table, OBMYSQL indicates a user tenant of OceanBase AI Database.
The following table describes the versions of supported source and destination databases.
DatabaseVersionTiDB 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
obmysql40
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 structures to OceanBase AI Database
The file exported by DBCAT is an SQL file. You can use the import feature of OceanBase Developer Center (ODC) to import the table structures to OceanBase AI Database in batches.
You can also use the source command to import data from the SQL file into OceanBase AI 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.
Verify the data import result
Example: View the table structure of a table in DB2 LUW and in OceanBase AI Database.
View the table structure 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 table structure of the bmsql_customer table in the destination OceanBase AI 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 structures are imported to OceanBase AI Database, the character types and lengths of the table fields may change. For more information about table field conversion, see the data type mappings in Migrate data from a DB2 LUW database to a user tenant of OceanBase AI Database.