ICETOOL - SELECT Operator


Selects records from a data set for inclusion in an output data set based on meeting criteria for the number of times of the specified numeric or character field values.

Records that are not selected can be saved in a separate output data set specified with DISCARD.


Syntax: -



From 1 to 10 ON fields can be specified.

All ON fields are used to determine the value count to be matched against the criteria.

The records selected can be limited to those with duplicate values, non-duplicate values, values that occur less than, equal to or more than n times, or the first, first n, or last record with each unique or duplicate value.

DISCARD(savedd) can be used to save the records which do not meet the criteria.

DISCARD(savedd) may be used with or without TO(outdd).

The DFSORT control statements in xxxxCNTL are used if USING(xxxx) is specified.


Required Operands: -


FROM -

Specifies the ddname of the input data set.

DD statement must supply for the ddname specified.


TO -

Specifies the ddname of the output data set for the selected records.

DD statement must supply for the ddname specified.


DISCARD -

Specifies the ddname of the output data set for the records which are not selected.

DD statement must supply for the ddname specified.

TO(outdd) and DISCARD(savedd) may be used together or separately.


ON -

Specifies a field to be used for this operation.

From 1 to 10 ON fields can be specified.

– (p,m,f) gives the position, length and format of a numeric or character field. A field must not extend beyond position 32752 or the end of the record.

– VLEN is equivalent to ON(1,2,BI) for variable-length records, represents the record length for each record.


ALLDUPS, NODUPS, HIGHER, LOWER, EQUAL, FIRST, LAST, FIRSTDUP, LASTDUP –

Defines the criteria against which the value counts are to be matched.

x, y, v, u and w must be specified as n or +n where n can 0 to 99.


Optional Operands: -


VSAMTYPE -

Specifies the record format for a VSAM input data set (F or V).


UZERO -

Causes -0 to be treated as unsigned, that is, as +0.


USING -

Specifies the first 4 characters of the ddname (xxxxCNTL) for the DFSORT control statement data set.

DD statement must supply for xxxxCNTL if USING(xxxx) specified.


Example: -

Below example to select the first two employee records for each different employee country(from 60th column of length 2) which is the selection criteria.


Input: -

MTHUSR.INPUT.DATASET -


JCL: -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
//MTHUSRR JOB (123),'MTHUSR',CLASS=A,MSGCLASS=A,MSGLEVEL=(1,1),         
//             NOTIFY=&SYSUID                                           
//*                                                                     
//STEP01   EXEC PGM=ICETOOL                                             
//INDD     DD DSN=MTHUSR.INPUT.DATASET,DISP=SHR                         
//OUTDD1   DD DSN=MTHUSR.OUTPUT.DATASET1,                               
//            DISP=(NEW,CATLG,DELETE),                                  
//            SPACE=(TRK,(1,1),RLSE),UNIT=SYSDA,                        
//            DCB=(DSORG=PS,RECFM=FB,LRECL=80,BLKSIZE=800)              
//TOOLIN   DD *                                                         
  SELECT FROM(INDD) TO(OUTDD1) ON(60,2,CH) FIRST(2)                     
/*                                                                      
//TOOLMSG  DD DSN=MTHUSR.OUTPUT.TOOLMSG1,DISP=SHR                       
//DFSMSG   DD SYSOUT=*                                                  
//                                                                      
**************************** Bottom of Data ****************************


OUTPUT: -

TOOLMSG: -


MTHUSR.OUTPUT.DATASET1: -


Explaining Solution: -

  • INDD - Specifies the ddname for input dataset.
  • OUTDD1 - Specifies the ddname for output dataset.
  • TOOLIN DD * - Specifies the ICETOOL statements for DFSORT.
  • TOOLMSG - Specifies ICETOOL message data set.
  • DFSMSG - Specifies DFSORT message data set.
  • SELECT FROM(INDD) TO(OUTDD1) ON(60,2,CH) FIRST(2) - Selects the first two records for each individual key specified in the criteria which is from 60th position of length 2.