CoSort Streamlines High Volume Data Manipulation for CalWORKS Information Network
Reviewer
Dan Gonos, EDS chief technologist for CalWORKS Information Network (CalWIN) project.
Background
Under the 11-year CalWIN project, EDS is contracted to replace a 30-year-old system with the largest human services system of its kind in the county. It is a mission-critical cornerstone for both collecting and sharing information among agencies supporting 30,000 end users in their day-to-day jobs in more than 800 locations throughout California. CalWIN's interactive interview tools make it possible to quickly determine benefit levels for more than 2,200,000 families in need. Other features allow county workers to coordinate benefits across programs to better align services delivered to clients. A case management component enables the counties to use readily accessible information to measure the effectiveness and outcomes of various service programs.
Platforms
IBM pSeries running AIX 5 release.
Problem Solved
In addition to CalWIN's interactive tools, the system executes approximately 6,400 batch scripts each evening, which produce interface and reporting extract files that are used to deliver and manage benefits. The legacy system CalWIN replaced required several products and steps to convert, reformat, sort, join and merge these (differently formatted) files. CalWIN uses CoSORT to perform many of these functions all at the same time by embedding straightforward sort control language (SortCL) program scripts. For example, one of the SortCL sections of a batch job sorts and executes two joins containing 500,000 records each. CoSORT's SortCL processes this portion of the job in 25 seconds. This level of application throughput allows CalWIN to rapidly transform tremendous data volumes into meaningful decision information that field workers need every day.
Product Functionality
CoSORT's SortCL is a 4GL for multifunction data transformation and reporting. SortCL runs on the command line, from a Java GUI, in batch, or via program (API) calls. In a single SortCL job script (and I/O pass) through one or more flat sources in one or more formats, you can either: sort, merge, join or report. In that job, you can map across commonly named fields to conditionally select, reformat, type-convert, cross-calculate, aggregate, find/replace, sequence and/or report. SortCL will simultaneously produce one or more custom output targets in one or more (detail and/or summary) formats.
Strengths
The sort, join and merge features in CoSORT's SortCL program not only allow multiple records to be merged into one, but also allow users to specify the specific keys on which to match. While it joins, SortCL can also produce multiple outputs in multiple formats. CoSORT's processing performance scales according to the needs of the application and the configuration of the system.
Weaknesses
New functionality introduced in subsequent CoSORT releases tightened the rules of the SortCL language so that some of our more complicated and less conventional scripts that had (accidentally) worked in prior releases no longer would. However, IRI was able to modify new executables so they would continue to support our diverse needs.
Selection Criteria
We researched leading high-volume, flat-file data manipulation software that had sort, join and merge capabilities. CoSORT provided the reliability and scalability we needed, as well as flexible and perpetual licensing.
Deliverables
In addition to SortCL's UIs, it supports various JCL sort parm and third-party meta data (field layout) conversion utilities to help migrate jobs and field layouts for use in SortCL. CoSORT has other UIs and APIs, including an interactive, menu-driven program for sort/merge operations (which can save the specs for batch runs) and approximately a dozen plug-and-play replacements for third-party sort functions.
Vendor Support
The CoSORT people are outstanding to work with, whether for technical support, purchasing, product enhancements or platform migrations. Their responsiveness and flexibility has been tremendous from day one.
Documentation
The documentation is fairly complete and has enough examples to facilitate development. Developers without prior knowledge of CoSORT can build scripts without IRI's help.
