SELECT with first n duplicates

ICETOOL's SELECT operator now allows you to select the first n records with each key or the first n duplicate records with each key.

As example, following SELECT operator could be used to list the top 3 students in each class.
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,10,CH) FIRST(3) USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,10,CH,A,21,2,ZD,D)

We sort on the class name in ascending order, and on the average in descending order, to get the records in order by class name and highest to lowest average. We SELECT on the class name and use FIRST(3) to get the first three records for each class which gives us up to three students with the highest average.

I/P records :
Geometry Fred 85
Geometry Janis 71
Geometry Leonard 78
Geometry Michael 91
Geometry Susan 83
Geometry William 92
Algebra Fred 83
Algebra Janis 90
Algebra Leonard 85
Algebra Michael 94
Algebra Susan 92
Algebra William 87

O/P records:
Algebra Michael 94
Algebra Susan 92
Algebra Janis 90
Geometry William 92
Geometry Michael 91
Geometry Fred 85

Syntax :- The syntax for the new operands of SELECT is as follows:
SELECT FROM(DDIN) TO(DDOUT) ON(1,5,CH) FIRST(x)FIRSTDUP(x)
Detailed Description :

• FIRST(x) - Limits the records selected to those with ON values that occur only once (value count = 1) and the first x records of those with ON values that occur more than once (value count > 1).
• FIRSTDUP(x) - Limits the records selected to the first x records of those with ON values that occur more than once (value count > 1).

Hope you find this post useful. Please do provide your comments or reactions. Any sort of feedback is appreciated. You may post your queries in comments as well. For further detailed reference on SELECT and other new ICETOOL functions,Click Here.

SUBSET

SUBSET is a new ICETOOL operator that allows you to create a subset of the input or output records with specific header, trailer, and relative records, or without specific header, trailer, and relative records. SUBSET gives you new capabilities for keeping or removing the first n records of your data set, the last n records of your data set, and/or specific relative records in your data set. SUBSET does not require an "identifier" in the records to be keptor removed; it keeps track of the first n records, relative record numbers, and the last n records automatically.

Various options of SUBSET allow you to define the criteria for keeping or removing records.

As an example, you could use the following SUBSET operator to keep the first two input records, the fifth, sixth and seventh input records, and the last input record.

SUBSET FROM(IN) TO(OUT) KEEP INPUT FIRST(2) RRN(5,7) LAST
I/P records:
HEADER 001
HEADER 002
Vicky
Frank
Regina
Viet
David
Dave
Carrie
Sam
Sri Hari
Martin
LAST 999

O/P records:
HEADER 001
HEADER 002
Regina
Viet
David
LAST 999

Syntax - The syntax for the SUBSET operator is as follows:
SUBSET FROM(indd) TO(outdd) DISCARD(savedd) KEEPREMOVE INPUTOUTPUT HEADERFIRSTHEADER(x)FIRST(x)
RRN(x)RRN(x,y)RRN(x,*) ...
TRAILERLASTTRAILER(x)LAST(x)
USING(xxxx) VSAMTYPE(x)

Detailed Description :

• FROM(indd) - Specifies the ddname of the input data set.
• TO(outdd) - Specifies the ddname of the output data set , TO and DISCARD can both be specified.
• DISCARD(savedd) - DFSORT will write the records it does not select for the operation.
• KEEP - Specifies that the records that meet the criteria are to be kept.
• REMOVE - Specifies that the records that meet the criteria are to be removed.
• INPUT - The criteria of records selection takes place before INREC or SORT.
• OUTPUT - The criteria of records selection takes place after INREC or SORT.
• RRN(x) - point to exact single records,
• RRN(x,y) - specifies a range of records,
• RRN(x,*) - Specifies relative record numbers x through the last record.

Hope you find it useful. Please do provide your comments or reactions. For further reference click here.

DATASORT (Sort recs between header and trailer)

DATASORT is a new ICETOOL operator that allows you to sort the data records in a data set without sorting the header or trailer records. DATASORT gives you new capabilities for sorting the data records between header records (first n records of the data set) and trailer records (last n records of the data set) while keeping the header and trailer records in place.

EX : Your file has 100 records and you want to sort the file from 3rd record to 99th record only.

//TOOLIN DD *
DATASORT FROM(IN) TO(OUT) HEADER(2) TRAILER USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,4,CH,A)

I/P :
Header 001
Header 002
BBBB
CCCC
AAAA
DDDD
Trailer 001

O/P :
Header 001
Header 002
AAAA
BBBB
CCCC
DDDD
Trailer 001

Hope you find it useful. Its pretty easy as it doesn't requires any identifiers for header and trailer records. Just mention header and trailer record numbers.

Please do provide your comments or reactions. For further reference click here.

Group operations (WHEN=GROUP)

WHEN=GROUP is a new type of IFTHEN clause that allows you to do various types of operations involving groups of records.

WHEN=GROUP facilitates various types of group operations such as sorting by groups, including or omitting records by groups, and so on. You may also propagating fields, identifiers and sequence numbers within groups. WHEN=GROUP clauses can be used in INREC, OUTREC and OUTFIL statements by themselves or in conjunction with the other existing types of IFTHEN clauses.

An example to brief you on this new function, you could use the following statement to define groups of 3 records and add an identifier and sequence number to each record of each group.


INREC IFTHEN=(WHEN=GROUP,RECORDS=3,PUSH=(15:ID=3,19:SEQ=5))
Input records are:

Vicky
Sri
Hari
Frank
David
Dave
Regina
Sam
Viet

The output records would be:

Vicky 001 00001
Sri Hari 001 00002
Frank 001 00003
David 002 00001
Dave 002 00002
Regina 002 00003
Sam 003 00001
Viet 003 00002


Syntax :

The syntax for the IFTHEN WHEN=GROUP clause is as follows:
IFTHEN=(WHEN=GROUP,BEGIN=(logexp),END=(logexp),RECORDS=n,PUSH=(c:item,...))


Description :

• WHEN=GROUP must be specified to indicate this is a WHEN=GROUP clause.
  You can specify the BEGIN, END and RECORDS operands in any combination to define the groups, but You must specify at least one of these operands.


• BEGIN=(logexp) - Specifies the criteria to be tested to determine if a record starts a group.


• END=(logexp) - Specifies the criteria to be tested to determine if a record ends a group.


• RECORDS=n - Specifies the maximum number of records in a group. n can be 1 to 2000000000.


• PUSH=(c:item,...) - Specifies the position where each field, identifier or sequence number is to be overlaid in the records of each group.


• ID=n - Specifies a ZD identifier of length n is to be added to every record of each group. The identifier starts at 1 for the first group and is incremented by 1 for each subsequent group. n can be 1 to 15.


• SEQ=n - Specifies a ZD sequence number of length n is to be added to every record of each group. The sequence number starts at 1 for the first record of each group and is incremented by 1 for each subsequent record of the group. n can be 1 to 15.

Please do provide your comments or reactions. For further reference click here.

Find and Replace (FINDREP)

It does the same thing as its name suggests. It find the given piece of string and replaces it with another string given by you.

As a simple example, you could use the following INREC statement to replace all instances of 'Goodbye' in your input records with 'Bye', shift the bytes after the replaced constants to the left, and pad on the right with blanks.

INREC FINDREP=(IN=C'Goodbye',OUT=C'Bye')

If you had 60 byte input records like this:
*"Goodbye John"*"Goodbye William"*"Goodbye Goodboy"*
"Goodbye Michael""Good Dog""Goodbye Goodbye"

You would get 60-byte output records like this:
*"Bye John"*"Bye William"*"Bye Goodboy"*
"Bye Michael""Good Dog""Bye Bye"

For detailed reference on FINDREP and other new ICETOOL functions, Click Here.

Please do provide your comment or reactions.

DFSORT, ICETOOL upgraded in 2008

DFSORT is IBM's high performance sort, merge, copy, analysis and reporting product. DFSORT is an optional feature of z/OS.

DFSORT is designed to optimize the efficiency and speed with which operations are completed through synergy with processor, device, and system features (for example, memory objects, Hiperspace, data space, striping, compression, extended addressing, DASD and tape device architecture, processor memory, processor cache, and so on) and other products (for example, The SAS System, COBOL, PL/I, IDCAMS BLDINDEX, and so on).

DFSORT includes the high-performance ICEGENER facility, the versatile ICETOOL utility, Symbols, and multiple output capability with the powerful OUTFIL feature.

z/OS DFSORT V1R5 PTF UK90013, which was first made available in July, 2008, provides important enhancements to DFSORT and DFSORT's ICETOOL. Below is the list of few newly enhanced functions:

• Find and Replace (FINDREP).
• Group Operations(WHEN=GROUP).
• Sorting data between headers and trailers (DATASORT).
• Keeping or Removing the first n records (SUBSET).
• Selecting the first n duplicate records (SELECT with FIRST(n) and FIRSTDUP(n)).
• Splicing with non-blank fields (SPLICE with WITHANY).
• Displaying and Writing counts (DISPLAY with COUNT, ect).
• Reports with multiple and multipart titles (DISPLAY and OCCUR with TITLE, etc).
• Reports without carriage control characters (DISPLAY and OCCUR with NOCC).
• Additional defaults (BLKSIZE for DUMMY, SKIP=0L for SECTIONS, etc).
• Easier migration from other sort products, and more.

We will look into the above functions in detail in our next post.

Note: The above post is an excerpt from the white paper published by Frank Yaeger CLICK HERE. You can access all of the DFSORT books online at URL: CLICK HERE.

Please do provide your comments or reactions.