SORT Utility Capabilities
Part Three - Challenge #02

Background:

You will learn about SORT capabilities from your assigned mentor, Mister Peabody

SORT utility has useful data processing capabilities beyond the obvious.

One objective of Part 3 is to never write a program when a system utility will accomplish a needed task.

Your objective is to develop a process to validate client records.
  Validating client records is a real world data processing task.

Your assignment is to validate client records

You are new to the responsibility of validating another companies client master file.

A senior technician, Mister Peabody, is assigned to guide you. Let's call him Peabody for short.

Peabody has decades of experience and plans to retire. Peabody knows the ability to locate and use the professional documentation is critical to completing assignments. Peabody is unable to remember everything but he does remember where to find technical details needed to accomplish a task. When Peabody fails to remember details to accomplish a task, he will direct you to specific professional documentation.

NOTE: Whenever you see "Advice from Peabody" in Part 3, the information is extremely helpful.


Challenge Situation
  • Your company, (AAGI) AA Global Investments, periodically acquires smaller regional investment companies
  • AAGI is negotiating to acquire, BBRI, BB Regional Investments
  • Acquistion of BBRI includes 45 million clients
  • AAGI due diligence is to validate BBRI client records
  • BBRI gives AAGI the master file of 45 million client records with the corresponding record layout

Advice from Peabody
  • Study BBRI client master file record layout to develop a validation plan.
  • Does the BBRI client master file contain duplicate records?
  • Does the BBRI client master file contain invalid field formats in the client records?"

Peabody tells you about a "toolbag" of JCL used to execute system utilities and special programs to validate record layouts.

Peabody tells you the SORT utility can identify duplicate records using a specific control statement,
SUM FIELDS=NONE.


Challenge:

Study the Master File Client Record Layout
Peabody reviewed the client master file record layout with you and made a few observations.

Peabody mentioned the layout is a simple client master file layout with defined field types to help quickly identify duplicate records.

The Unique Account Number can be used to identify duplicate records.
  -- A client may have multiple account numbers.
  -- A duplicate account number is a data problem.

Note: The challenge uses a small BBRI master file but the file could easily be 45 million records.


Any Duplicate Unique Account Numbers in the Master File?

Peabody wants you to report back to him the total duplicate unique account numbers found in BBRI master file.

You are given the following details to get the information Peabody requested:

  1. Edit your z#####.jcl partitioned data set and select, s, a new member name, sort001
  2. Copy 'zos.public.toolbag(sort001)' into z#####.jcl(sort001)
  3. JCL job sort001 is a simple sort accompanied by SUM FIELDS=NONE statement
  4. Modify JCL SET operation to reference ZOS.PUBLIC.BBRI.CLIENTS as the BBRI SORTIN data set name
  5. Submit and review output
  6. If message ICE055I is in SYSOUT DDNAME of the JCL job output,
      then any records with duplicate unique account numbers where found and deleted.
      The total duplicate unique account numbers follow DELETE in message ICE055I.

You report the number of total duplicate unique account numbers found to Peabody.
Peabody responds, when duplicate records are found the business analysts need more information.
You must provide a special report about the duplicate records.


Business Analysts and Auditors Request a Report About BBRI Client Records

Peabody explains when duplicate records are found, then the business analysts want the following details:

  • List of client records containing duplicate unique client account numbers
  • List of client records that excludes the duplicate account numbers

Peabody informs you that a SORT companion utility program, ICETOOL, is significantly more feature rich.
ICETOOL can create the needed report about the duplicate unique account numbers. However, several minor modifications are needed.

You are given the following action items to get the information the business analysts requested:

  1. Edit your z#####.jcl partitioned data set and select, s, a new member name, sort002
  2. Copy 'zos.public.toolbag(sort002)' into z#####.jcl(sort002)
  3. JCL sort002 executes the SORT utility companion program, ICETOOL
  4. Modify JCL SET operation to reference ZOS.PUBLIC.BBRI.CLIENTS as the client master file input
  5. Modify ICETOOL select control statement directives -
      Observe the select statement directive ON(P,M,F)
      where P,M,F are values for starting position (P), length (M), and field format (F)

      ** Get the unique account number values for P, M, and F from the JCL job SORT001
      where the control statement instructs the program to sort the data from SORTIN
      using starting column, length, ascending A order, and character format.

      ISPF split and swap features are available
        F2 will 'split' making 2 displays available
        F9 will 'swap' between the 2 displays
        F3 is available to terminate 1 of the displays
  6. Submit and review output
  7. Report needed by business analysts is written to Z#####.P3.OUTPUT(#02)

You and Peabody review the JCL job output and the report in Z#####.P3.OUTPUT(#02).

Peabody observed the report is NOT exactly what the business analysts requested.

Peabody needs you to adjust an ICETOOL select control statement directives to get a report needed by the business analyst.


Adjust ICETOOL Select Control Statement Directive

The current Z#####.P3.OUTPUT(#02) has a single record for each duplicate unique client account number.

The business analysts need the duplicate report to include all duplicate unique account number records to compare each record with all duplicates of the record

Peabody mentioned the select control statement directive LASTDUP needs to change.
However, Peabody could not remember the syntax of the replacement directive to write all the duplicate records into the duplicate record report.

Peabody tells you to look at the following ICETOOL select statement directive documentation to determine the syntax of the directive needed to write all the duplicate records into the duplicate report.
Using the ICETOOL utility - Selecting records by field occurrences


  1. Edit z#####.jcl(sort002)
  2. Modify ICETOOL select control statement directive LASTDUP with a directive writing a report the business analysts need to visually compare the duplicate records with each other
  3. Submit and review output
  4. Report needed by business analysts is written to Z#####.P3.OUTPUT(#02)

You just begun the journey of learning tricks known by z/OS technicians. You will learn more!

Next: Challenge #03