Category: SORT

Sort card – replace selective fields (IFTHEN,WHEN)

Lets say, we want to replace the word ‘TEST’ in position 16 into ‘TEMP’

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-
123423452345   TEST  ARASDRGSDFGS
12342356563465 AAAA  JHFDGHDFSGDSF
7853425632456  TEST  JDFSGGDSFHJGGH
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=(16,4,CH,EQ,C'TEST'),OVERLAY=(16:C'TEMP'))

Lets say, we want to replace the word ‘TEST’ in position 16 into ‘TEMP’, and ‘TEMP’ in position 36 to ‘TEST’

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-
123423452345   TEST  ARASDRGSDFGS
12342356563465 AAAA  JHFDGHDFSGDSF TEMP  
7853425632456  TEST  JDFSGGDSFHJGGH
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=(16,4,CH,EQ,C'TEST'),OVERLAY=(16:C'TEMP')),
      IFTHEN=(WHEN=(36,4,CH,EQ,C'TEMP'),OVERLAY=(16:C'TEST'))

also, read about FINDREP, more about IFTHEN

Advertisement

ICETOOL to get maximum, minimum value records

ICETOOL SELECT FIRST / LAST to get largest / smallest values within a set

Suppose we have a file with Branch name, Account number, Transaction amounts. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Chennai   10011     00523
Chennai   10011     00010
Bangalore 10011     00056
Bangalore 10011     00670
Chennai   10012     00200
Chennai   10012     00235
Chennai   10012     00750
Bangalore 10012     00750
Bangalore 10012     00034

and, we want to get the maximum value transactions for every account. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Bangalore 10011     00670
Bangalore 10012     00750
Chennai   10011     00523
Chennai   10012     00750

this can be done using ICETOOL as,

//TOOLIN DD *
 SELECT FROM(INFILE) TO(OUTFILE) ON(BRANCH) ON(ACCOUNT) -
 FIRST USING(CTL1)
/*
//CTL1CNTL DD *
 SORT FIELDS=(BRANCH,A,ACCOUNT,A,TRANS-AMT,D)
/*
//SYMNAMES DD *
 BRANCH,1,10,CH
 ACCOUNT,11,5,ZD
 TRANS-AMT,21,5,ZD
/*

In the above ICETOOL step, we are sorting the file in the order of Branch name (Ascending), Account number (Ascending), Transaction amount (Descending).

(If you want to select the smallest/minimum value transactions only, then sort the Transaction amount in Ascending order).

Then for each Branch and Account, we are selecting the first record only.

what are SYMNAMEs?

ICETOOL to copy files

Direct copy of multiple files. Copy files from DD names INFILE1, INFILE2, INFILE3 to OUTFILE1, OUTFILE2, OUTFILE3

//TOOLIN DD *
 COPY FROM(INFILE1) TO(OUTFILE1)
 COPY FROM(INFILE2) TO(OUTFILE2)
 COPY FROM(INFILE3) TO(OUTFILE3)

Copy of multiple files with some processing. Copy files from DD names INFILE1, INFILE2, INFILE3 to OUTFILE1, OUTFILE2, OUTFILE3

//TOOLIN DD *
 COPY FROM(INFILE1) TO(OUTFILE1) USING(CTL1)
 COPY FROM(INFILE2) TO(OUTFILE2) USING(CTL2)
 COPY FROM(INFILE3) TO(OUTFILE3) USING(CTL3)
/*
//CTL1CNTL DD *
 SORT FIELDS=(1,5,CH,A)
/*
//CTL2CNTL DD *
 SORT FIELDS=(6,5,CH,A)
/*
//CTL3CNTL DD *
 SORT FIELDS=COPY
 OUTFIL CONVERT,
 OUTREC=(1:5,80,20X)

SORT in Easytrieve

You can Sort a file into another file in Easytrieve. The Syntax is

 SORT file-name-1 +
 TO file-name-2 +
 USING (field-name [D]...) +
 [BEFORE proc-name]
  • file-name-1 is the input file DD Name
  • file-name-2 is the Sorted output file
  • field-name is the field defined in file-name-1, based on which the file has to be sorted
  • If you want the sort to be on Descending order, pass the parameter ‘D’ after the field name (by Default, it will be Ascending)
  • If you have any select criteria to filter what records to be written in the output file, have it in a Proc and give the proc name with BEFORE parameter (optional)

Let us see a couple of sample programs to explain.

Simple Sort on a key

The below program will simply 

 FILE INFILE FB(80 960)
   ACCOUNT-NO  1 8 N
*
 FILE OUTFILE
*
 SORT INFILE TO OUTFILE USING ACCOUNT-NO

being updated..

Sort-card to split a file into multiple files (OUTFIL,FNAMES,SAVE)

Let’s say we have a file with first byte having 2 different values ‘A’ or ‘B’ or any other character.
We are going to create a file with all records starting with A (DD name SORTXXA), a second file with all records starting with B (DD name SORTXXB), a third file (DD name SORTXXX) with remaining records.

 SORT FIELDS=COPY
 OUTFIL FNAMES=SORTXXA,INCLUDE=(1,1,CH,EQ,C'A') 
 OUTFIL FNAMES=SORTXXB,INCLUDE=(1,1,CH,EQ,C'B')   
 OUTFIL FNAMES=SORTXXX,SAVE

SYMNAMES in SORT

SYMNAMES is a means of defining variables in SORT. When you have a complex sortcard, it becomes hard to have your sort control card ‘readable / understandable’. It is a good practice to always use SYSNAMES DD to define variables and then use the logical variable names in Sort card.

Below is an example with the SYMNAMES DD (variables declared) and SYSIN (the ‘sort control card’).

//SORTSTEP  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DISP=SHR,
//            DSN=MYDSN.NAME
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SYMNAMES DD *
 Name,1,10,CH
 Age,11,2,ZD
/*
//SYSIN    DD *
 SORT FIELDS=(Age,D)
 INCLUDE FILEDS=(Age,GT,25)
/*
//SORTOUT  DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=MYDSN.OUTPUT.NAME

read more on SYMNAMES

ICETOOL Count

Below step will get the number of records present in IN1 and IN2 files. The counts will be displayed in TOOLMSG DD. This will come handy, when you need to get count of big files and you have so many files to check the count.

//STEP01 EXEC PGM=ICETOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//IN1 DD DSN=MY.DSN.ONE,DISP=SHR
//IN2 DD DSN=MY.DSN.TWO,DISP=SHR
//TOOLIN DD *
 COUNT FROM(IN1)
 COUNT FROM(IN2)

Sort Card examples (IBM Mainframes)

Avoid duplicates in a particular field (say first 8 characters)

SORT FIELDS=(1,8,CH,A)
SUM FIELDS=NONE

Sort first 8 characters, and omit few values

SORT FIELDS=(1,8,CH,A)
OMIT COND=(1,8,CH,EQ,C'AAAAAAAA')

Sort first 8 characters, and include only few values

SORT FIELDS=(1,8,CH,A)
INCLUDE COND=(1,8,CH,EQ,C'AAAAAAAA',OR, 1,8,CH,EQ,C'BBBBBBBB')

Sort card to select only 15 characters from 36th position of my input file.

//SORTSTEP  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DISP=SHR,
//            DSN=MYDSN.NAME
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SYSIN    DD *
 SORT FIELDS=COPY
 OUTFIL CONVERT,
 OUTREC=(1:36,15,65X)
/*
//SORTOUT  DD DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(250,100),RLSE),
//            UNIT=DISK,
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
//            DSN=MYDSN.OUTPUT.NAME

The 65X is given to fill the remaining 65 bytes of the output file with spaces.