MDTransform - Data Transformation during RFP Deployment
The File Data Transformation option is available for Physical Files or SQL Tables that are requested in MDCMS/MDOpen on a partition where MDTransform is licensed.
If the object is requested for update, MDRapid must first be enabled for the request as MDRapid must be used for transformation of data when the file isn't modified or recompiled. Enable MDRapid by editing the Object Request and setting parameter "Use MDRapid" to *YES.
Data Transformation is used, by default, to map the data in a file from the prior format for the file to the new format during the installation of the file.
Data Transformation performs a dynamic SQL insert to populate the new version. The results for each column, by default, are the contents of the same column name in the prior version, if the prior column type can be cast to the new column type. For new or invalid columns, MDCMS automatically fills with blank, 0 or null, depending on column properties.
In order to view/customize the data transformation values for a file, the new and old version of the file must exist. MDCMS will attempt to create the new version of the file if it doesn’t exist in the developer’s library when requested for a check-out level. For higher levels, the file must exist in the from level to use the Data Transformation option.
Data Transformation Listing
Within the MDCMS Object Manager, the option is F - File Data Transformation. Within MDOpen for Object Requests, the option is Data Transformation.
The panel lists all fields in the new version of the file.
| Filter Field | Description |
|---|---|
| Field Name | include row only if the field name contains the value in the filter |
| Description | include row only if the field description contains the value in the filter |
| Field Type | include row only if the field type matches the value in the filter |
| New/Diff/Custom only | include row only if the field is new, has a different format or contains a custom result value |
| Row Option | Description |
|---|---|
| Edit (click on Field Name) | Edit the Result Value |
| Remove Custom Transformation | Reset the Result Value back to the default |
| Add/Remove Key for MDRapid | If MDRapid will be used to minimize downtime during deployment of the file, keys can be defined to designate the columns that combined would uniquely identify each record in the file. If the file has a unique key or primary key constraint on it, those fields will automatically be designated as the key fields. If key fields are not defined, MDRapid will map the records by relative record number instead, which is a bit slower and takes more overhead during the journal syncing process. If using MDRapid to transition a table from single-membered to partitioned, the designation of key fields is required. Up to 99 fields can be designated as a key field. If a field is marked in error, or relative record mapping should be used, remove the key from the field(s). |
| File Option | Description |
|---|---|
| Enable | Enable MDTransform for the copy of data from the old version to new version (default) |
| Disable | Disable MDTransform for the copy of data from the old version to new version. CPYF will be used instead. |
| SQL Statement | View the SQL syntax for the INSERT statement that will be used for the copy based on the current transformation definitions for the file. This can then be copied to an interactive SQL session for troubleshooting purposes. |
SQL Result Value
The SQL result is the value that will be inserted into the field for each row of the new version of the file. By default, the value will be the same as the value in the old version of the file or the default value for the field when new.
A custom result value is any valid SQL syntax, including funcitions, to transform the current value in the prior version of the file.library. Special values: - 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.