Question
How do you convert datatypes or substitute column values with expressions?
Answer
There may be times when 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 Local Data Processing 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 Local Data Processing 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 Local Data Processing 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 Local Data Processing 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 Local Data Processing 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 Local Data Processing 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 prec, scale, bytelen, charlen, encoding 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 Local Data Processing 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
Local Data Processing 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 Local Data Processing 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 |