Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Calculating FIELDS difference and Sum of all differences

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
DS
Warnings : 1

New User


Joined: 09 Jun 2005
Posts: 24
Location: Bangalore

PostPosted: Wed Dec 26, 2007 7:27 am    Post subject: Calculating FIELDS difference and Sum of all differences
Reply with quote

File 1
------

Code:

001 Jason Caren 020302
002 Andrew K    005670
005 Thomas Jr.  050793
008 Ebby Arthur 053121
009 Simon       032010
011 Campbell    007600
012 Jhuan Jes   045018
015 Sion Mason  042809
019 Shane W     038910
023 Herman Tr   006109


File 2
------

Code:

001 Jason Caren 025027
005 Thomas Jr.  050793
006 Jerry Tom   036931
007 Schlember   000793
008 Ebby Arthur 050381
010 Crump Jo    035175
011 Campbell    010502
012 Jhuan Jes   045018
014 Calman      023611
019 Shane W     037103
021 Jane Well   043801
023 Herman Tr   012109


1. Record structure for both files is

Code:

   01  PERS-RECORD.
       03  EMP-NUM-KEY   PIC  X(03).
       03  FILLER      PIC  X.
       03  EM-NAME      PIC  X(11).
       03  FILLER      PIC  X.
       03  EMP-SALARY                    PIC  X(06).
       03  FILLER      PIC  X(58).


Addl details : RECFM=FB,LRECL=80 both files sorted individually on EMP-NUM-KEY.
Both files has to compare each other and prepare 3 reports like follows.

Output FILES reqired are
i) Records exist in File 1 only and sum of all salaries in trailer line.
ii) Records present only in File 2 and all salaries sum in trailer line.
iii) Difference of salary (File 1 sal - File 2 sal)
if employee present in both files.
Eg: Emp 001 --> 001 Jason Caren -19357 (File 1 Sal - File 2 Sal)
and sum of all these differences in trailer line.

Plz help with a solution. I searched the forum POSTs but not able to see similar one. If any one finds similar one plz let me know.

Thanks & Regards,
DS.
Back to top
View user's profile Send private message

shankar.v

Active User


Joined: 25 Jun 2007
Posts: 196
Location: Bangalore

PostPosted: Wed Dec 26, 2007 1:44 pm    Post subject:
Reply with quote

DS,

Please check with the following code for your requirement.
Code:
// EXEC PGM=ICETOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//FILE1 DD *
001 JASON CAREN 020302
002 ANDREW K    005670
005 THOMAS JR.  050793
008 EBBY ARTHUR 053121
009 SIMON       032010
011 CAMPBELL    007600
012 JHUAN JES   045018
015 SION MASON  042809
019 SHANE W     038910
023 HERMAN TR   006109
/*
//FILE2 DD *
001 JASON CAREN 025027
005 THOMAS JR.  050793
006 JERRY TOM   036931
007 SCHLEMBER   000793
008 EBBY ARTHUR 050381
010 CRUMP JO    035175
011 CAMPBELL    010502
012 JHUAN JES   045018
014 CALMAN      023611
019 SHANE W     037103
021 JANE WELL   043801
023 HERMAN TR   012109
/*
//T DD DSN=&&T,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT DD DSN=&&OUT,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//F1ONLY DD SYSOUT=*
//F2ONLY DD SYSOUT=*
//BOTH DD SYSOUT=*
//TOOLIN DD *
 COPY FROM(FILE1) TO(T) USING(CTL1)
 COPY FROM(FILE2) TO(T) USING(CTL2)
 SPLICE FROM(T) TO(OUT) ON(1,3,CH) WITHALL  WITH(81,6) WITH(88,1) -
 USING(CTL3) KEEPNODUPS
 COPY FROM(OUT) TO(BOTH) USING(CTL4)
/*
//CTL1CNTL DD *
 INREC OVERLAY=(87:C'11')
//CTL2CNTL DD *
 INREC OVERLAY=(81:17,6,C'22')
//CTL3CNTL DD *
 OUTFIL  FNAMES=F1ONLY,
        INCLUDE=(87,2,CH,EQ,C'11'),
         OUTREC=(1,80),
       TRAILER1=(C'SUM OF ALL SALARIES: ',TOT=(17,6,ZD,M11,LENGTH=8)),
       REMOVECC
 OUTFIL  FNAMES=F2ONLY,
        INCLUDE=(87,2,CH,EQ,C'22'),
         OUTREC=(1,80),
       TRAILER1=(C'SUM OF ALL SALARIES: ',TOT=(17,6,ZD,M11,LENGTH=8)),
       REMOVECC
 OUTFIL  FNAMES=OUT,
        INCLUDE=(87,2,CH,EQ,C'12'),
         OUTREC=(1,16,17,6,ZD,SUB,81,6,ZD,EDIT=(STTTTT),SIGNS=(,-),
                 23,58)
/*
//CTL4CNTL DD *
 OUTFIL FNAMES=BOTH,
  TRAILER1=(C'SUM OF ALL SALARIES: ',TOT=(18,5,ZD,M11,LENGTH=8)),
        REMOVECC
/*
//

F1ONLY:
Code:
002 ANDREW K    005670       
009 SIMON       032010       
015 SION MASON  042809       
SUM OF ALL SALARIES: 00080489

F2ONLY:
Code:
006 JERRY TOM   036931       
007 SCHLEMBER   000793       
010 CRUMP JO    035175       
014 CALMAN      023611       
021 JANE WELL   043801       
SUM OF ALL SALARIES: 00140311

BOTH:
Code:
001 JASON CAREN -04725       
005 THOMAS JR.   00000       
008 EBBY ARTHUR  02740       
011 CAMPBELL    -02902       
012 JHUAN JES    00000       
019 SHANE W      01807       
023 HERMAN TR   -06000       
SUM OF ALL SALARIES: 00018174
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Dec 26, 2007 10:46 pm    Post subject:
Reply with quote

DS,

Shankar is on the right track, but his job does NOT give the correct total for the matched file. It should be -9080, but his job gives +18174.

Here's a DFSORT/ICETOOL job that will give the correct results:

Code:

//S1 EXEC PGM=ICETOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//FILE1 DD *
001 JASON CAREN 020302
002 ANDREW K    005670
005 THOMAS JR.  050793
008 EBBY ARTHUR 053121
009 SIMON       032010
011 CAMPBELL    007600
012 JHUAN JES   045018
015 SION MASON  042809
019 SHANE W     038910
023 HERMAN TR   006109
/*
//FILE2 DD *
001 JASON CAREN 025027
005 THOMAS JR.  050793
006 JERRY TOM   036931
007 SCHLEMBER   000793
008 EBBY ARTHUR 050381
010 CRUMP JO    035175
011 CAMPBELL    010502
012 JHUAN JES   045018
014 CALMAN      023611
019 SHANE W     037103
021 JANE WELL   043801
023 HERMAN TR   012109
/*
//T1 DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(5,5),RLSE)
//T2 DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(5,5),RLSE)
//F1ONLY DD SYSOUT=*
//F2ONLY DD SYSOUT=*
//BOTH DD SYSOUT=*
//TOOLIN DD *
COPY FROM(FILE1) TO(T1) USING(CTL1)
COPY FROM(FILE2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,3,CH) WITHALL  WITH(81,6) WITH(88,1) -
  USING(CTL3) KEEPNODUPS
COPY FROM(T2) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(87:C'11')
//CTL2CNTL DD *
  INREC OVERLAY=(81:17,6,C'22')
//CTL3CNTL DD *
  OUTFIL FNAMES=F1ONLY,REMOVECC,
    INCLUDE=(87,2,CH,EQ,C'11'),
    BUILD=(1,80),
    TRAILER1=(C'SUM OF ALL SALARIES: ',
      TOT=(17,6,ZD,M11,LENGTH=8))
  OUTFIL FNAMES=F2ONLY,REMOVECC,
    INCLUDE=(87,2,CH,EQ,C'22'),
    BUILD=(1,80),
    TRAILER1=(C'SUM OF ALL SALARIES: ',
      TOT=(17,6,ZD,M11,LENGTH=8))
  OUTFIL FNAMES=T2,
    INCLUDE=(87,2,CH,EQ,C'12'),
    BUILD=(1,16,17,6,ZD,SUB,81,6,ZD,EDIT=(STTTTT),SIGNS=(,-),
           23,58)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=BOTH,REMOVECC,
    TRAILER1=(C'SUM OF ALL SALARIES: ',
      TOT=(17,6,FS,EDIT=(STTTTTTT),SIGNS=(,-)))
/*
Back to top
View user's profile Send private message
DS
Warnings : 1

New User


Joined: 09 Jun 2005
Posts: 24
Location: Bangalore

PostPosted: Tue Jan 01, 2008 3:37 am    Post subject: Thanks Shankar
Reply with quote

Shankar,

Thank you very much for your help.

Regards,
DS.
Back to top
View user's profile Send private message
DS
Warnings : 1

New User


Joined: 09 Jun 2005
Posts: 24
Location: Bangalore

PostPosted: Tue Jan 01, 2008 3:39 am    Post subject: Marvellous Mentoring
Reply with quote

Frank,

Thanks and Appreciate your Marvellous Mentoring..

Regards,
DS.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 5 Sat Jan 21, 2017 4:02 am
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us