MDTransform - Data Copy Templates
A Data Copy Template defines a set of libraries for the copying of data from one environment to another. This is typically used to refresh test libraries with some or all data located in production libraries, without losing format and object changes made in the test environment.
There are 2 levels of capabilities available with a Data Copy Template:
-
Basic capabilities – the files to copy from must be in a library on the local system and CPYF is used to copy all records in the file. If the format of the target file is different, *MAP/*DROP is used.
-
MDTransform capabilities (MDTransform License on the target partition is required):
- The files to copy from can be in a library on a different system or logical partition (DDM read access to the data library and to the QSYS2 library must be allowed on that system).
- SQL is used for the copy for better handling of modified field types
- Validation can be performed to ensure the copy will function at run-time
- Record Conditions can be defined for files so that only a subset of data will be copied
- Custom field value Transformation, such as to synthesize confidential data, can be performed on specific field columns.
Data Copy Template Maintenance
The Data Copy templates can be modified or executed from the MDCMS Setup Menu option 5=Templates->option 21=Data Copy. Execution of Data Copy Templates can also be performed using command MDCPYDATA (see section MDCPYDATA below).
MDSEC Authority to code md/7 (Template Maintenance) required to add or modify Data Copy templates.
List Data Copy Templates
This panel lists all defined Data Copy Templates used to update data on the same partition you are currently connected to.
| Filter Field | Description |
|---|---|
| Pos | the cursor will be positioned to the closest match in the list |
| From Location | include row only if the Template includes a library mapping with the name of the from location containing the value in the filter |
| From Library | include row only if the Template includes a library mapping with the name of the from library containing the value in the filter |
| Target Library | include row only if the Template includes a library mapping with the name of the target library containing the value in the filter |
| Option | Description |
|---|---|
| 2=Edit | Edit the Library Mappings for the Template |
| 3=Copy | Copy all properties of a Template to a new Template name |
| 4=Delete | Delete the Template |
| 5=View | View the Template |
| 7=Rename | Rename the Template |
| C=Conditions | View/Manage/Execute any template-level condition definitions. More Info |
| S=Status | View the Execution Status for any actively running jobs for a Template |
| T=Transformations | View/Manage any template-level Transformation definitions. More Info |
| V=Validate | Prompt to Execute the data copy for all defined files in the template in validation mode. This means that connectivity, authority and SQL syntax validation will be performed, but no data will actually be copied |
| W=Wildcards | View/Manage any wildcards defined for the Template. |
| X=Execute | Prompt to Execute the data copy for all defined data areas and files in the template |
| 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 |
| F6=Add | Add a new Template |
| F8=Desc/Info | Toggle the Listing between library mappings and Template Descriptions |
| F11=View Output | View the MD Report Output listing |
| F21=Sys Cmd | access the system command line |
Data Copy Template Properties
Press F6 from the Data Copy Template listing to add a new template, or use options 2, 3, or 4 to manage an existing template.
| Field | Description |
|---|---|
| Template | a 10 character ID to use as the key for the Template |
| Description | a description of the Template |
| Dft #Jobs | The number of parallel copy jobs that should be started by default when a data copy is executed. The valid range is between 1 and 9. For large sets of data, the recommended number of jobs is 5. If enough memory and CPU is available, then the full 9 jobs is recommended. |
| Dft Job Queue | The name of the job queue that the data copy jobs will be submitted to by default. Press F4 to browse from a list. At the time of the execution, the job queue must be released and the subsystem of the job queue must be active.<brIt is recommended that the subsystem have its own shared processor pool to reduce impact to other jobs running on the system. |
| Library | The library where the job queue object exists Press F4 to browse from a list. |
Data Copy Template Library Mapping
| Library Mapping Property | Description |
|---|---|
| From Location | The location of the library to be copied from Press F4 to browse from a list. *LOCAL - the library to copy from is on this partition Otherwise, the location ID of the from location. Locations are defined from the MDCMS Setup Menu option 6=IBMi Locations. DDM connectivity is required when copying from a remote partition. |
| From Library | The library to be copied from Press F4 to browse from a list. The from library does not have to exist at the time of defining the mapping. |
| Target Library | The library on this partition to be copied to Press F4 to browse from a list. The target library does have to exist at the time of defining the mapping. |
| Sort Sequence | The sequence in the list to process the library mapping. The sort can be used, for example, to pull data from production for a primary test environment and then copy from that environment to other environments on the same system. |
| Omit by Dft | N - all data areas and files will be included for copy by default, including new ones in the future. Y - all data areas and files will be excluded for copy by default, including new ones in the future. WARNING: the template only tracks exceptions to the default, so if the Omit by Dft value is toggled, all exceptions will be reversed and the object list will need to be updated to reflect the new rule. |
| Option | Description |
|---|---|
| 2=Edit | Edit the Library Mapping |
| 3=Copy | Copy all properties of a Library Mapping to a new mapping |
| 4=Delete | Delete the Library Mapping |
| 5=View | View the Library Mapping |
| O=Objects | View/Manage/Execute the list of data areas and tables/physical files in the target library |
| V=Validate | Prompt to Execute the data copy for all defined files in the library mapping in validation mode. This means that connectivity, authority and SQL syntax validation will be performed, but no data will actually be copied |
| X=Execute | Prompt to Execute the data copy for all defined data areas and files in the library mapping |
| 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 |
| F6=Add | Add a new Library Mapping |
| F11=View Output | View the MD Report Output listing |
| F21=Sys Cmd | access the system command line |
Data Copy Template Target Library Objects
The list of all physical files and data areas currently in a target library for a Data Copy Template Library Mapping can be viewed by using option O=Objects for a Library Mapping entry.
| Filter Field | Description |
|---|---|
| Object | include row only if the object name contains the value in the filter |
| Description | include row only if the object description contains the value in the filter |
| Omit | Y - include row only if the object is omitted from the data copy process N - include row only if the object is included in the data copy process |
| Cond | Y - include row only if conditions are defined for the file N - include row only if conditions are not defined for the file |
| Transform | Y - include row only if Data Transformations are defined for the file N - include row only if Data Transformations are not defined for the file |
| Option | Description |
|---|---|
| C=Conditions | View/Manage any file-level condition definitions. More Info |
| I=Include | Include object in the data copy process |
| O=Omit | Omit object from the data copy process |
| Q=Query | Query the contents of the target object |
| S=SQL | View the SQL string that will be used to insert records into the target file |
| T=Transform | View/Manage any file-level Data Transformation definitions. More Info |
| V=Validate | Prompt to Execute the data copy for the specific object in validation mode. This means that connectivity, authority and SQL syntax validation will be performed, but no data will actually be copied |
| X=Execute | Prompt to Execute the data copy for the specific object. |
| Function Key | Description |
|---|---|
| F3/F12=Exit | return to previous screen |
| F5=Refresh | Refresh the Listing |
| F11=View Output | View the MD Report Output listing |
| F13=Include all | Include every object in the data copy process that is currently displayed in the list, based on the filters |
| F14=Omit all | Omit every object from the data copy process that is currently displayed in the list, based on the filters |
| F15=Print | Generate a report of all objects currently displayed in the list, based on the filters. Press F11 to view the report. |
| F21=Sys Cmd | access the system command line |
MDCPYDATA Command – Copy Data to Target Library
The MDCPYDATA command is provided to run a Data Copy process. This command is prompted when using option V=Validate or X=Execute at the Template level, Library Mapping level or for a specific object in a library mapping.
The MDCPYDATA command can also be executed from a command line or scheduling tool.
| Parameter | Description |
|---|---|
| Data Copy Template | The ID of an existing Data Copy Template |
| Logging Level | A log report can optionally be generated. The report is made available from the MD Reports screen, which is accessible with F11 from most MD screens. 0 – nothing is logged 1 – logs any data areas or files that could not be copied due to errors 2 – log level 1 + any files that had a different record format 3 – log level 2 + all copied objects 4 – log level 3 + all objects not copied because they are on the omit list |
| Validate Only | *NO – the data is copied *YES – the process is validated but the data itself isn’t copied *SQL – the process is validated and the SQL syntax is returned for troubleshooting purposes (if for a specific file), but the data itself isn’t copied |
| Submit Job | *YES – the copy process will be submitted as a new batch job *NO – the copy process will run immediately within the same job |
| Job queue | The name of the job queue to submit the job to, if requested *DFT – the default job queue defined for the template. If undefined, then *JOBD will be used *JOBD – the job description assigned to the user profile for the job submitting the command |
| Job queue Library | The location of the job queue to submit the job to, if requested |
| Specific From Location | The location to copy from *NO – copy from all locations for the template |
| Specific From Library | The library to copy from *NO – copy from all libraries for the template |
| Specific Target Library | The library to copy to *NO – copy to all libraries for the template |
| Specific Object Name | The specific object to copy *NO – copy all included objects for the template |
| Specific Object Type | The specific object type to copy *NO – copy all types of included objects for the template *DTAARA – copy only data areas *FILE – copy only physical files |
| Specific Template Condition | Specifies if the list of objects to copy should be limited to the list of files that are applied to a specific Template Condition. 0 – the objects to be copied aren’t limited to a Template Condition Otherwise, specify the numeric ID of the condition, which is visible from the detail screen for the condition. |
| # Parallel Copy Jobs | Specifies the number of jobs to be submitted for parallel copying of the data. If only performing validation or copying a specific object, then this parameter is ignored and 1 job will be used. If 1, then just 1 job is submitted. If more than 1, then 1 control job + n copy jobs are submitted. Ensure that the job queue used allows for the number of jobs selected + 1 (when > 1). A maximum of 9 copy jobs can be used. 0=use the default number defined for the template. If undefined, 1 will be used. |
| Lock Wait in Seconds | The amount of time, in seconds, that MDCMS will wait to get the necessary lock on an object before timing out. If a time-out occurs, the object will be logged as an error and the data copy process will move on to the next object in the list. |
| Environment ID | The name of the MDCMS instance (or suffix) *DFT - the default instance of MDCMS, which doesn't have a suffix. For a different library suffix, this would be entered for the environment ID. |
| Email Report to Group | Specifies the ID of the MDCMS user group to receive the Data Copy Log by email. Each user in the group that has an email address defined |
| will receive an email with the Log attached as an xlsx document. | |
| Email Report to User | Specifies the ID of the MDCMS user to receive the Data Copy Log by email. If the user has an email address defined, they |
| will receive an email with the Log attached as an xlsx document. |
The Data Copy Process
For each Target object that is not omitted and exists in both the From Library and the Target Library, the following occurs:
-
Automatically increase maximum number of members if less than current number of members in From Library
-
Adds or Removes members as necessary if the From File has <> 1 member
-
Automatically increase maximum number of records if less than current number of records in From Library
-
Automatically handle differing record formats
-
Apply any defined record conditions to limit records copied
-
Perform any defined column transformations during the copy process
-
clear member if from member is empty
-
adjust the next value of an Identity Column in SQL Tables
-
temporarily disable constraints and triggers in a target file during the copy process