This documentation is for an older version of HVR.

AdaptDDL

Description

Normally HVR only handles database DML statements (such as insert, update and delete). Action AdaptDDL causes HVR to also react to DDL statements such as create table, drop table, alter table ... add column or drop column.

This action should normally be defined on both the capture location and the integrate location. When on the capture database, the capture job will react to DDL changes to tables already in the channel by changing the column information in the HVR catalogs. If parameter /AddTablePattern is defined it will also add new tables to the channel. If the action is also defined on the integrate database then the capture job will then apply these DDL changes to the integrate databases; in some situations it would do an alter table to the target table in the integrate database; in other situations it will do an HVR Refresh which will either create or alter the target table and then resend the data from the capture database.

The mechanism of AdaptDDL shares many 'regular' components of HVR replication. In fact the capture job automatically handles each DDL change just as a careful operator using the HVR GUI should. So if a capture job encounters a DDL it will re-inspect the source table (as if it used Table Explore); if it sees for example that a new table is needed it will automatically add it to the HVR catalogs. Sometimes the capture job will do an HVR Refresh, although where possible HVR will instead do an alter table on the target table, for efficiency. A consequence of this mechanism is that many strong features of HVR will work normally with AdaptDDL:

Heterogeneous replication (between different DBMS's) works normally with AdaptDDL
Actions such as TableProperties, ColumnProperties, Restrict and Transform work normally with AdaptDDL. Note that these actions must be defined for all tables (table="*") for them to affect a new table is added to a channel by AdaptDDL.
Different HVR Refresh options (such as row-wise refresh and the option to create unicode tables) work normally with AdaptDDL.

Note that internally the AdaptDDL mechanism does NOT work by just getting the full 'create table' SQL statement from the DBMS logging system and sending that through HVR's internal pipeline. Instead the capture job reacts to any DDL it detects by re-inspecting the table and 'adapting' the channel to reflect the new situation that it sees at that time (which may be later than the original DDL). This delayed response (instead of sending SQL DDL through a pipeline) has some advantages:

In many situations the DBMS logging does not contain enough data after a DDL statement to continue (or start) replicating the table, so a HVR Refresh is necessary anyway. For example, during a big upgrade, DBMS logging on a table may have been disabled to bulk-load data.
If a table has been dropped and created multiple times (maybe HVR was turned off during a weekend upgrade) then HVR will not waste time performing each intermediate change; it will instead 'skip' to the last version of the table.
Sharing the 'regular' components of HVR allows its rich functionality to be used in an 'adaptive' channel. Otherwise AdaptDDL would only be usable in an homogeneous situation e.g. a channel from Oracle version 11.1 to Oracle 11.1 with no special actions defined.

Restrictions

Capturing DDL changes using action AdaptDDL is not supported for certain databases. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.
Action AdaptDDL cannot be used together with Transform /SapXForm.
For Oracle, action AdaptDDL is not supported if the log read method is SQL (Capture /LogReadMethod=SQL).
For SQL Server, action AdaptDDL is not supported if the log read method is SQL (Capture /LogReadMethod=SQL) in HVR versions prior to 5.2.3/0.
For SQL Server, action AdaptDDL is not supported when capturing from a SQL Server Always On secondary node.

Parameters

This section describes the parameters available for action AdaptDDL.

Parameter	Argument	Description
/AddTablePattern	patt	Add new tables to the channel if the new table name matches patt. If this parameter is not defined then new tables are never added to the channel. Patterns can include wildcards (* or o?_line_) or ranges (ord_[a-f]). For a list of patterns, either use a pattern containing a \| symbol (example, tmp\|temp) or defining multiple AdaptDDL /AddTablePattern* actions. This action should be defined on Table * (all tables) and typically on both capture and integrate locations. If /CaptureSchema is not defined then this table must be in the location's 'current' schema. A table will not be replicated twice, even if it matches multiple AdaptDDL /AddTablePattern actions. This parameter is only effective when defined on a capture location. This parameter is not supported for certain databases. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.
/IgnoreTablePattern	patt	Ignore a new table despite it matching a pattern defined by /AddTablePattern. The styles of pattern matching is the same as the pattern of /AddTablePattern. This parameter only affects tables matched by the /AddTablePattern parameter on the same AdaptDDL action, not those matched by other /AddTablePattern parameters. For example a channel defined with these actions: AdaptDDL /AddTablePattern=t* /IgnoreTablePattern=tmp\|temp *AdaptDDL /AddTablePattern=x This channel will automatically add to tables tab_1 and tmp_x but not table tmp_y**. This parameter is only effective when defined on a capture location. This parameter is not supported for certain databases. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.
/CaptureSchema	schema	This parameter controls which schema's new tables are matched by /AddTablePattern. Value schema is not a pattern (no '' wildcards) but it is case-insensitive. If this parameter is not defined then the only new table that are matched are those in the location's 'current' or 'default' schema. When a new table is added using this parameter then the HVR capture job will also generate TableProperties/Schema* action(s), unless the schema is the capture location's current schema. This parameter is only effective when defined on a capture location. This parameter is not supported for certain databases. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.
/IntegrateSchema	schema	This parameter allows a new table which is matched in a schema on a capture database defined with /CaptureSchema to be sent to a schema different from the default schema on an integrate database. One or more mappings to be defined. So when a channel has action *AdaptDDL /AddTablePattern=""/CaptureSchema=aa1/IntegrateSchema=bb1 and action AdaptDDL /AddTablePattern=""/CaptureSchema=aa2/IntegrateSchema=bb2* then table aa1.tab would be created in the integrate database as bb1.tab whereas table aa2.tab would be created in the target database as bb2.tab. Each table would be added to the channel with two TableProperties /Schema actions; one on the capture location and one on the integrate location. This parameter is only effective when defined on a capture location, even though it actually causes actions to be generated on the integrate location group(s). This parameter is not supported for certain databases. For the list of supported databases, see Log-based capture of DDL statements using action AdaptDDL in Capabilities.
/OnEnrollBreak `Since v5.6.5/13`	pol	This parameter applies policy pol to control the behavior of capture job (whether to execute HVR Refresh because of a DDL change) for an existing table if there is a break in the enroll information (like data type changes, partition changes etc.). This parameter is only effective if defined on the target location where the HVR Refresh would load data into. This parameter does not control the behavior of a capture job for a new table being added to the channel. Available values for pol are: REFRESH (default): Refresh the table. FAIL_INTEG_JOB: Send a breakpoint control to all involved integrate jobs. Once all changes up to the DDL sequence are integrated, the control will cause the integrate job to fail with an error. The problem must be solved manually and the control must be removed manually. WARNING: Issue a warning and then continue without the HVR Refresh. If necessary, perform only the alter table.
/OnPreserveAlterTableFail `Since v5.6.5/13`	pol	This parameter applies policy pol to control the behavior of capture job for an existing table to handle any failure while performing alter table on the target table. This parameter is only effective if defined on the target location where the alter table is being performed. Available values for pol are: CREATE_AS_SELECT (default): Move existing table to a temporary table and create new table with new layout as selected from old table. FAIL_INTEG_JOB: Send a breakpoint control to all involved integrate jobs. Once all changes up to the DDL sequence are integrated, the control will cause the integrate job to fail with an error. The problem must be solved manually and the control must be removed manually. WARNING: Issue a warning and then continue replication without retrying to perform the alter table.
/RefreshOptions	refropts	Configure which HVR Refresh options the capture job should use to create or alter the target table(s) and (when necessary) re-populate the data. This parameter is only effective when defined on a integrate location. Value refropts is a list of option letters, separated by spaces. Possible options are: -f : Fire database triggers/rules while applying SQL changes for with row-wise refresh. The default behavior is that database trigger/rule firing is disabled during refresh. For Oracle and SQL Server, this is avoided by disabling and re-enabling the triggers. Requires -gr (row-wise refresh). -gx : Granularity of refresh. Parameter x can be either: b (default) : Bulk refresh using bulk data load. r : Row–wise refresh -mmask : Mask (ignore) some differences between the tables that are being compared. Parameter mask should be composed of one of these letters: d : Mask out delete differences. u : Mask out update differences. i : Mask out insert differences. Letters can be combined, for example -mid means mask out inserts and deletes. If a difference is masked out, then the refresh will not rectify it. Requires -gr (row-wise refresh). -pN : Perform refresh into multiple locations in parallel using N sub–processes. No effect if only one integrate location. -v : Verbose. This causes row–wise refresh to display each difference detected. Differences are presented as SQL statements. Requires -gr (row-wise refresh) All refreshes implied by AdaptDDL use context adaptddl (like hvrrefresh -Cadaptddl) so data truncated and selected can be controlled using action Restrict with /Context=adaptddl.
/OnDropTable	pol	Policy pol controls behavior if a drop table is done to a replicated table. Available values for pol are: KEEP: Table will remain in channel. Capture job will write a warning message in log. The next hvrinit will give error ('table not found') when it attempts to regenerate enroll information for this channel. DROP_FROM_CHANNEL_ONLY (default): Table (and its actions) are deleted from catalogs only, but the table is left in any target databases. DROP_FROM_CHANNEL_AND_TARGET: Table (and all its actions) are deleted from catalogs and the target table is dropped from the target databases. Defining the parameter on the capture location controls whether the table is dropped from the channel repository tables, whereas defining it on the integrate location controls whether the target table is dropped. If this is the last table in the channel, then HVR will not drop it from the repository table, instead the capture job will fail because an HVR channel must always contains at least one table. If the value is KEEP or DROP_FROM_CHANNEL_ONLY and the table is created again in the capture database, then the old table in the integrate database will be reused; it will be recreated or an `alter table` done to make its columns match.
/KeepExistingStructure		Preserve old columns in target, and do not reduce data types sizes. This means if an alter table statement was done on the capture table to drop a column or make it smaller (e.g. varchar(12) to varchar(5)) this will not be propagated to the target table. This can used to protect historical data, which could have been purge of the capture database was not replicated (using Capture /IgnoreSessionNames) or if the integrate table contains a row for each capture change (ColumnProperties /TimeKey). This parameter is only effective when defined on a integrate location.
/KeepOldRows `Since v5.6.5/12`		Preserve old/existing rows (hvrrefresh -cp) in target table if the table is dropped and recreated with a new layout during HVR Refresh.

Behavior for Specific DDL Statements and Capture DBMSs

DDL SQL Statement	Behavior without AdaptDDL	Behavior with AdaptDDL Defined	Notes for specific capture DBMS
create table	Capture job ignores DDL. Operator must manually perform 'Adapt steps' (including Table Explore and HVR Refresh) to add table to channel.	If new table is not in channel but the capture location has action AdaptDDL with a matching /AddTablePattern then the table is added to the channel and supplemental logging is enabled (if necessary). If integrate database(s) also have action AdaptDDL then the capture job will do an HVR refresh which will also create the table in the target database(s).This refresh should be quick because the new table should be empty or at least very small. If the table already existed in the integrate database it will be recreated or an alter table used to make its columns match. If /AddTablePattern is not defined or the table name does not match then this DDL statement is ignored.
drop table	If a table was in the channel then capture job will write a warning message in log. The next hvrinit will give error ('table not found') when it attempts to regenerate enroll information for this channel.	If the table in is the channel then the behavior depends on value of AdaptDDL parameter /OnDropTable.	For SQL Server, this is not allowed if Capture /SupplementalLog=ARTICLE_OR_CDCTAB if the table has a primary key because when HVR is capturing a table, the drop table statement gives "Cannot drop table … because it is being used for replication" [error 3724].
create table, followed quickly by drop table	Both DDL statements are ignored.	If the drop table is already complete by the time the capture job encounters the first create table in the DBMS logging then the capture job will ignore both DDL statements. If the drop table occurs after the capture job has finished processing the create table statement then each DDL statement will processed individually (see lines above). But if the drop table occurs while the capture job is still processing the create table statement then its refresh may fail with a 'table not found' error. But the capture job will then retry and succeed, because the drop table is already complete (see above).
drop table, followed quickly by create table	Capture job will write a warning message when it sees the drop table and when it sees create table it will update its internal enroll information so that it can still parse new values.	If the create table is already complete by the time the capture job encounters the first drop table in the DBMS logging then the capture job will refresh the table again, because there may be updates to the newly recreated table which HVR cannot process because supplemental logging had no been created yet. It will then update its internal enroll information so that it can still parse new values. If the create table is has not happened by the time the capture job encounters the first drop table then these statements will be processed individually.
alter table ... add column – without a specified default value clause	New column will be ignored; it won't be added to target and its value won't be replicate or refreshed. But replication of other columns continues normally. Subsequent hvrinit or hvrrefresh commands will also work normally.	Capture job will add the column to the channel catalogs. If an integrate database(s) has action AdaptDDL then the capture job's behavior will do an alter table to add the column to the table in the target database(s). For some DBMSs the capture job will then refresh the data into the integrate location(s). Then replication will resume.	For Oracle and SQL Server HVR will not refresh the data and just continue replication.
alter table ... add column – with a specified default value clause	Same as regular alter table ... add column above.	Same as regular alter table ... add column above, except the target table will just get an alter table ... add column with a default value defined by HVR. This means when HVR creates or alters tables it assigns a default value to mandatory columns. The default value is dependent on the data type, and not inherited from the source database. As a result, when a column with a default value is added to the source database, the new column on the target will show the HVR default value for previously existing rows. Newly replicated values will get the correct value from the source.	Same as for regular alter table ... add column.
alter table ... drop column	Capture job will only update its internal enroll information so that it can still parse new values. If this was a key column or it was not nullable and had no default then integrate errors will start to occur.	Capture job will drop the column from the channel catalogs. If an integrate database(s) has action AdaptDDL then the capture job will use alter table to drop the column to the table in the target database(s), unless /KeepExistingStructure is defined. In this case the columns is kept in the target. For some DBMSs the capture job will then refresh the data into the integrate location(s). Then replication will resume.	For Oracle and SQL Server HVR will not refresh the data and just continue replication. Note: Both alter table ... add column and alter table ... drop column usually resume replication without a refresh. However, there are some exceptions to this rule. If a column was dropped and then added again, HVR needs to refresh the data to assure that all data is replicated correctly. Additionally, for SQL Server locations which have Capture/LogReadMethod set to SQL, dropping a column will cause a refresh to prevent potential issues with the ongoing capture.
alter table … modify column – to make column 'bigger', e.g., varchar(5) to varchar(12).	Capture job will only update its internal enroll information so that it can still parse new values. But when a new large value is captured it will either cause an error in the integrate job, or (if TableProperties /IgnoreCoerceError is defined) it will be truncated.	Capture job will change the column's information from the channel catalogs. If an integrate database(s) has action AdaptDDL then the capture job's will do an alter table to change the target column's width. No refresh will be done to the target table. Then replication will resume.
alter table … modify column – to make column 'smaller', e.g., varchar(12) to varchar(5).	Capture job will only update its internal enroll information so that it can still parse new values. No errors.	Capture job will change the column's information from the channel catalogs. If an integrate database(s) has action AdaptDDL then the capture job's will do an alter table to change the target column's width, unless /KeepExistingStructure is defined. The capture job will then refresh the target table. Then replication will resume.
alter table ... modify column – to change 'data type', e.g., number to varchar(5).	Capture job will only update its internal enroll information so that it can still parse new values. But when a new value is captured the integrate job may give an error if it cannot convert the new value into the target's old data type.	Capture job will change the column's information in the channel catalogs. HVR's refresh has the ability to alter columns on some database platforms, typically based on database limitations. If an integrate database(s) has action AdaptDDL then the capture job will either do an alter table to drop the column to the table in the target database(s)., or if alter table in the target DBMS cannot change data types then the table will be dropped and recreated. The capture job will then refresh the target table. Then replication will resume.
alter table ... modify column – to change 'encryption', e.g., enable encryption or change encryption algorithm.	Capture job will warn that the channel definition should be upgraded and a refresh should be done. It will also give an error because it cannot handle the encrypted columns correctly.	Capture job will change the column's encryption information in its internal enroll information. It will then refresh the target table. Then replication will resume. The capture job will not replicate the encryption setting change to the target table.	This is supported only on Oracle 11 and higher. For more information on HVR's support of Oracle's encryption feature (TDE) see the TDE section in the Requirements for Oracle.
alter table ... rename column	Capture job will only update its internal enroll information so that it can still parse new values. If this was a key column or it was not nullable and had no default then integrate errors will start to occur.	Capture job will change the table's information in the channel catalogs. If an integrate database(s) has action AdaptDDL then the capture job will either do an alter table to rename the column to the table in the target database(s)., or if alter table in the target DBMS cannot rename columns then the table will be dropped and recreated. The capture job will then refresh the target table. Then replication will resume.	SQl Server does not support alter table ... rename column but it uses the build in function sp_rename.
truncate table	HVR captures this as a special DML statement (hvr_op=5), unless Capture/NoTruncate is defined. This changes is applied as truncate table by the integrate job, unless Restrict/RefreshCondition is defined there.	HVR captures this as a special DML statement (hvr_op=5), unless Capture/NoTruncate is defined. This changes is applied as truncate table by the integrate job, unless Restrict /RefreshCondition is defined there.
alter index ... on..rebuild – in online mode, e.g., with (online=on) SQL Server only	Capture job will only update its internal enroll information so that it can still parse new values.	Capture job will only update its internal enroll information so that it can still parse new values.
alter table ... add constraint ... primary key create unique index create index ... local (partition ...) drop index	Ignored. But if a uniqueness constraint is relaxed on the capture database (for example if the primary key gets an extra column) then a uniqueness constraint violation error could occur during integration	HVR only maintains a single key (the "replication key") in the HVR channel catalogs and on the target tables. If there are multiple uniqueness constraints on the capture table (e.g. a primary key and several unique indexes) then HVR uses a hierarchy rule to decide which is its replication key (e.g. a primary key would 'win'). When the capture job encountered this DDL statement then it will re-inspect the capture table and see if its 'replication key' has now changed. If it has then the capture job will change the channel catalogs to either add, remove or change this 'replication index'. If integrate database(s) also have action AdaptDDL then the capture job will change the 'replication index' on the target table in the target database(s). The index 'name' and other attributes (such as 'fill factor') are ignored, as are other 'secondary' indexes on the capture table. No refresh is needed.
alter table ... add foreign key	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
alter table ... rename to ... – Rename table	Capture job will write a warning message in log. The next hvrinit will give error ('table not found') when it attempts to regenerate enroll information for this channel.	This is treated like a drop table and a create table. So the old name is deleted from the catalogs and added to the target depending on parameter /OnDropTable (see above). If the new table name matches /AddTablePattern then it is added to the channel. If integrate database(s) also have action AdaptDDL then the capture job will do an HVR refresh which will also create the new table name in the target database(s).
alter table … truncate partition	Ignored. The deletes implied by this DDL statement will not be replicated.	If an integrate database(s) has action AdaptDDL then the capture job will refresh the target table. Then replication will resume.
alter table...merge partition	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
alter table ... split partition	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
alter table ... exchange partition	Ignored. The changes implied by this DDL statement will not be replicated.	If an integrate database(s) has action AdaptDDL then the capture job will refresh the target table. Then replication will resume.
alter table ... move tablespace	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
alter tablespace ...	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
create sequence	Changes captured and integrated if action DbSequence is defined. See that action for limitations.	Changes captured and integrated if action DbSequence is defined. See that action for limitations.
drop sequence	Ignored.	Ignored.
create/drop view create/drop synonym	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
create/drop trigger	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
create/drop procedure create/alter/drop function create/alter/drop user create/alter/drop role create/drop directory	Ignored. Replication continues correctly.	Ignored. Replication continues correctly. DDL not replicated/propagated to target database(s).
dbms_redefintion – to change tables storage (partitioning, compression, tablespace, LOB storage etc..) but not information stored in HVR catalogs (column names, data types or key)	Capture job will only update its internal enroll information so that it can still parse new values.	HVR recognizes Oracle dbms_redefintion because it sees that the create time is same but the table id has changed. HVR assumes that no other zero other DDL (alter table) subsequently. in which case no refresh needed. Enroll information will be updated and capture will continue.	This is supported only on Oracle.
dbms_redefintion – to change tables storage (partitioning, compression, tablespace, LOB storage etc..) but not info stored in HVR catalogs (column names, data types or key), followed by an alter table to change other column information.	Capture job will only update its internal enroll information, and will treat the subsequent DDL statement individually.	HVR recognizes Oracle dbms_redefintion because it sees that the create time is same but the table id has changed. HVR assumes (incorrectly) that no other zero other DDL (alter table) subsequently. so it neglects to do a refresh.	This is supported only on Oracle.
dbms_redefintion – which changes information in the HVR catalogs (the column names, data types or primary key)	See row above showing behavior for specific alter table type.	See row above showing behavior for specific alter table type.	This is supported only on Oracle.

Use of Capture Rewind with AdaptDDL

Problems can occur when Capture Rewind is used to go back to a time before a DDL statement changed a replicated table.

Background: The capture job parses its tables changes (called 'DML') using 'enroll information' which is created by HVR Initialize. This has an Advanced Option called Table Enrollment (option -oe) can be used to either (a) not regenerate this enroll information or to (b) only regenerate this enroll information. When the capture job encounters a DDL statement it will re-inspect the table and save the table's new structure as a 'revision' to its original enrollment information. This will help it process subsequent DML statements from the logging.

But if Capture Rewind is used with HVR Initialize then the 'original' enrollment information created by that command may be newer than the DML changes that the capture job must parse. If a DDL statement (such as alter table ... drop column) was performed between the 'rewind' point where the capture job must start parsing and the moment when HVR Initialize generated the enrollment information, the capture job may fail when fail if it encounters a DML record using the old table structure. Such errors will no longer happen after the capture job encounters the actual DDL statement or after it passes the moment that HVR Initialize was run.