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

Supported DDL operations for synchronization

• CREATE TABLE

  Notice

  CREATE TABLE AS SELECT is not supported.

• ALTER TABLE

• DROP TABLE

• TRUNCATE TABLE

• RENAME TABLE

• CREATE INDEX

• DROP INDEX

Limitations on DDL synchronization

• CREATE TABLE

  Category	Description
  Syntax limitations	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 columns contain incompatible functions or complex expressions. A syntax that contains the XML data type is not supported.
  Column attribute processing	At present, the values of column attributes, such as column name, column type, NULL or NOT NULL, and default value, are paid attention to. Generated columns are not supported. DDL does not process the definitions of generated columns. Take caution with default values that contain functions. If the functions cannot be executed, the table creation will fail.
  Index processing	We recommend that you use explicit index definitions and add explicit PRIMARY KEY, UNIQUE KEY, and CHECK constraints. Do not use CHECK constraints. Anonymous indexes automatically generate names, which may cause name conflicts. Take caution with functions. If the functions cannot be executed, the table creation will fail. We recommend that you do not use foreign keys. OMS does not support the synchronization of tables with foreign keys. The foreign keys will be discarded during DDL conversion.
  Partition (Partition)	Only time-based RANGE partitioning is supported. For non-time-based RANGE partitions, the expression will be directly shifted. We recommend that you do not use functions in partitions. Functions may cause incompatibility of syntax, which will lead to the failure of the migration task. The range of a partition cannot start or end with data source-related expressions such as MINVALUE or MAXVALUE. For a DB2 LUW database, a partition must contain the ending and exclusive keywords. Otherwise, an error will be returned. The DB2 LUW database does not support the every syntax extension for partitions to prevent calculation errors. In a DB2 LUW database, the primary key of a table is added to the partitioning column. If the primary key and the 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 a 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-compatible tenant of OceanBase Database. This is because 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.

  • If you want to use the ALTER TABLE statement to modify a LOB column, you must specify the length of the LOB column. However, you cannot reduce the length of a LOB column by using the ALTER TABLE statement.

  • The UNIQUE INDEX attribute cannot be null in a DB2 LUW database. Therefore, when you use the ALTER TABLE ADD UNIQUE statement, the involved columns must have the NOT NULL constraint.

• CREATE INDEX

  The Local syntax is not supported for normal tables in OceanBase Database.

• DDL parsing

  Refer to the DB2 official documentation for executing DDL statements to ensure maximum compatibility. Note the following limitations when you parse DDL statements in a DB2 LUW database:

  • In the DB2 LUW database, the DDL cache is refreshed by querying the schema. We recommend that you parse only one type of DDL statements at a time and proceed to the next type only after you have confirmed that the current type of DDL statements is parsed correctly.

  • 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 parse only objects whose names conform to the following rules: the name must be case-sensitive and must start with a letter or underscore (_) and contain only letters, underscores, and digits. The name of the object cannot be a DB2 LUW keyword.