Page 1 of 1

How can we count and compare the records using ICETOOL?

PostPosted: Tue Feb 17, 2009 11:58 am
by bunny2
HI ALL
I have requirement on ICETOOL which is as follows:
1. I will have two files i.e. (1.before updation file and 2. File with identifiers (22,12)).
2. From the file with identifiers I need to get the count of records with different identifiers.

Ex: Count of Records with the identifier 12: 5(B)
Count of Records with the identifier 22: 10(C)
3. Then I need to get the sum of them (B+C)

Ex: Total no of records are : 15 (B+C)

4. Then I need to get the total count of the before updation file(i.e A) .and this count should be compared with the sum of the B and C .
Here there are 2 cases (i.e.,A=(B+C))
1. If total no of records are equal:
Then the Report will look like ,

The title for the report DATE TIME
------------------------------------------------------

DESCRIPTION COUNT
-------------------- ----------------
The no of records with identifier 12 5
The no of records with identifier 22 10
------------------------------------------------------- -----------------
The total no of record are 15(B+C)
------------------------------------------------------- -----------------

2. If the total no of records are not equal :
Then we need to find the difference of the count of Before updation file (i.e A) and (B+C) and report that .
i.e.

The title for the report DATE TIME
------------------------------------------------------

DESCRIPTION COUNT
-------------------- ----------------
The no of records with identifier 12 5
The no of records with identifier 22 10
------------------------------------------------------- -----------------
The no of records that are missed in after updation are 2(say) ( A-(B+C))
------------------------------------------------------ -----------------

Re: How can we count and compare the records using ICETOOL?

PostPosted: Wed Feb 18, 2009 12:39 am
by Frank Yaeger
Please show an example of the records in each input file.

What is the RECFM and LRECL of each input file?

What is the starting position of the identifier field that has '22' or '12' in input file2?

I need to get the total count of the before updation file(i.e A)


Is A just the count of the total records in input file1?

What length do you want for the numeric values in the report?

Re: How can we count and compare the records using ICETOOL?

PostPosted: Wed Feb 18, 2009 4:15 pm
by bunny2
HI Frank Yaeger ,

Example of the records in 1.before updation file is as follows:

OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS
OPEN SYS MANGLR 00010FGHGH CHEEGHHJJKKKKSE SDB01HBRBROSCCVSCOSCCVSSS

Example of the records in 2. File with identifiers is as follows:
Last 2 positions identifiers
00001FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H12
00002FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H12
00003FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC22 H22
00004FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC22 H22
00005FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC22 H22
00006FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC22 H22
00007FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H12
00008FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H12
00009FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H12
00010FGHGH CHEEGHHJJKKKKSE OPEN SYS MANGLR SDB01HBRBROSCCVSC12 H22


RECFM is FB and LRECL is 72 for both the files.

Starting position is 71 for both the files.

yes A Jst the count of the total records in input file1(i.e 1.before updation file )

Can you pls tell me what do you mean by "What length do you want for the numeric values in the report?"?

Re: How can we count and compare the records using ICETOOL?

PostPosted: Wed Feb 18, 2009 4:18 pm
by bunny2
Starting position is 71 for both the files

sorry! Starting position is 71 for both the identifiers

Re: How can we count and compare the records using ICETOOL?

PostPosted: Thu Feb 19, 2009 3:16 am
by skolusu
Bunny2,

The following DFSORT/ICETOOL JCL will give you the desired results.

//STEP0100 EXEC PGM=ICETOOL 
//TOOLMSG  DD SYSOUT=*       
//DFSMSG   DD SYSOUT=*       
//I1       DD DSN=before updation file,DISP=SHR
//I2       DD DSN=File with identifiers,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(TRK,(1,1),RLSE)
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY FROM(I1) USING(CTL1)                                 
  COPY FROM(I2) USING(CTL2)                                 
  SORT FROM(T1) USING(CTL3)                                 
//CTL1CNTL DD *                                             
  OUTFIL FNAMES=T1,REMOVECC,NODETAIL,BUILD=(80X),           
  TRAILER1=(X,COUNT=(M11,LENGTH=8),X,8C'0',X,8C'0',X,8C'0')
/* 
//CTL2CNTL DD *                                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(73:C'000')),             
  IFTHEN=(WHEN=(71,2,CH,EQ,C'12'),OVERLAY=(73:C'101')),     
  IFTHEN=(WHEN=(71,2,CH,EQ,C'22'),OVERLAY=(73:C'011'))       
                                                             
  OUTFIL FNAMES=T1,REMOVECC,NODETAIL,BUILD=(80X),           
  TRAILER1=(X,8C'0',X,                                       
            TOT=(73,1,ZD,M11,LENGTH=8),X,                   
            TOT=(74,1,ZD,M11,LENGTH=8),X,                   
            TOT=(75,1,ZD,M11,LENGTH=8))                     
/*
//CTL3CNTL DD *                                                     
  SORT FIELDS=(1,1,CH,A)                                           
  SUM FIELDS=(2,8,11,8,20,8,29,8),FORMAT=ZD                         
  OUTFIL FNAMES=OUT,IFOUTLEN=80,                                   
  IFTHEN=(WHEN=(2,8,ZD,EQ,29,8,ZD),                                 
  BUILD=(25:C'THE TITLE FOR THE REPORT',X,DATE1(/),X,TIME,/,       
         80C'-',/,                                                 
         C'DESCRIPTION COUNT',/,                                   
         80C'-',/,                                                 
         C'THE NO OF RECORDS WITH IDENTIFIER 12 : ',11,8,ZD,/,     
         C'THE NO OF RECORDS WITH IDENTIFIER 22 : ',20,8,ZD,/,     
         80C'-',/,                                                 
         C'THE TOTAL NO OF RECORD ARE           : ',02,8,ZD,/,     
         80C'-')),                                                 
  IFTHEN=(WHEN=NONE,                                               
  BUILD=(25:C'THE TITLE FOR THE REPORT',X,DATE1(/),X,TIME,/,       
         80C'-',/,                                                 
         C'DESCRIPTION COUNT',/,                                   
         80C'-',/,                                                 
         C'THE NO OF RECORDS WITH IDENTIFIER 12 : ',11,8,ZD,/,     
         C'THE NO OF RECORDS WITH IDENTIFIER 22 : ',20,8,ZD,/,     
         80C'-',/,                                                 
         C'THE NO OF RECORDS THAT ARE MISSED IN ',                 
         C'AFTER UPDATION ARE : ',2,8,ZD,SUB,29,8,ZD,M10,LENGTH=8,/,
         80C'-'))                                                   
/*                                                                 


If the counts match then the report will be as follows

                        THE TITLE FOR THE REPORT 2009/02/18 14:30:11   
------------------------------------------------------------------------
DESCRIPTION COUNT                                                       
------------------------------------------------------------------------
THE NO OF RECORDS WITH IDENTIFIER 12 :        5                         
THE NO OF RECORDS WITH IDENTIFIER 22 :        5                         
------------------------------------------------------------------------
THE TOTAL NO OF RECORD ARE           :       10                         
------------------------------------------------------------------------


If the counts don't match then the report will be as follows

                        THE TITLE FOR THE REPORT 2009/02/18 14:29:07
---------------------------------------------------------------------
DESCRIPTION COUNT                                                   
---------------------------------------------------------------------
THE NO OF RECORDS WITH IDENTIFIER 12 :        5                     
THE NO OF RECORDS WITH IDENTIFIER 22 :        5                     
---------------------------------------------------------------------
THE NO OF RECORDS THAT ARE MISSED IN AFTER UPDATION ARE :        3   
---------------------------------------------------------------------

Re: How can we count and compare the records using ICETOOL?

PostPosted: Thu Feb 19, 2009 10:21 am
by bunny2
Thank you skolusu