Category: ICETOOL

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)

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)