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 ...; |