AdaptDDL

From HVR
Jump to: navigation, search
Actions
Previous: Restrict
Next: DbSequence
Action Reference

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 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 action 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 the AdaptDDL action 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.

Parameters

Parameter Argument Description
/AddTablePattern patt Add new tables to channel if the new table name matches patt. If this parameter is not defined then new tables are never be added to the channel.

Patterns can include wildcards (‘*’ or ‘o?_line_*’) or ranges (‘ord_[a-f]’). For a list patterns, either use a pattern containing a ‘|’ symbol (such as ‘mast_*) or defining multiple AdaptDDL/AddTablePattern actions. This action should be on table ‘*’ (‘all tables’) and on 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.

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

/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.
/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 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).
/RefreshOptions refropts Configure which hvrrefresh options the capture job should used to create or alter the target table(s) and (when necessary) re-populate the data. 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 databases this is avoided by disabling and re–enabling the triggers. Requires -gr (row-wise refresh).

-g x Granularity of refresh. Parameter x can be either

b Bulk refresh using bulk data load. This is the default.
r Row–wise refresh.

-m mask. Mask (ignore) some differences between the tables that are being compared. Parameter mask can 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).

-p N Perform refresh into multiple locations in parallel using N sub–processes. Not 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.

This parameter is only effective when defined on a integrate location.

/OnDropTable pol Value pol controls behaviour if a drop table is done to a replicated table. Possible values 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: Table (and its actions) are deleted from catalogs only, but the table is left in any target databases. This is the default.
  • 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 catalogs, whereas defining it on the integrate location controls whether the target table is dropped Note that if this is the last table in the channel then HVR will not drop it from the catalog. Instead the capture job will fail, because an HVR channel must always contains at least one table.

/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 Preserve old rows in target during recreate. 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.

Behavior for specific DDL statements and capture DBMSs

DDL SQL Statement Behavior without AdaptDDL action Behavior if AdaptDDL action is 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. Possible values 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: Table (and its actions) are deleted from catalogs only, but the table is left in any target database(s).. This is the default.
  • DROP_FROM_CHANNEL_AND_TARGET: Table (and all its actions) are deleted from catalogs and the target table is dropped from the target database(s).

Defining the parameter on the capture location controls whether the table is dropped from the channel catalogs, whereas defining it on the integrate location controls whether the target table is dropped. Note that if this is the last table in the channel then HVR will not drop it from the catalog. 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.

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 existing columns will have the HVR default value instead of the default value from the original alter table ... add column statement. 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 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 ‘datatype’, 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 datatype. Capture job will change the column’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 drop the column to the table in the target database(s)., or if alter table in the target DBMS cannot change datatypes 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 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 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 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, datatypes 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 infomation 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, datatypes 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, datatypes 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 action

Problems can occur when Capture 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.

If the channel already existed then one tactic to avoid such capture errors is to not regenerate existing enroll information when using HVR Initialize for Capture Rewind. But this could cause a different error, if a DDL statement happened after the ‘old’ capture job stopped running and before the new rewind point.

Restrictions

Action AdaptDDL can be used to capture DDL changes from few databases only. For more information, see Log-based capture of DDL statements using action AdaptDDL

For SQL Server, HVR must read using the FILE interface; Capture /LogReadMethod=SQL is not supported.