- \n
- IRI CoSort<\/a> for big data sorting, transformation, cleansing, and reporting<\/li>\n
- IRI NextForm<\/a> for data type, file format and database table migration<\/li>\n
- IRI FieldShield<\/a> for structured data discovery and de-ID (masking)<\/li>\n
- IRI RowGen<\/a> for generating safe, realistic flat-file or DB test data<\/li>\n
- IRI Voracity<\/a> for all of the above, plus ETL, CDC, wrangling, etc.<\/li>\n<\/ul>\n
What is Reuse?<\/b><\/h6>\n
One common definition of reuse is building new software from existing software components.<\/p>\n
Reuse is like recycling.\u00a0 Materials (components) are reused to create new products.<\/p>\n
Usage of Open Source Software (OSS)<\/a> in the development of other OSS and commercial software is a real world example of reuse.\u00a0 Building software that leverages OSS can result in lower costs and faster delivery of high quality software.<\/p>\n
Accomplishing Reuse with SortCL Scripts<\/b><\/h6>\n
To accomplish reuse, you must look at a requirement and think about how a script can be built to accomplish more than one task. For example, sorting an employee file by multiple attributes (keys) like name, name within a job title, or by some other attributes in the file. In a moment, I will cover how to build a script that provides this type of sort key flexibility, along with other scenarios.<\/p>\n
The basic ways to accomplish reuse when developing SortCL scripts are to use environment variables and specification statements (\/SPECIFICATION).\u00a0 Environment variables and specification files are resolved at runtime, and can provide a great deal of flexibility for a single script file.<\/p>\n
Environment Variables<\/strong> are variable names preceded by a $, and are replaced with the current operating system environment variable value at runtime prior to execution of the job script.<\/p>\n
The following depicts a PowerShell script that sets the value of an environment variable named\u00a0 sortKey to the value of Name prior to executing the EmployeesByDynamicKey SortCL job script.\u00a0 As a point of clarification, a $ is used in PowerShell to define variables for use in the PowerShell script.\u00a0 Environment variables are set in PowerShell using $Env:environment variable name=value <\/i>syntax as shown below for the variable sortKey.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByDynamicKey.png\")
<\/a><\/p>\nSortCL Specification Statements<\/b> (\/SPECIFICATION) define a named file that contains one or more script statements; and is replaced by the contents of the file prior to execution.<\/p>\n
It is a best practice to create Data Definition Files (.ddf) for a set of field definitions and reference them in SortCL scripts.\u00a0 This allows for reuse of the field definitions within and across multiple scripts.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/spec-employeescsv.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/Employee_csv.png\")
<\/a><\/p>\nAdvantages of Reusable Scripts<\/b><\/h6>\n
Scripts that are reusable eliminate the need to create a new script for every situation.\u00a0 For example, a single script can be built to sort a file using different key fields or applying different filtering criteria.<\/p>\n
Creating a reusable script takes a little extra thought, but can result in reduced development, maintenance and execution costs.<\/p>\n
Now that we have covered how reuse can reduce costs and the SortCL syntax that facilitates reuse, I will walk through a series of examples that should provide a good foundation for incorporating reuse into your SortCL job scripts.<\/p>\n
Achieving Reuse by Example<\/b><\/h6>\n
The EmployeesByName script shown below will be used as a basis for the reuse discussions that follow.\u00a0 This script is an example of a non-reusable SortCL job script that sorts a weekly employee file by name (last, first).<\/p>\n
Script<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByName.png\")
<\/a><\/p>\nInput file sample – Unordered Data<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeekly.png\")
<\/a><\/p>\nOutput File sample – data ordered by last name, first name<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeeklyByName.png\")
<\/a><\/p>\nAs you can see in the output file above, specifying \/PROCESS=CSV resulted in the field names specified by the CDEF attribute to be written to the first record of the output file and all fields surrounded by quotes.<\/p>\n
Reuse Example 1 – Field Definitions<\/b><\/h6>\n
As mentioned earlier, it is a best practice to create a Data Definition File (DDF) for a set of field definitions and reference the .ddf file with a \/SPECIFICATION statement in the SortCL job script.<\/p>\n
Creating a SortCL job script with a \/SPECIFICATION statement referencing a .ddf file instead of the individual \/FIELD statements, as shown above, would look like the EmployeesByName_ddf script below. The .ddf file is reusable for the input and output as they both contain the same fields.<\/p>\n
Executing the script will create the exact same file that the EmployeeByName script above did.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByName_ddf.png\")
<\/a><\/p>\nReuse Example 2 – Providing Sort Keys at Execution Time<\/b><\/h6>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/iri-script-reuse-flow-highlighted.png\")
<\/a><\/p>\nAs mentioned earlier, we will now look at creating a script that is reusable for multiple sorting scenarios.\u00a0 Instead of starting from scratch, the EmployeesByName_ddf script can be modified by following these steps.<\/p>\n
Step 1<\/strong><\/p>\n
Create files containing \/KEY statements for each field you want to sort on and place them in a KeyFiles Directory. For purposes of demonstration, I created five files with a .keys extension, as follows:<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/DeptNameSalary.png\")
<\/a><\/p>\nWhile a requirement to sort the file only by department name is unlikely, I created a file with the department name (DeptName.keys) so it could be reused in the DeptNameSalary and DeptNameEmplName key files by using a \/SPECIFICATION statement.<\/p>\n
Step 2<\/strong><\/p>\n
Make a copy of the EmployeesByName_ddf.scl file and name it EmployeesByDynamicKey.scl.<\/p>\n
Step 3<\/strong><\/p>\n
Change the script \/SORT statement by replacing the \/KEY statements with a \/SPECIFICATION statement as shown here.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/sort-spec-keys.png\")
<\/a><\/p>\nA quick test with the IRI Workbench using Run Configuration, setting the value for the sortKey environment variable to Name on the Environment tab, and clicking the Run button validates the changes worked successfully.<\/p>\n
As expected the output file is sorted by employee name just like the script above.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByDynamicKey-environment.png\")
<\/a><\/p>\nsortKey can now be set to the name of any of the .key files in the SortKeys directory created in step 1.\u00a0 Sorting using any other fields is as easy as creating a new .keys file and specifying it for the value of the sortKey environment value.<\/p>\n
Step 4<\/strong><\/p>\n
Change the \/OUTFILE file name to use the sortKey environment variable.\u00a0 This will result in the output file name reflecting the sort sequence of the file.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/outfile-employeesweeklyby.png\")
<\/a><\/p>\nFor example, specifying DeptNameEmplName as the value for sortKey will result in the execution of the script creating file EmployeesWeeklyByDeptNameEmplName.csv.<\/p>\n
The complete modified script is as follows:<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeekly-script.png\")
<\/a><\/p>\nWith this single SortCL job script multiple files can be created as shown in the following PowerShell script.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByDynamicKey-ps1.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/outputfiles-employeesweekly.png\")
<\/a><\/p>\nReuse Example 3 – Field Predicates<\/b><\/h6>\n
This example demonstrates how to streamline manual <\/i>creations of the script above and provide additional reuse capabilities by leveraging the \/FIELD_PREDICATE statement.<\/p>\n
A field predicate allows you to set default attributes like POSITION and SEPARATOR for the \/FIELD statements that follow it. Another benefit of removing the position parameter is that it allows you to remove or reorder fields without needing to update position numbers.<\/p>\n
Before continuing with the example, note that field predicate statements are incompatible with IRI Workbench operations and third-party tools<\/i>. Those interfaces seed and\/or need verbose field statements for mapping and lineage uses with which predicate interferes.<\/p>\n
Converting the prior example to use field predicates can be done in three easy steps as follows:<\/p>\n
Step 1<\/strong><\/p>\n
Create a copy of the EMPLOYEES_CSV.ddf used in the earlier examples. Then, streamline the file by removing the TYPE and SEPARATOR parameters from the \/FIELD statements.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/Employees_CSV2.png\")
<\/a><\/p>\nStep 2<\/strong><\/p>\n
Modify the script from above to use the .ddf file from Step1 and add a predicate statement prior to the specification statement for the input and output files.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/highlighted-modified-EmployeesWeekly-script.png\")
<\/a><\/p>\nRunning a quick test validates the output matches the earlier runs.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeeklyByName-2.png\")
<\/a><\/p>\nStep 3<\/strong><\/p>\n
Modify the SEPARATOR parameter of the output file to be an environment variable.\u00a0 This will allow the same SortCL script to be used to produce output files with different separators.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/sep-highlighted.png\")
<\/a><\/p>\nTesting the modified script specifying a vertical bar (|) and tilde (~) for the environment variable will result in the outputs below.<\/p>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeeklyByname-3.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesWeeklyByname-3-tilde.png\")
<\/a><\/p>\nReuse Example 4 – Extending Capabilities<\/b><\/h6>\n
The examples covered above demonstrate methods for creating SortCL job scripts that are reusable by using environment variables and specification statements within the context of the script. In this example, I cover two basic methods that can be used to add an additional output file to an existing job script.<\/p>\n
Producing more than one output in a single job script eliminates the need for multiple scripts.\u00a0 Creating multiple files in a single script results in reduced execution times and computing resource consumption, as the input file and sorting is only executed once.<\/p>\n
Method 1 – Modify Existing Script<\/strong><\/p>\n
As shown below, the existing script from Example 2 was modified to add a new output file that contains only the employee Id and name.\u00a0 While the field choices I made were arbitrary, having to create a file without salary is a likely business scenario.<\/p>\n
- IRI NextForm<\/a> for data type, file format and database table migration<\/li>\n
![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByDynamicKey_ext.png\")
<\/a><\/p>\n![\"\"](\"\/blog\/wp-content\/uploads\/2021\/09\/EmployeesByDynamicKey_incl.png\")
<\/a><\/p>\n