How to Convert Datatypes or Substitute Column values With Expressions?

Problem Summary

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

Answer

There may be time where you want to or need 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. 

Details

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

 

This table shows details of /Name, /BaseName, /DataTypeMatch & /DataType. More options can be found @ https://www.hvr-software.com/docs/actions/columnproperties

 

Parameter

Argument

Description

/Name

col_name

Name of column in hvr_column catalog.

/BaseName

tbl_name

This action defines the actual name of the column in the database location, as opposed to the column name that HVR has in the channel.

This parameter is needed if the 'base name' of the column is different in the capture and integrate locations. In that case the column name in the HVR channel should have the same name as the 'base name' in the capture database and parameter /BaseName should be defined on the integrate side. An alternative is to define the /BaseName parameter on the capture database and have the name for the column in the HVR channel the same as the base name in the integrate database.

The concept of the 'base name' in a location as opposed to the name in the HVR channel applies to both columns and tables, see /BaseName in TableProperties.

Parameter /BaseName can also be defined for file locations (to change the name of the column in XML tag) or for Salesforce locations (to match the Salesforce API name).

Parameter /BaseName cannot be used together with /Extra and /Absent.

/DatatypeMatch

datatypematch

Data type used for matching a column, instead of /Name.

  Since    v5.3.1/3  

Value datatypematch can either be single data type name (such as number) or have form datatype[condition].condition has form attribute operator value.

attribute can be precscalebytelencharlenencoding or null 

operator can be =<>!=<><= or >= 

value is either an integer or a single quoted string. Multiple conditions can be supplied, which must be separated by &&.

This parameter can be used to associate a ColumnProperties action with all columns which match the data type and the optional attribute conditions. Examples are:

/DatatypeMatch="number"

/DatatypeMatch="number[prec>=19]"

/DatatypeMatch="varchar[bytelen>200]"

/DatatypeMatch="varchar[encoding='UTF-8' && null='true']"

/DatatypeMatch="number[prec=0 && scale=0]" matches Oracle numbers without any explicit precision or scale.

/Datatype

data_type

Data type in database if this differs from hvr_column catalog.

 

 

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

Group 

Table 

Action 

SRCGRP 

ColumnProperties /DatatypeMatch = "number[prec=0 && scale=0]" /Datatype="float"

The above 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 used for matching a column, instead of /Name

If your source is SqlServer with a definition varchar(8000) you may wish to store this in your target Oracle database as a clob. 

Group 

Table 

Action 

SRCGRP 

ColumnProperties /Name=”MYLOB” /Datatype=”CLOB” 

 

Substituting Column Values Into Expressions with /Column Properties 

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

Another use case is if you have a target which is used by an ETL process to extract the records from for a Data Warehouse, one could use /TimeKey integration method which will insert source change data as a time series row on a target. A term which you all maybe more familiar with is audit or history of operations.  For this you would select create a new action /ColumnProperties and set ColumnProperties on the Target group (not the source group). You want to populate a new column, /Name, ie dml_operation with the operation type that was performed on the row, 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}, specifically contains information about what the captured operation performed on the row was on the source. Operations include 0-delete, 1-insert or 2-before update 3-after update, etc.  Give this field the appropriate datatype, ie  /Datatype = Integer. 

Group 

Table 

Action 

TGTGRP 

ColumnProperties  /Name=dml_operation /Extra /IntegrateExpression={hvr_op} /TimeKey /IgnoreDuringCompare /Datatype=integer