MDTransform - Data Copy Template Data Transformations
A Data Copy Transformation provides the ability to manipulate the values in table columns as they are copied from one location/library to another. A transformation can be defined for a specific file column from the Objects listing, or it can be defined at the Template level and then applied to any number of file columns within the libraries defined for the template.
MDTransform must be licensed to use Transformations.
If a transformation isn’t applied to a file column, then the value for each row in the from library will be copied without alterations.
Template Transformations Listing
This panel is reached with option T for a Data Copy Template. The panel lists all Template-Level Transformations.
| Filter Field | Description |
|---|---|
| Description | include row only if the description of the Transformation contains the value in the filter |
| Transformation | include row only if the SQL string of the Transformation contains the value in the filter |
| Used by File | include row only if the Transformation is applied to a specific file name |
| Used by Field | include row only if the Transformation is applied to a specific field name |
| Option | Description |
|---|---|
| 2=Edit | Edit the Transformation Description and Result Value |
| 3=Copy | Copy all properties of a Transformation to a new Transformation |
| 4=Delete | Delete the Transformation definition |
| 5=View | View the Transformation definition |
| F=Fields | View/Manage all Fields that the Transformation definition is applied to |
| Function Key | Description |
|---|---|
| F3/F12=Exit | return to previous screen |
| F5=Refresh | Refresh the Listing |
| F6=Add | Add a new Transformation Definition |
| F11=View Output | View the MD Report Output listing |
| F21=Sys Cmd | access the system command line |
Data Transformation Properties
Press F6 from the Transformation listing to add a new Transformation Definition, or use options 2, 3, or 4 to manage an existing definition.
| Field | Description |
|---|---|
| Template | the Template to contain the definition |
| Transformation Description | a description of the Transformation |
| Transformation Result Value | The SQL syntax to transform the current value in the from library and then insert that value into the target library. Any syntactically correct SQL expression that is allowed for a column in an SQL statement may be used, including functions. NULL – set the value to NULL *GEN – for columns that contain the Generated clause, *GEN can be used to indicate that the system should generate a new value in the target library at copy execution time. Wildcards can be used in the SQL string and will be replaced with the actual value for the wildcard at execution time. Additionally, the ? symbol can be used as a replacement value for specific characters in a field name. At execution time, MDCMS will replace the complete field name with the first system or SQL long name that matches the field name string containing one or more ? symbols. This is helpful when the field name has a different prefix or suffix, depending on the file, so that the same transformation can be applied in spite of the variation. |
Transformation Example Result Value: Make Customer Identifying Data Anonymous
This is an example showing how a Transformation Template could easily be used to mask customer identifying data when copying from production to test.
If the current value for the field is blank, then the field will remain blank in the target library. However, if it isn’t blank, then the value is replaced with the name of the field concatenated by the customer number with leading zeroes stripped off. This is just one example, but the transformation can be as elaborate as necessary, including by using a user-defined function to prepare the value.
Fields for Data Copy Template Transformation
Once a Template Transformation has been defined, option F can be used to select which file fields should use that transformation.
If the Transformation is not yet applied to any fields, the Filters panel will be automatically displayed. The Filters can also be adjusted at any time by pressing F7 from the Field Listing.
The Field Listing can be toggled using F8 (see the Function Key table). The Sts column in the listing has the following possible values: - NO - the field doesn't have Transformation defined for it - YES - the field uses the selected Transformation definition - OTH - the field uses a different Transformation definition - CST - the field uses a custom result value specifically defined for it
A field can only contain 1 Result value, either from a Template-Level Transformation definition or a field-level value.
| Filter Field | Description |
|---|---|
| Target Library | include row only if the target library name matches the generic pattern |
| File System Name | include row only if the system name of a file in the target library matches the generic pattern |
| File SQL Name | include row only if the SQL long name of a file in the target library matches the generic pattern |
| File Description | include row only if the file description matches the generic pattern |
| Contains Field | include row only if the file in the target library contains a field whose system name or SQL long name matches the generic pattern |
| Field System Name | include row only if the system name of a field matches the generic pattern |
| Field SQL Name | include row only if the SQL long name of a field matches the generic pattern |
| Field Description | include row only if the description of a field matches the generic pattern |
| Field Types | include row only if the SQL field type (up to 4 types) match. Press F4 to select a type from a list |
| Field Length | include row only if the field length is within the minimum and/or maximum range |
| Field Decimals | include row only if the number of decimal places for a field is within the minimum and/or maximum range |
| Option | Description |
|---|---|
| 1=Apply Transformation | 1 - apply the transformation to the field in the listing remove the 1 to no longer apply to a field |
| 5=Field Info | view all of the primary properties of a field |
| Q=Query File | Query the data in the target file |
| Function Key | Description |
|---|---|
| F3/F12=Exit | return to previous screen |
| F4=Browse | Browse list of available values for the field where cursor is positioned |
| F5=Refresh | Refresh the Listing |
| F7=Filters | Bring up filter parameters. Any fields already applied to the Transformation Definition will continue to be applied even if the filter is changed. |
| F8=Toggle View | Toggle the listing between: Only Fields assigned to the Transformation Only Fields matching current Filter values Fields assigned or matching current Filter values |
| F9=SQL Name/Description | Toggle the listing between SQL naming for the tables and columns or descriptions of the files and fields |
| F13=Select All Undefined | Select every field in the current list that isn't currently defined to have Transformation at the Template or File level |
| F14=Unselect All | Remove the selection from every field in the current list |
| F18=Select All including Defined | Assign every field in the current list to this Transformation definition, even if the field is currently assigned to a different definition or has Transformation defined at the File level |
Field-Specific Transformation Result Value
A transformation value can also be defined directly for a specific field. To do so: - use option O=Objects for the Library Mapping definition of a Template and then - use option T=Transform for the file containing the field(s) - use option 2=Edit for a field
The Result Value is the SQL syntax to transform the current value in the from library and then insert that value into the field in the target library for each row of copied data.
Any syntactically correct SQL expression that is allowed for a column in an SQL statement may be used, including functions. - NULL – set the value to NULL - *GEN – for columns that contain the Generated clause, *GEN can be used to indicate that the system should generate a new value in the target library at copy execution time.
Use option 4=Remove Custom Result to revert the field to not using any custom transformation.