Sum up values with comma and compare the totals



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sum up values with comma and compare the totals

Postby venky_jm1 » Tue Feb 05, 2013 11:30 am

I have file1, file2 and file3 with a trailer record as follows:
file1:
TOTAL amounts TRANSFERRED TO bank1 $100,000.25
file2:
TOTAL amounts TRANSFERRED TO bank2 $200,000.25
file3:
TOTAL amounts TRANSFERRED TO bank3 $300,000.50

I would like sum up file1's total and file2's total(300,000.50) and see if it matches file3's total and populate a new trailer record accordingly.
If it matches:
BANK1 AND BANK2 TOTAL MATCHES BANK3'S TOTAL

else:
BANK1 AND BANK2 TOTAL DOES NOT MATCH BANK3'S TOTAL
venky_jm1
 
Posts: 2
Joined: Tue Feb 05, 2013 11:19 am
Has thanked: 1 time
Been thanked: 0 time

Re: Sum up values with comma and compare the totals

 

Re: Sum up values with comma and compare the totals

Postby skolusu » Tue Feb 05, 2013 10:57 pm

venky_jm1,

You need to provide more details.

1. What is the LRECL and RECFM of all the 3 Files?
2. What is the position, length and format of the Amount field?
3. Does all the 3 files have a trailer record? and is it always identified with the word 'TOTAL' ? If so what position is the word TOTAL.
4. What happens if You don't find a TOTAL record in one of the 2 sets i.e FILE1 and FILE2 have a TOTAL record , but FIL3 doesn't. Similarly the reverse case also. FILE3 having TOTAL record and FILE1 and FILE2 not having a TOTAL record.
5. Last but not least run the following job and show us the complete sysout which will help us determine the level of DFSORT you have
//STEP0100 EXEC PGM=SORT 
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD *           
DUMMY RECORD             
//SORTOUT  DD SYSOUT=*   
//SYSIN    DD *           
  OPTION COPY             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Sum up values with comma and compare the totals

Postby venky_jm1 » Thu Feb 07, 2013 1:39 am

Thanks Kolusu for looking into this, please see below for my updates:

1. What is the LRECL and RECFM of all the 3 Files? LRECL=133, RECFM=FBA
2. What is the position, length and format of the Amount field? format of the amount field - PIC $$$,$$$,$$$.99CR ; file1 & file2's amount field starting pos-51 and end-pos-64;; file3's amount field starting pos-41 and end-pos-54
3. Does all the 3 files have a trailer record? and is it always identified with the word 'TOTAL' ? If so what position is the word TOTAL. Yes, TOTAL starts at pos-3
4. What happens if You don't find a TOTAL record in one of the 2 sets i.e FILE1 and FILE2 have a TOTAL record , but FIL3 doesn't. Similarly the reverse case also. FILE3 having TOTAL record and FILE1 and FILE2 not having a TOTAL record. There will be a TOTAL record all the time in all three files but the amount can be zero on someday.
5. Last but not least run the following job and show us the complete sysout which will help us determine the level of DFSORT you have - please see attached

1ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K76585 E7-K70685
 ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 14:18 ON WED FEB 06, 2013 -
0            OPTION COPY
 ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
 ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K76585 E7-K70685
 ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
 ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
 ICE088I 0 VXJSORTB.STEP0100.        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
 ICE092I 0 MAIN STORAGE = (23515096,23515096,23501878)
 ICE156I 0 MAIN STORAGE ABOVE 16MB = (23424438,23424438)
 ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
 ICE128I 0 OPTIONS: SIZE=23515096,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
 ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO   ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
 ICE130I 0 OPTIONS: RESALL=0,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
 ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
 ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
 ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
 ICE235I 0 OPTIONS: NULLOUT=RC0
 ICE236I 0 OPTIONS: DYNAPCT=20 ,MOWRK=Y
 ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
 ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
 ICE751I 1 EF-BASE   F0-K66717 E8-K79103
 ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
 ICE055I 0 INSERT 0, DELETE 0
 ICE054I 0 RECORDS - IN: 1, OUT: 1
 ICE052I 0 END OF DFSORT
venky_jm1
 
Posts: 2
Joined: Tue Feb 05, 2013 11:19 am
Has thanked: 1 time
Been thanked: 0 time

Re: Sum up values with comma and compare the totals

Postby skolusu » Sat Feb 09, 2013 12:30 am

use the following DFSORT JCL which will give you the desired results. I added a few checks to have the job return an RC=4 when you have a missing TOTAL record in any files. Also I am handling multiple TOTAL records in File3

//STEP0100  EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                               
//INA      DD DISP=SHR,DSN=Your File1
//         DD DISP=SHR,DSN=Your File2
//*
//INB      DD DISP=SHR,DSN=Your File3
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY,NULLOUT=RC4                                           
  JOINKEYS F1=INA,FIELDS=(1,5,A)                                     
  JOINKEYS F2=INB,FIELDS=(1,5,A)                                     
  REFORMAT FIELDS=(F1:1,19,F2:1,19)                                 
  INREC IFTHEN=(WHEN=(6,14,ZD,EQ,25,14,ZD),                         
  BUILD=(C'BANK1 AND BANK2 TOTAL MATCHES BANK3''',C'S TOTAL')),     
  IFTHEN=(WHEN=NONE,                                                 
  BUILD=(C'BANK1 AND BANK2 TOTAL DOES NOT MATCH BANK3''',C'S TOTAL'))
//*                                                                 
//JNF1CNTL DD *                                                     
  INCLUDE COND=(3,5,CH,EQ,C'TOTAL')                                 
  INREC IFTHEN=(WHEN=INIT,BUILD=(3,5,51,14)),                       
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=6,IN=C'CR',OUT=C'- ')),       
  IFTHEN=(WHEN=INIT,OVERLAY=(6:6,14,SFF,ZD,LENGTH=14))               
  SUM FIELDS=(6,14,ZD)                                               
//*                                                                 
//JNF2CNTL DD *                                                     
  INCLUDE COND=(3,5,CH,EQ,C'TOTAL')                                 
  INREC IFTHEN=(WHEN=INIT,BUILD=(3,5,41,14)),                       
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=6,IN=C'CR',OUT=C'- ')),       
  IFTHEN=(WHEN=INIT,OVERLAY=(6:6,14,SFF,ZD,LENGTH=14))
  SUM FIELDS=(6,14,ZD)                                                             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
venky_jm1 (Sat Feb 09, 2013 7:18 pm)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post