Dynamic DDL operations during data migration between an Oracle tenant of OceanBase Database and a DB2 LUW database

Supported DDL operations

• CREATE TABLE

• 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 tenant of OceanBase Database V2.2.76 or later:

• CREATE TABLE

• DROP TABLE

• TRUNCATE TABLE

• RENAME TABLE

• ADD COLUMN

• MODIFY COLUMN

Limits

• 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 and do not process the definitions of generated columns. Default values with functions should 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, including primary keys, unique keys, and CHECK constraints. CHECK constraints are not preferred. Anonymous indexes automatically generate names, which may cause name conflicts. Functions should be used with caution. If a function used cannot be executed, the table creation will fail. Foreign keys are not recommended. OMS does not support 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 project. The range of a partition cannot start or end with a representation related to MINVALUE or MAXVALUE. Partitions of the DB2 LUW database must contain ending with the exclusive attribute. 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 column is added to the primary key of a table. If the primary key and partitioning 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 statement in a DB2 LUW database is converted to the ADD UNIQUE statement in an Oracle tenant of OceanBase Database. OceanBase Database does not support adding a primary key after a table is created. Therefore, 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 limits 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 one type of DDL statements 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.

  • If you enable DDL operations in incremental migration, the DROP INDEX command is executed on all indexes, which may cause index loss in the destination database.