Synchronize DDL operations from a DB2 LUW database to an Oracle-compatible tenant of OceanBase Database

Supported DDL operations

• CREATE TABLE

  Notice

  CREATE TABLE AS SELECT is not supported.

• ALTER TABLE

• DROP TABLE

• TRUNCATE TABLE

• RENAME TABLE

• CREATE INDEX

• DROP INDEX

• Adding or dropping comments on tables or columns

The following DDL operations are supported when you migrate incremental data from DB2 LUW 10.1 to an Oracle-compatible tenant of OceanBase Database V2.2.76 or later:

• CREATE TABLE

• DROP TABLE

• TRUNCATE TABLE

• RENAME TABLE

• ADD COLUMN

• MODIFY COLUMN

Limitations

• CREATE TABLE

  Category	Description
  Syntax restriction	The CREATE LIKE syntax is not supported. A syntax that contains column type aliases (non-native data types) is not supported. A syntax that contains the SELECT clause or subqueries is not supported. An error may be returned if the generated column contains incompatible functions or complex expressions. A syntax that contains the XML data type is not supported.
  Column attribute processing	Supported column attributes are the column name, column type, null or not null, and default value. DDL statements do not support attributes of generated columns or process the definitions of generated columns. Default values with functions must be used with caution. If a function used cannot be executed, the table creation will fail.
  Index processing	We recommend that you explicitly define indexes and constraints, such as the PRIMARY KEY and UNIQUE KEY constraints. We recommend that you do not use CHECK constraints. Anonymous indexes automatically generate names, which may cause name conflicts. Use functions with caution. If a function used cannot be executed, the table creation will fail. Avoid using foreign keys. OMS does not support the synchronization of tables with foreign keys. Foreign keys will be discarded during DDL conversion.
  Partition	OMS supports synchronization of time-based RANGE partitions but directly translates expressions for non-time-based RANGE partitions. We recommend that you do not use functions in partitions. Functions may cause incompatibility of syntax and further cause failure of the migration task. The range of a partition cannot start or end with a data source-related expression such as MINVALUE or MAXVALUE. For partitions of the DB2 LUW database, the ending and exclusive keywords must be specified. Otherwise, an error is returned. The DB2 LUW database does not support the every syntax extension for partitions to prevent calculation errors. In the DB2 LUW database, a partition key is added to the primary key of a table. If the primary key and partition key have no intersection, the primary key is changed to a unique index to adapt to the partition rules and constraints of OceanBase Database. In the DB2 LUW database, the CREATE/ALTER TABLE ADD PARTITION statement must carry partition names. Anonymous partitions are not supported.

• ALTER TABLE

  • The ALTER TABLE ADD PRIMARY syntax of the DB2 LUW database is converted to the ADD UNIQUE syntax of the Oracle-compatible tenant of OceanBase Database. Because OceanBase Database does not support adding a primary key after a table is created, you must explicitly specify the primary key when you create a table.

  • When you execute the ALTER TABLE statement to modify data of the LOB type, you must specify the data length. However, you cannot reduce the length of the LOB-type data by executing the ALTER TABLE statement.

  • The UNIQUE INDEX attribute cannot be null in a DB2 LUW database. Therefore, the column that is specified as the unique key requires a NOT NULL constraint. In other words, when you execute the ALTER TABLE ADD UNIQUE statement, you must specify the NOT NULL constraint for the related column.

• CREATE INDEX

  You cannot specify the Local attribute in the statement to create a regular table in OceanBase Database.

• Parse DDL statements

  When you parse DDL statements, you can refer to the official DB2 database documentation to ensure the maximum compatibility. Note the following limitations when you parse the source DDL statements in a DB2 LUW database:

  • The DB2 LUW database checks the schema to refresh the cached, parsed source DDL statements. We recommend that you only parse DDL statements of one type at a time and proceed after you confirm the results.

  • Avoid frequent CREATE and DROP operations on partitioned tables. We recommend that you execute the CREATE PARTITION TABLE statement, confirm that the table is synchronized, and then execute the DROP PARTITION TABLE statement.

  • The DB2 LUW database can only parse an object whose name consists of letters, underscores (_), and digits, begins with a letter or underscore, and does not contain a DB2 LUW keyword.