How to Convert Data Types or Substitute Column Values With Expressions

Question

How do you convert datatypes or substitute column values with expressions?

Environment

HVR 5

Answer

Sometimes you may want to override the automatic/default mapping of data type for your replication set. This can be done globally for all tables, at the individual table level, or for a specific column. This is done by using the HVR action ColumnProperties.

The action ColumnProperties lets you define the properties of a column. This column is matched either by specifying parameter /Name or /DataType. This action definition affects replication (both capture and integration), refresh, and compare.

For example, by default, HVR maps a number (without scale or (default) precision) in Oracle to numeric(38,4), because it is implicit in Oracle. By defining the following action, Oracle’s number (without scale or precision) is mapped to float instead:

In the following example, ColumnProperties /DatatypeMatch is used for mapping all columns with number (without scale or precision) on the source into float datatype on the target. In this example /DatatypeMatch matches a column instead of /Name.

If your source is SQL Server with definition varchar(8000), you may wish to store this in your target Oracle database as a CLOB.

Substituting Column Values Into Expressions with ColumnProperties

HVR has different actions that allow column values to be used in SQL expressions to map column names or do SQL restrictions. Column values can be used in these expressions by enclosing the column name in braces, e.g. {MyValue}.

Another use case is if you have a target that is used by an ETL process to extract the records from a Data Warehouse. You can then use /TimeKey integration method, which will insert source change data as a time series row on a target. A term that you may be more familiar with is audit or history of operations. For this, you need to define a new action ColumnProperties on the Target group (not the source group). And populate a new column dml_operation with the operation type that was performed on the row using IntegrateExpression{hvr_op}.

Each capture, fail, or history table created by HVR contains columns from the replicated table it serves, plus extra columns. The column {hvr_op} contains information about the operation performed on a row at the source location. Operations include 0-delete, 1-insert or 2-before update, 3-after update, etc. Set appropriate datatype for this field using /Datatype = Integer.