Now that we understand data mapping, we can perform data transformation in our GoldenGate configuration. Using the built-in string manipulation and number conversion functions with the COLMAP option, it is possible to achieve the most common data transformations: truncation, concatenation, substitution, conversion, and case changes.
Note that the target column is always on the left in the COLMAP statement.
Data transformation does come at a price though—performance. When conducting complex or numerous simple transformations with high data volume and throughput, latency will prevail. It may not be as significant a bottleneck as the serial process, but CPU and memory consumption on the target system will increase.
Starting with truncation, Oracle performs an implicit data type conversion between a source and target column. However, should the target column scale be smaller than its source, the data is truncated on the target.
GoldenGate offers the @STREXT function for string truncation and character extraction. Although the function will extract any characters from a string based on begin and end character positions, it lends itself to truncating the string to a certain length, as shown in the following code:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.CREDITCARD_ACCOUNTS, &COLMAP (USEDEFAULTS, & AREA_CODE = @STREXT(PHONE_NUMBER,1,5), & PHONE_NO = @STREXT(PHONE_NUMBER,6,11));
Here, we split the 11-digit telephone number from the source table into AREA_CODE and PHONE_NO fields on the target table.
The @STRCAT function provides string concatenation by joining two or more separate strings together. In the following code, we will concatenate the FIRST_NAME and SURNAME fields from the source table to the NAME field on the target table:
MAP SRC. CREDITCARD_ACCOUNTS, TARGET TGT. CREDITCARD_ACCOUNTS, & COLMAP (USEDEFAULTS, & NAME = @STRCAT(FIRST_NAME," ",SURNAME));
The @STRSUB function provides string substitution, allowing a pattern of characters to be replaced with a new string. The following code converts the TITLE field from the source table to an abbreviated form on the target table:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.CREDITCARD_ACCOUNTS, & COLMAP (USEDEFAULTS, & TITLE = @STRSUB(TITLE, "DOCTOR","DR","MISTER","MR"));
Although it is possible to use the @STRSUB function to perform case change transformations, the preferred method is to use the @STRUP function, which converts a lowercase string to uppercase.
The following code illustrates the function's simplicity:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.CREDITCARD_ACCOUNTS, & COLMAP (USEDEFAULTS, & TITLE = @STRUP(TITLE));
In addition to string manipulation, GoldenGate supports numeric conversions through two functions.
The @NUMSTR function converts a string to a number for arithmetical calculations, while the @STRNUM converts a number to a string, but with the additional option of padding characters.
The following code will convert the CREDIT_BALANCE value from the source table to a string padded with zeros to a maximum of five characters:
MAP SRC.CREDITCARD_PAYMENTS, TARGET TGT.CREDITCARD_PAYMENTS, & COLMAP (USEDEFAULTS, & CREDIT_BALANCE = @STRNUM(CREDIT_BALANCE,LEFTZERO, 5));
It is also possible to convert a binary string of eight or less bytes to a number using the @NUMBIN function.
The @COMPUTE function supports arithmetic expressions that are useful to evaluate fields, including those that may be missing or null. Simple arithmetic is executed through standard operators, such as + (plus), - (minus), * (multiply), and / (divide). These do not require @COMPUTE when used in conjunction with another GoldenGate function.
Here is an example:
MAP SRC.CREDITCARD_PAYMENTS, TARGET TGT.CREDITCARD_PAYMENTS, & COLMAP (USEDEFAULTS, & CREDIT_BALANCE = @STRNUM(CREDIT_BALANCE + NEW_TRANSACTIONS,LEFTZERO, 5));
However, when programming comparison expressions that return TRUE (nonzero) or FALSE (0), @COMPUTE is required.
For example, if CREDIT_BALANCE is greater than CREDIT_LIMIT, return FALSE.
@COMPUTE (CREDIT_BALANCE > CREDIT_LIMIT) returns 1 @COMPUTE (CREDIT_BALANCE < CREDIT_LIMIT) returns 0
Valid numeric strings can be converted to a number of different formats that are compatible with SQL. The default date format for GoldenGate is 'YYYY-MM-DD HH:MI:SS'; this is generated by the @DATENOW function.
To perform a conversion on a numeric string, use the @DATE function, choosing the relevant format options, which are similar to SQL.
The following code converts the DATE_KEY column numeric values on the source to a date on the target:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.CREDITCARD_ACCOUNTS, &
COLMAP (USEDEFAULTS, &
CREATED_AT = @DATE ("YYYY-MM-DD:HH:MI:SS", "YYYYMMDDHHMISS", DATE_KEY) );The @DATENOW function can be used to populate a MODIFIED_AT column on the target table, as shown in the following code:
MAP SRC.CREDITCARD_ACCOUNTS, TARGET TGT.CREDITCARD_ACCOUNTS, & COLMAP (USEDEFAULTS, & MODIFIED_AT = @DATENOW);
When used in combination with @DATEDIFF and @DATENOW, @COMPUTE can calculate the numeric day of the year as follows:
TODAY = @COMPUTE (@DATEDIFF ('DD', '2015-04-20', @DATENOW ()) +1)