Synchronize DDL operations from the Oracle compatible mode 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 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 columns contain incompatible functions or complex expressions. A syntax that contains the XML data type is not supported.
  Column attribute processing	Currently, 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 statements 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 use explicit index definitions, including explicit definitions of the primary key (PK) and unique key (UK). We recommend that you do not use CHECK. 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. We recommend that you do not use foreign keys. At present, OMS does not support the synchronization of tables with foreign keys. 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 may lead to 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 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 and partition key cannot be intersected. Otherwise, the primary key will be 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 will be converted into the ADD UNIQUE statement in the Oracle compatible mode of OceanBase Database. Since 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.

  • 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 use the ALTER TABLE statement to reduce the length of a LOB column.

  • The UNIQUE INDEX attribute cannot be null in a DB2 LUW database. Therefore, when you create a unique key (UK) column, you must set a non-null constraint for the column. In other words, when you use the ALTER TABLE ADD UNIQUE statement, you must set a non-null constraint for the involved column.

• CREATE INDEX

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

• DDL parsing

  Refer to the official DB2 documentation for executing DDL statements to ensure maximum compatibility. DB2 LUW has the following limitations on parsing source DDL statements:

  • DB2 LUW refreshes the cached parsed results of DDL statements 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 are correctly parsed.

  • We recommend that you do not frequently perform the CREATE and DROP operations on partitioned tables. Instead, you can first use the CREATE PARTITION TABLE statement to create partitions and then use the DROP PARTITION TABLE statement to drop partitions, after you have confirmed that the partitions are synchronized correctly.

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