Additional Points

The section provides additional information on suppressing DDL related errors, preventing loops, and managing multiple events.

Suppressing DDL Related Errors

DDL related errors are suppressed by default except of the critical ones, which prevent the process from running (like file opening errors etc.). In case a non-critical DDL error occurs, an entry in the info-file is created for it (under the group "General information"). See process settings, "Information protocol writing" category.

Note: Suppressing or ignoring errors is potentially dangerous, since the target database could become out of sync with the source, or even corrupt. In such a case it is the user's responsibility to maintain the structural integrity of the target database. Always check the info file entries for information about potential problems.

Preventing Loops

In case of bidirectional replication, if "Forward DDLs" flag is activated, a loop prevention mechanism should be used for DDL as for any other CDC data. Please refer to other section of the documentation for more details on loop prevention in a bidirectional CDC environment.

Multiple events

Some DDL statements can contain multiple DDL events and can be seen as a group of DDL statements just written in a shortened form and executed in a single transaction. In such cases the DDL functionality (protocol, exit procedure etc.) is executed for each of the events, one after another.

Typical example:
create table t1(id int, primary key(id))

This statement defines two DDL events: CREATE_TABLE and ADD_PRIMARY_KEY. The DDL-related procedures will be called twice, for CREATE_TABLE and then for ADD_PRIMARY_KEY.

Another example (Oracle):
alter table t1
add c1 int
modify c2 number
add c3 int constraint t1_pk primary key
The following events will be identified by RDRS:
  • ADD_COLUMN (column c1)
  • MODIFY_COLUMN (column c2)
  • ADD_COLUMN (column c3)
  • ADD_PRIMARY_KEY (key column c3).
Note: All the events from the same DDL statement have the same time stamp. In such cases the input table in repository is terminated only once; the new input table gets all the changes.

Default values in Oracle and MS SQL Server

If a DDL statement contains a "default" clause for a column added or modified, the default value has to be surrounded by parentheses to be properly recognized by the DDL parser. For example,
alter table mytable add x1 int default (10)
Otherwise an information message is added to the info file (in case it is activated), and DDL processing for the current event is stopped. It continues from the next event. For example,
alter table mytable add x1 int default 10
The information message reads:
005I: DDL processing is stopped.Reason:default values without parentheses 
are not supported. DDL processing continues from the next transaction.

The reason for this is that default values can be very complex expressions containing various operators, function calls etc.

However, it is possible to change this behavior by defining a manual parameter DDL_DEFAULT_NO_SPACE and setting it to 1. In this case the DDL parser assumes that the default value is everything between the keyword default and the next white space, but white spaces in strings and inside parentheses are ignored.

Some examples without DDL_DEFAULT_NO_SPACE parameter (default behavior) :
"...default (10)"  - works
"...default (10 + 10)"  - works
"...default 10"  - does not work
Some examples with DDL_DEFAULT_NO_SPACE parameter:
"...default (10)"  - works (no changes)
"...default (10 + 10)"  - works (no changes)
"...default 10"  - works
"...default 10+10*10"  - works (no spaces)
"...default substr('a b', 1, 1)"  - works (spaces in strings and inside parentheses 
are allowed)
"...default 10 + 10"  - does not work (only recognizes the first "10" as the default 
value but parsing is stopped after that, since "+" is an unexpected token)