This documentation is for an older version of HVR.

Data Type Mapping

HVR's mapping/conversion of data types is complex because each DBMS's data types have a specific range which seldom corresponds the range of another DBMS. For example, data type varchar(10) in SQL Server corresponds to varchar2(10 bytes) in Oracle, but varchar(8000) corresponds to clob. Note that the mapping here does not just depend on the 'name' of the data type, but also its 'attributes' like byte length, encoding, scale and precision, etc. If HVR is not accurate in mapping data types, then target tables could be created which are unable to contain the data delivered from the source DBMS.

Data types are not directly mapped/converted from the source DBMS to the target DBMS, instead they are first mapped to HVR's own 'catalog data types' and then these catalog data types are mapped to corresponding data types in target DBMS or file format. Data type mapping/conversion happens in the following manner:

When a channel is built using Table Explore or using the command Hvradapt, the source DBMS data types mapped to HVR's own 'catalog data types'.
After the tables are added to a channel the mapping of data types in source DBMS (capture location) to HVR's catalog data types can be viewed in HVRGUI from the Table Explore window.
Then during HVR Refresh and Integrate, HVR's catalog data types are mapped to corresponding data types in the target DBMS (integrate location). This mapping happens at the moment the HVR Refresh is used to create target tables.
After performing HVR Refresh/Integrate the mapping of HVR's catalog data types to the target DBMS data types can be viewed in HVRGUI from the Table Explore window.

Customizing Data Type Mapping

If the automatic/default mapping of data type is not appropriate for a specific channel, it can be modified using action ColumnProperties /Datatype.

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

Group	Table	Action
SRCGRP	*	ColumnProperties /DatatypeMatch="number[prec=0 && scale=0]" /Datatype="float"

In the above example, ColumnProperties /DatatypeMatch is used for mapping all columns with number (without scale or precision) data type into float data type.

Alternatively, ColumnProperties /Name can be used for mapping the data type of a specific column (e.g. MYCOLUMN) into float data type.

Group	Table	Action
SRCGRP	*	ColumnProperties /Name="MYCOLUMN" /Datatype="float"

Extended Data Type Support

'Extended data types' are the DBMS data types (e.g. sql_variant in SQL Server or xmltype in Oracle) which are not mapped to native HVR data types. Instead, HVR's Extended Data Type Support feature should be used for such data types.

HVR uses action ColumnProperties /CaptureExpression for converting an extended data type to a supported data type during capture, compare or refresh (source) and ColumnProperties /IntegrateExpression for converting it back to extended data type during integrate, compare or refresh (target) respectively.

Data Type Mapping

Customizing Data Type Mapping

Extended Data Type Support

Data Type Mapping for Location Types