MDTransform - Data Copy Template Conditions
A Data Copy condition provides the rules for determining which records in a physical file should be copied. A condition can be defined for a specific file from the Objects listing, or it can be defined at the Template level and then applied to any number of files within the libraries defined for the template. Multiple conditions can be used together (bound by the AND operator) for a file.
Conditions can also be used to define a subset of files to be included for copy when the X=Execute option is used for the specific condition number.
MDTransform must be licensed to use Conditions.
If a condition isn’t applied to a file, then all records will be copied.
Template Conditions Listing
This panel is reached with option C for a Data Copy Template. The panel lists all Template-Level Conditions.
| Filter Field | Description |
|---|---|
| Description | include row only if the description of the Condition contains the value in the filter |
| Condition | include row only if the SQL string of the Condition contains the value in the filter |
| Used by File | include row only if the Condition is applied to a specific file name |
| Option | Description |
|---|---|
| 2=Edit | Edit the Condition definition |
| 3=Copy | Copy all properties of a Condition to a new Condition |
| 4=Delete | Delete the Condition definition |
| 5=View | View the Condition definition |
| F=Files | View/Manage all Files that the Condition definition is applied to |
| V=Validate | Prompt to Execute the data copy for all files assigned to the condition definition 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 files assigned to the condition definition |
| Function Key | Description |
|---|---|
| F3/F12=Exit | return to previous screen |
| F5=Refresh | Refresh the Listing |
| F6=Add | Add a new Condition Definition |
| F11=View Output | View the MD Report Output listing |
| F21=Sys Cmd | access the system command line |
Condition Properties
Press F6 from the Condition listing to add a new Condition Definition, or use options 2, 3, or 4 to manage an existing definition.
| Field | Description |
|---|---|
| Template | the Template to contain the definition |
| Condition Desc | a description of the Condition |
| Records to Replace | 1 – All Records in File – all existing records in the target file will be removed before any records matching the condition are copied from the originating location/library 2 – Records matching Condition – only the records in the target file that match the condition will be removed before any records matching the condition are copied from the originating location/library. This provides a way to add certain use cases for testing without removing the other cases in the file(s). If multiple conditions are applied to the same file, then if at least one of the included conditions is set to 2, then option 2 will be used for all of the included conditions for that file. |
| Always include Condition | 1 – Yes – the condition will be appended to the WHERE clause with an AND operator, even when executing the copy for other condition numbers or for no specific condition number. 2 – the condition will only be appended to the WHERE clause if the execution is running for the specific condition number. When adding an object specific condition from the object listing within the library listing for the template, option 2 will mean that the condition will only be appended to the WHERE clause if the execution is not running for a specific condition number. |
| Join File | A join file can be defined for the condition in order to limit the records to be copied by those matching certain column values in the join file. The join condition is specified as part of the condition string in the Condition parameter which is then included in the WHERE clause. By using the AND predicate, additional conditions beyond the Join conditions can be included in the Condition parameter. Due to DDM constraints, the Join file must be on the same partition as the file that is being copied from. The library containing the join file is specified in parameter Remote Library. *FROM special value for the Remote library can be used and will be replaced by the actual from library at runtime. *TARGET special value for the Remote library can be used and will be replaced by the actual target library at runtime. If the Records to Replace option is set to 2=Records matching Condition, then the condition is also run as a subquery on the local delete statement. In this case, the join file must also be on the local partition and the Local Library parameter must be defined. If the from library is also on the local partition, then the join file can be the same object, but when the from library is on a remote partition, then a copy of the join file of the same name must exist in some library on each partition. |
| File ID | The file ID (prefixed qualifier) of the join file to use in the Condition statement. For example, if the File ID is set to S, then the referenced column in the condition statement must start with S followed by a period. When multiple conditions are applied to same file and each condition uses a different join file, then each join file should have a distinct File ID to ensure that the final WHERE clause can be interpreted correctly. |
| Condition | The rules to determine which records should be included. Technically, this is the SQL where clause for a select statement and any syntactically correct and allowed where clauses may be used, including functions. If the name of a column in the file is used in the condition, the file identifier F should be used to ensure that the column will be properly identified at runtime. For example, use F.CUSTNO instead of CUSTNO. The name of a column in the join file used in the condition should start with the file identifier defined in parameter File ID. String constants should be delimited with a single quote '. 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, the data copy process 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 condition can be applied in spite of the variation. The condition can be left blank if it is used just to create a subset of files to be updated within the template. |
Files for Data Copy Template Condition
Once a Template Condition has been defined, option F can be used to select which files should use that Condition.
If the Condition is not yet applied to any files, the Filters panel will be automatically displayed. The Filters can also be adjusted at any time by pressing F7 from the File Listing.
The File Listing can be toggled using F8 (see the Function Key table).
| 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 |
| and 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 of both the first field and second field |
| Option | Description |
|---|---|
| 1=Apply Condition | 1 - apply the Condition to the file in the listing remove the 1 to no longer apply to a file |
| Q=Query File | Query the data in the target file |
| Function Key | Description |
|---|---|
| F3/F12=Exit | return to previous screen |
| F5=Refresh | Refresh the Listing |
| F7=Filters | Bring up filter parameters. Any files already applied to the Condition Definition will continue to be applied even if the filter is changed. |
| F8=Toggle View | Toggle the listing between: Only Files assigned to the Condition Only Files matching current Filter values Files assigned or matching current Filter values |
| F9=SQL Name/Description | Toggle the listing between SQL naming for the tables or descriptions of the files |
| F13=Select All | Select every file in the current list |
| F14=Unselect All | Remove the selection from every file in the current list |
File-Specific Conditions
Conditions can also be defined directly for a specific file. To do so: - use option O=Objects for the Library Mapping definition of a Template and then - use option C=Conditions for the file
| Field | Description |
|---|---|
| Records to Replace | 1 – All Records in File – all existing records in the target file will be removed before any records matching the condition are copied from the originating location/library 2 – Records matching Condition – only the records in the target file that match the condition will be removed before any records matching the condition are copied from the originating location/library. This provides a way to add certain use cases for testing without removing the other cases in the file(s). If multiple conditions are applied to the same file, then if at least one of the included conditions is set to 2, then option 2 will be used for all of the included conditions for that file. |
| Always include Condition | 1 – Yes – the condition will be appended to the WHERE clause with an AND operator, even when executing the copy for other condition numbers or for no specific condition number. 2 – the condition will only be appended to the WHERE clause if the execution is running for the specific condition number. When adding an object specific condition from the object listing within the library listing for the template, option 2 will mean that the condition will only be appended to the WHERE clause if the execution is not running for a specific condition number. |
| Join File | A join file can be defined for the condition in order to limit the records to be copied by those matching certain column values in the join file. The join condition is specified as part of the condition string in the Condition parameter which is then included in the WHERE clause. By using the AND predicate, additional conditions beyond the Join conditions can be included in the Condition parameter. Due to DDM constraints, the Join file must be on the same partition as the file that is being copied from. The library containing the join file is specified in parameter Remote Library. *FROM special value for the Remote library can be used and will be replaced by the actual from library at runtime. *TARGET special value for the Remote library can be used and will be replaced by the actual target library at runtime. If the Records to Replace option is set to 2=Records matching Condition, then the condition is also run as a subquery on the local delete statement. In this case, the join file must also be on the local partition and the Local Library parameter must be defined. If the from library is also on the local partition, then the join file can be the same object, but when the from library is on a remote partition, then a copy of the join file of the same name must exist in some library on each partition. |
| File ID | The file ID (prefixed qualifier) of the join file to use in the Condition statement. For example, if the File ID is set to S, then the referenced column in the condition statement must start with S followed by a period. When multiple conditions are applied to same file and each condition uses a different join file, then each join file should have a distinct File ID to ensure that the final WHERE clause can be interpreted correctly. |
| WHERE | The rules to determine which records should be included. Technically, this is the SQL where clause for a select statement and any syntactically correct and allowed where clauses may be used, including functions. If the name of a column in the file is used in the condition, the file identifier F should be used to ensure that the column will be properly identified at runtime. For example, use F.CUSTNO instead of CUSTNO. The name of a column in the join file used in the condition should start with the file identifier defined in parameter File ID. String constants should be delimited with a single quote '. 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, the data copy process 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 condition can be applied in spite of the variation. The condition can be left blank if it is used just to create a subset of files to be updated within the template. |