Table of Contents Previous Next


7 Common Operations : 7.8 Replicating DDL Changes

Note: See Section 7.6.5 for information on dealing with other types of table definition changes.
Table definition changes are generally implemented using the SQL ALTER TABLE statement, which is issued in an SQL command line utility program such as PSQL.
The DDL change replication feature accepts one or more ALTER TABLE statements. The statements may be provided by means of a text file or by entering them directly into the Alter Publication Table dialog box. The latter can be done by copying and pasting the statements into the dialog box, or by directly typing in the statements. The DDL change replication feature then performs the following actions:
Applies the ALTER TABLE statements to the appropriate target table in the publication and subscription databases of a single-master replication system, or in all master nodes (including the master definition node) of a multi-master replication system.
The syntax of the ALTER TABLE statement accepted by the DDL change replication features is as follows:
ALTER TABLE schema.table_name action
where action can be any of the following:
RENAME [ COLUMN ] column_name TO new_column_name
ADD [ COLUMN ] column_name data_type
[ DEFAULT dflt_expr ]
[ column_constraint_1 [ column_constraint_2 ] ...]
DROP [ COLUMN ] column_name [ RESTRICT ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
[ COLLATE "collation" ]
[ USING data_type_expr ]
Set the DEFAULT value of a column:
ALTER [ COLUMN ] column_name SET DEFAULT dflt_expr
Note: The SET DEFAULT clause is not supported when Oracle or SQL Server is the subscription database.
Drop the DEFAULT value of a column:
ALTER [ COLUMN ] column_name DROP DEFAULT
Note: The DROP DEFAULT clause is not supported when Oracle or SQL Server is the subscription database.
ALTER [ COLUMN ] column_name SET NOT NULL
Note: The SET NOT NULL clause is not supported when SQL Server is the subscription database.
ALTER [ COLUMN ] column_name DROP NOT NULL
Note: The DROP NOT NULL clause is not supported when SQL Server is the subscription database.
The following restrictions apply to the manner in which the ALTER TABLE statements are specified whether it is in a text file or entered directly into the dialog box:
Each ALTER TABLE statement must be terminated by a semicolon and begin on a separate line.
Although the Postgres ALTER TABLE statement allows multiple actions per statement, the xDB DDL change replication feature permits only one action per ALTER TABLE statement.
The target table of all ALTER TABLE statements must be the same.
The DROP COLUMN action cannot be specified for a column that comprises part of the table’s primary key.
The name of the schema containing table_name. This value is case-sensitive.
A column constraint such as a UNIQUE or CHECK constraint. For additional information on column constraints see the CREATE TABLE SQL command in the PostgreSQL Core Documentation located at:

https://www.postgresql.org/docs/current/static/sql-createtable.html
In the DROP COLUMN clause, do not drop the column if there are objects dependent upon it. This is the default. Note: You cannot specify the CASCADE option as it is not supported by the DDL change replication feature.
The following are examples of ALTER TABLE statements that can be used by the DDL change replication feature.
The following set of ALTER TABLE statements, either specified by a text file or entered directly into the dialog box, adds columns to the edb.emp table.
The following ALTER TABLE statement changes the data type length of the title column and sets its values with the USING data_type_expr clause.
The following query shows the values assigned to the title column after the DDL change replication feature applies the preceding ALTER TABLE statement to the edb.emp table. This change to the title column and assignment of values occurs in all the subscription databases of a single-master replication system or in all the master nodes of a multi-master replication system.
The following set of ALTER TABLE statements drops the columns that were added in the first example.
The DDL statement is executed in a controlled manner such that the target table is exclusively locked (by the default setting of configuration option ddlChangeTableLock) during the course of the operation. This is done to avoid loss of any transactions while the replication triggers and shadow table are modified by the DDL change replication process. Only one target table is locked at a time while DDL change replication takes place on that table, its triggers, and shadow table.
Note: Exclusive acquisition of each target table during the DDL change replication process can be turned off by setting ddlChangeTableLock to false. However, this should be done only when there are no write transactions taking place against the target table, otherwise transactions may not be recorded by the replication system. See Section 10.4.1.11 for additional information on the ddlChangeTableLock configuration option.
If the publication server configuration option ddlChangeTableLock is set to its default value of true, an exclusive table lock is requested on the table to which the DDL change is to be applied. If another application already has a lock on the table, there is a wait time of 2 minutes after which the DDL change replication process is aborted if the lock is not released before then. If ddlChangeTableLock is set to false, an exclusive table lock is not requested.
Step 1: If you plan to use a file to supply the ALTER TABLE statements to a publication table, prepare the text file. Make sure this text file is accessible by the operating system account with which you will open the xDB Replication Console.
Alternatively, you can copy and paste, or directly type in the ALTER TABLE statements into the Alter Publication Table dialog box without having to save the statements in a file.
Step 2: Make sure the publication server whose node is the parent of the publication containing the table you wish to change is running and has been registered in the xDB Replication Console you are using. See Section 5.2.1 for directions on starting and registering a publication server.
Step 3: Under the publication database of a single-master replication system, or under the master definition node of a multi-master replication system, open the Alter Publication Table dialog box by clicking the secondary mouse button on the Table node of the table to be modified and choose Alter Table.
pub_alter_table_dialog_box_1
Step 4: In the Alter Publication Table dialog box, if you saved the ALTER TABLE statements in a text file, make sure the DDL Script File option is selected, browse for this file, and click the OK button.
pub_alter_table_dialog_box_2a
Alternatively, if you are directly entering the ALTER TABLE statements, select the DDL Script option instead of the DDL Script File option. Directly type in, or copy and paste the ALTER TABLE statements from your source into the text box. Click the OK button.
pub_alter_table_dialog_box_2b
Step 5: If the DDL replicated successfully message box appears, the DDL change was successful across all databases. Click the OK button.
pub_alter_table_dialog_box_3
Were the modifications in the ALTER TABLE statements successfully applied to the target table in each database of the replication system?
For the trigger-based method, was the shadow table RRST_schema_table located in the _edb_replicator_pub schema in each database of the replication system modified to account for the ALTER TABLE statements?

7 Common Operations : 7.8 Replicating DDL Changes

Table of Contents Previous Next