Synchronize DDL operations from the MySQL 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

• Adding or dropping comments on tables or columns

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 column contains 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 statements do not process the definitions of generated columns. Default values with functions should be used with caution. If a function 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 indexes. Anonymous indexes will automatically generate names, which may cause naming conflicts. Use functions with caution. If a function 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. The foreign keys will be discarded during DDL conversion.
  Partition	Only time-based RANGE partitions can be synchronized. For non-time-based RANGE partitions, expressions 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 of a table must contain the ending and exclusive keywords in its partition key. 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 defined on the same column as the partition key. If the primary key and the partition key have no intersection, the primary key is changed into a unique index to adapt to the partitioning 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 into the ADD UNIQUE statement in the MySQL compatible mode 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.

  • If you want to modify a LOB column by using the ALTER TABLE statement, you must specify the length of the LOB column. However, you cannot decrease 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 add a unique key (UK) column, the UK column must have a NOT NULL constraint. In other words, when you execute the ALTER TABLE ADD UNIQUE statement, the involved column must have a NOT NULL constraint.

• CREATE INDEX

  The Local syntax is not supported for a regular table in OceanBase Database.

• DDL parsing

  Refer to the official DB2 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 are parsed correctly.

  • We recommend that you do not frequently perform the CREATE and DROP operations on partitioned tables. Instead, you should execute the CREATE PARTITION TABLE statement, confirm that the partitioned table is synchronized, and then execute the DROP PARTITION TABLE statement.

  • In a DB2 LUW database, an object name that is parsed must be composed of letters, underscores (_), and digits, must start with a letter or underscore, and must not be a DB2 LUW keyword.