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	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 primary keys (PKs) and unique keys (UKs), and avoid using CHECK constraints. Anonymous indexes will automatically generate names, which may cause name 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. OMS does not support the synchronization of tables with foreign keys, and foreign keys will be discarded during DDL conversion.
  Partition	Only time-based RANGE partitioning is supported. 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 is 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 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 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 into the ADD UNIQUE statement in the Oracle compatible mode of OceanBase Database. OceanBase Database does not support the addition of a primary key. 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 use the ALTER TABLE statement to reduce the length of a LOB column.

  • The UNIQUE INDEX column in a DB2 LUW database cannot be empty. Therefore, when you use the ALTER TABLE ADD UNIQUE statement, the involved columns cannot be null.

• CREATE INDEX

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

• DDL parsing

  Refer to the DB2 documentation to execute 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, DDL statements are cached and 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 correctly parsed.

  • For partitioned tables, we recommend that you avoid frequent CREATE and DROP operations. We recommend that you use the CREATE PARTITION TABLE statement, and then, after the partition is synchronized, use 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 cannot be a DB2 LUW keyword.