Skip to content

Replication DDL LEVEL#

Overview#

DDL statements that can be executed on replication target tables are classified into DDL Level 0, DDL Level 1, and Other Statements.

Each level requires different property settings and allows different operations, so check which level a DDL statement belongs to before executing it.

This document lists the DDL statements available at each level, with SQL syntax examples.

Version#

  • Altibase 7 or later

DDL Levels at a Glance#

Category Required Settings Available Operations
DDL Level 0 - REPLICATION_DDL_ENABLE = 1 - Add/drop columns without constraints
- Set/drop column default values
- Change tablespaces
- TRUNCATE
- Create/drop regular indexes
DDL Level 1 - REPLICATION_DDL_ENABLE = 1
- REPLICATION_DDL_ENABLE_LEVEL = 1
- REPLICATION_SQL_APPLY_ENABLE = 1 (receiver side)
- Add/modify/drop columns with constraints
- Split/merge/drop partitions
- Add/modify/drop table constraints
- Create/drop unique key and function-based indexes
Other Statements - Not required - Index AGING
- COMPACT
- Index partition rebuild
- GRANT/REVOKE
- Create/drop triggers

Note

DDL Level 1 requires SQL apply mode (REPLICATION_SQL_APPLY_ENABLE) to be enabled on the receiver side.

Other statements can be executed at any time, regardless of the REPLICATION_DDL_ENABLE setting.

DDL Level 0#

Adding Columns without Constraints#

Compressed columns, foreign key columns, and columns with constraints are excluded.

Operation SQL Syntax Example
Add a regular column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE );

Dropping Columns without Constraints#

Compressed columns, PK columns, columns with constraints, and columns used in function-based indexes are excluded.

Operation SQL Syntax Example
Drop a regular column ALTER TABLE table_name DROP COLUMN column_name;

Column Default Values#

Operation SQL Syntax Example
Set a default value ALTER TABLE table_name ALTER COLUMN ( column_name SET DEFAULT value );
Drop a default value ALTER TABLE table_name ALTER COLUMN ( column_name DROP DEFAULT );

Changing Tablespaces#

Operation SQL Syntax Example
Change the tablespace of a table ALTER TABLE table_name ALTER TABLESPACE tablespace_name;
Change the tablespace of a partition ALTER TABLE table_name ALTER PARTITION partition_name TABLESPACE tablespace_name;

Deleting All Data#

Tables that contain compressed columns are excluded.

Operation SQL Syntax Example
Truncate a partition ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Truncate a table TRUNCATE TABLE table_name;

Creating and Dropping Indexes#

Unique key indexes and function-based indexes are excluded. (They belong to DDL Level 1.)

Operation SQL Syntax Example
Create a regular index CREATE INDEX index_name ON table_name ( column_name );
Drop a regular index DROP INDEX index_name;

DDL Level 1#

CAUTION

DDL Level 1 requires SQL apply mode (REPLICATION_SQL_APPLY_ENABLE = 1) to be enabled on the receiver side.

Adding and Modifying Columns#

Operation SQL Syntax Example
Add a NOT NULL column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE NOT NULL );
Add a UNIQUE column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE UNIQUE );
Add a LOCALUNIQUE column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE LOCALUNIQUE );
Change a NOT NULL constraint ALTER TABLE table_name ALTER COLUMN ( column_name NOT NULL );
ALTER TABLE table_name ALTER COLUMN ( column_name NULL );
ALTER TABLE table_name MODIFY COLUMN ( column_name NOT NULL );
ALTER TABLE table_name MODIFY COLUMN ( column_name NULL );
Change the data type ALTER TABLE table_name MODIFY COLUMN ( column_name DATA_TYPE );

Dropping Columns#

PK columns and compressed columns are excluded.

Operation SQL Syntax Example
Drop a column with constraints
Drop a column used in a function-based index
ALTER TABLE table_name DROP COLUMN column_name;

Partition Operations#

CAUTION

  • Replication must be stopped on both servers before execution.
  • The partition names used in the DDL statement must be the same on the local and remote servers.
  • Newly created partitions are automatically added to the replication targets.
  • Dropped partitions are automatically removed from the replication targets.
Operation SQL Syntax Example Remarks
Split a partition ALTER TABLE table_name SPLIT PARTITION .....;
Merge partitions ALTER TABLE table_name MERGE PARTITIONS .....; All target partitions must be replication targets
Drop a partition ALTER TABLE table_name DROP PARTITION partition_name; Available only when there are two or more partitions

Table Constraints#

Operation SQL Syntax Example
Add a UNIQUE constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE ( column_name );
Add a LOCALUNIQUE constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name LOCALUNIQUE ( column_name );
Add a CHECK constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( check_condition );
Rename a constraint ALTER TABLE table_name RENAME CONSTRAINT constraint_name TO constraint_name;
Drop a constraint ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Indexes#

Operation SQL Syntax Example
Create a unique key index CREATE UNIQUE INDEX index_name ON table_name ( column_name );
Create a function-based index CREATE INDEX index_name ON table_name ( expression );
Drop an index (unique key / function-based) DROP INDEX index_name;

Other Statements#

These statements can be executed at any time, regardless of the REPLICATION_DDL_ENABLE setting.

Category Operation SQL Syntax Example
Disk index Index AGING ALTER INDEX index_name AGING;
Memory/volatile table and partition Table COMPACT ALTER TABLE table_name COMPACT;
Memory/volatile table and partition Partition COMPACT ALTER TABLE table_name COMPACT PARTITION partition_name;
Index partition Rebuild an index partition ALTER INDEX REBUILD PARTITION index_partition_name;
Privilege Grant a privilege GRANT OBJECT ...;
Privilege Revoke a privilege REVOKE OBJECT ...;
Trigger Create a trigger CREATE TRIGGER ...;
Trigger Drop a trigger DROP TRIGGER ...;

Summary#

Level Operation SQL Syntax Example Exclusions and Cautions
DDL Level 0 Add a column without constraints ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE ); - Excludes compressed, foreign key, and constrained columns
DDL Level 0 Drop a column without constraints ALTER TABLE table_name DROP COLUMN column_name; - Excludes compressed, PK, constrained, and function-based index columns
DDL Level 0 Set a column default value ALTER TABLE table_name ALTER COLUMN ( column_name SET DEFAULT value );
DDL Level 0 Drop a column default value ALTER TABLE table_name ALTER COLUMN ( column_name DROP DEFAULT );
DDL Level 0 Change the tablespace of a table ALTER TABLE table_name ALTER TABLESPACE tablespace_name;
DDL Level 0 Change the tablespace of a partition ALTER TABLE table_name ALTER PARTITION partition_name TABLESPACE tablespace_name;
DDL Level 0 Truncate a partition ALTER TABLE table_name TRUNCATE PARTITION partition_name; - Excludes tables with compressed columns
DDL Level 0 Truncate a table TRUNCATE TABLE table_name; - Excludes tables with compressed columns
DDL Level 0 Create a regular index CREATE INDEX index_name ON table_name ( column_name ); - Excludes unique key and function-based indexes
DDL Level 0 Drop a regular index DROP INDEX index_name; - Excludes unique key and function-based indexes
DDL Level 1 Add a NOT NULL column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE NOT NULL );
DDL Level 1 Add a UNIQUE column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE UNIQUE );
DDL Level 1 Add a LOCALUNIQUE column ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE LOCALUNIQUE );
DDL Level 1 Change a NOT NULL constraint ALTER TABLE table_name ALTER COLUMN ( column_name NOT NULL );
ALTER TABLE table_name MODIFY COLUMN ( column_name NULL ); etc.
DDL Level 1 Change the data type ALTER TABLE table_name MODIFY COLUMN ( column_name DATA_TYPE );
DDL Level 1 Drop a column with constraints
Drop a column used in a function-based index
ALTER TABLE table_name DROP COLUMN column_name; - Excludes PK and compressed columns
DDL Level 1 Split a partition ALTER TABLE table_name SPLIT PARTITION .....; - Partition names must match on both servers
DDL Level 1 Merge partitions ALTER TABLE table_name MERGE PARTITIONS .....; - All target partitions must be replication targets
DDL Level 1 Drop a partition ALTER TABLE table_name DROP PARTITION partition_name; - Requires two or more partitions
DDL Level 1 Add a UNIQUE constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE ( column_name );
DDL Level 1 Add a LOCALUNIQUE constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name LOCALUNIQUE ( column_name );
DDL Level 1 Add a CHECK constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( check_condition );
DDL Level 1 Rename a constraint ALTER TABLE table_name RENAME CONSTRAINT constraint_name TO constraint_name;
DDL Level 1 Drop a constraint ALTER TABLE table_name DROP CONSTRAINT constraint_name;
DDL Level 1 Create a unique key index CREATE UNIQUE INDEX index_name ON table_name ( column_name );
DDL Level 1 Create a function-based index CREATE INDEX index_name ON table_name ( expression );
DDL Level 1 Drop a unique key/function-based index DROP INDEX index_name;
Other Statements Disk index AGING ALTER INDEX index_name AGING;
Other Statements Table COMPACT (memory/volatile) ALTER TABLE table_name COMPACT;
Other Statements Partition COMPACT (memory/volatile) ALTER TABLE table_name COMPACT PARTITION partition_name;
Other Statements Rebuild an index partition ALTER INDEX REBUILD PARTITION index_partition_name;
Other Statements Grant/revoke privileges GRANT OBJECT ...;
REVOKE OBJECT ...;
Other Statements Create/drop triggers CREATE TRIGGER ...;
DROP TRIGGER ...;

Reference#