IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare Two datasets and Override Matched records


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ArunkumarGanesan

New User


Joined: 09 Jan 2020
Posts: 8
Location: USA

PostPosted: Mon Apr 27, 2020 7:45 pm
Reply with quote

Hi,
i am trying to compare two FB datasets with same record length of 80 on 1,34 and override 35,6 only when matched without changing the record order.

I tried the below but the unmatched records are written at bottom.

F1: LRECL=80, FB

Code:
**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.               
*                                                 
**************************************************
                                                 
*MNTH                      START  NEXT           
*ID   VL REGION ARA PRODCT SEQ    SEQ             
*==== == ====== === ====== ====== ======         
  MAY 12 400972 770 XXXXXA 000502 000000 SEEDS   
  MAY 12 400972 781 XXXXXB 000502 000000 SEEDS   
  MAY 12 400972 789 XXXXXC 000502 000000 SEEDS   
  MAY 12 400972 791 XXXXXD 000506 000000 SEEDS   
                                                 
  MAY 12 400977 416 XAXAXA 001002 000000 SEEDS   
  MAY 12 400977 416 XAXAXB 701002 000000 SEEDS   
  MAY 12 400977 421 XAXAXC 201002 000000 SEEDS   
  MAY 12 400977 421 XAXAXD 501002 000000 SEEDS   
  MAY 12 400977 425 XAXAXE 201002 000000 SEEDS   
  MAY 12 400977 425 XAXAXF 501002 000000 SEEDS   
  MAY 12 400977 425 XAXAXG 001002 000000 SEEDS   
  MAY 12 400977 425 XAXAXH 701002 000000 SEEDS   
  MAY 12 400977 426 XAXAXI 201003 000000 SEEDS   
  MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS   


F2:
Code:

  MAY 12 400972 770 XXXXXA 000502 001456     
  MAY 12 400972 781 XXXXXB 000502 004456 
  MAY 12 400972 789 XXXXXC 000502 001233 
  MAY 12 400972 791 XXXXXD 000506 000045 
                                                 
  MAY 12 400977 416 XAXAXA 001002 000022     
  MAY 12 400977 416 XAXAXB 701002 000001 
  MAY 12 400977 421 XAXAXC 201002 034556     
  MAY 12 400977 421 XAXAXD 501002 003422     
  MAY 12 400977 425 XAXAXE 201002 000099     
  MAY 12 400977 425 XAXAXF 501002 007566 
  MAY 12 400977 425 XAXAXG 001002 196663 
  MAY 12 400977 425 XAXAXH 701002 000301 
  MAY 12 400977 426 XAXAXI 201003 000000 
  MAY 12 400977 426 XAXAXJ 501004 000000     

SYSIN:
Code:
JOINKEYS FILE=F1,FIELDS=(1,34,A)                   
JOINKEYS FILE=F2,FIELDS=(1,34,A)
JOIN UNPAIRED,F1                       
REFORMAT FIELDS=(F1:1,80,?,F2:35,6)                 
OPTION COPY                                         
OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'), 
       BUILD=(1,34,82,6,41,40))                   


Output from above sort:
Code:

  MAY 12 400972 770 XXXXXA 000502 001456 SEEDS   
  MAY 12 400972 781 XXXXXB 000502 004456 SEEDS   
  MAY 12 400972 789 XXXXXC 000502 001233 SEEDS   
  MAY 12 400972 791 XXXXXD 000506 000045 SEEDS   
                                                 
  MAY 12 400977 416 XAXAXA 001002 000022 SEEDS   
  MAY 12 400977 416 XAXAXB 701002 000001 SEEDS   
  MAY 12 400977 421 XAXAXC 201002 034556 SEEDS   
  MAY 12 400977 421 XAXAXD 501002 003422 SEEDS   
  MAY 12 400977 425 XAXAXE 201002 000099 SEEDS   
  MAY 12 400977 425 XAXAXF 501002 007566 SEEDS   
  MAY 12 400977 425 XAXAXG 001002 196663 SEEDS   
  MAY 12 400977 425 XAXAXH 701002 000301 SEEDS   
  MAY 12 400977 426 XAXAXI 201003 000000 SEEDS   
  MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS

**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.               
*                                                 
**************************************************
                                                 
*MNTH                      START  NEXT           
*ID   VL REGION ARA PRODCT SEQ    SEQ             
*==== == ====== === ====== ====== ======   

Expected Ouptut:
Code:

**************************************************
* UPDATE AS REQUIRED FOR EACH RUN.               
*                                                 
**************************************************
                                                 
*MNTH                      START  NEXT           
*ID   VL REGION ARA PRODCT SEQ    SEQ             
*==== == ====== === ====== ====== ======       
  MAY 12 400972 770 XXXXXA 000502 001456 SEEDS   
  MAY 12 400972 781 XXXXXB 000502 004456 SEEDS   
  MAY 12 400972 789 XXXXXC 000502 001233 SEEDS   
  MAY 12 400972 791 XXXXXD 000506 000045 SEEDS   
                                                 
  MAY 12 400977 416 XAXAXA 001002 000022 SEEDS   
  MAY 12 400977 416 XAXAXB 701002 000001 SEEDS   
  MAY 12 400977 421 XAXAXC 201002 034556 SEEDS   
  MAY 12 400977 421 XAXAXD 501002 003422 SEEDS   
  MAY 12 400977 425 XAXAXE 201002 000099 SEEDS   
  MAY 12 400977 425 XAXAXF 501002 007566 SEEDS   
  MAY 12 400977 425 XAXAXG 001002 196663 SEEDS   
  MAY 12 400977 425 XAXAXH 701002 000301 SEEDS   
  MAY 12 400977 426 XAXAXI 201003 000000 SEEDS   
  MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS


Thanks in advance
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 27, 2020 8:13 pm
Reply with quote

Code:
//SYSIN    DD *                                       
  JOINKEYS F1=F1,FIELDS=(1,34,A)                     
  JOINKEYS F2=F2,FIELDS=(1,34,A)                     
  JOIN UNPAIRED,F1                                   
  REFORMAT FIELDS=(F1:1,80,?,F2:35,6,F1:100,8)       
  SORT FIELDS=(88,8,ZD,A)                             
  OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'), 
         BUILD=(1,34,82,6,41,40))                     
  END                                                 
/*                                                   
//JNF1CNTL DD *                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(100:SEQNUM,8,ZD)) 
  END                                                 
/*                                                   
//JNF2CNTL DD *                                       
  END                                                 
/*

Output:
Code:
000001 ************************************************** 
000002 * UPDATE AS REQUIRED FOR EACH RUN.                 
000003 *                                                   
000004 ************************************************** 
000005                                                     
000006 *MNTH                      START  NEXT             
000007 *ID   VL REGION ARA PRODCT SEQ    SEQ               
000008 *==== == ====== === ====== ====== ======           
000009   MAY 12 400972 770 XXXXXA 000502 001456 SEEDS     
000010   MAY 12 400972 781 XXXXXB 000502 004456 SEEDS     
000011   MAY 12 400972 789 XXXXXC 000502 001233 SEEDS     
000012   MAY 12 400972 791 XXXXXD 000506 000045 SEEDS     
000013                                                     
000014   MAY 12 400977 416 XAXAXA 001002 000022 SEEDS     
000015   MAY 12 400977 416 XAXAXB 701002 000001 SEEDS     
000016   MAY 12 400977 421 XAXAXC 201002 034556 SEEDS     
000017   MAY 12 400977 421 XAXAXD 501002 003422 SEEDS     
000018   MAY 12 400977 425 XAXAXE 201002 000099 SEEDS     
000019   MAY 12 400977 425 XAXAXF 501002 007566 SEEDS     
000020   MAY 12 400977 425 XAXAXG 001002 196663 SEEDS     
000021   MAY 12 400977 425 XAXAXH 701002 000301 SEEDS     
000022   MAY 12 400977 426 XAXAXI 201003 000000 SEEDS     
000023   MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS     
Back to top
View user's profile Send private message
ArunkumarGanesan

New User


Joined: 09 Jan 2020
Posts: 8
Location: USA

PostPosted: Mon Apr 27, 2020 9:14 pm
Reply with quote

Thank you Joerg. Worked as expected.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 27, 2020 9:19 pm
Reply with quote

ArunkumarGanesan wrote:
Thank you Joerg. Worked as expected.

As it should. Thanks for the positive feedback. icon_cool.gif
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Mon Apr 27, 2020 11:08 pm
Reply with quote

Frankly speaking, this way of resolving such a problem is a sort of workaround: processing all garbage/headers/etc. records along with real data lines, and the re-order resulting lines by initial record numbers. Some accidentally matching garbage/header records may produce unexpected results.

Correct way would be:
1) Omit all non-data records from both input files by using INCLUDE/OMIT parameters of JOINKEYS statements
2) Match/build/overlay required data-only lines as needed
3) In OUTFIL statement, re-generate clear new HEADER/TRAILER/SECTION records as needed.

It looks a bit longer to code, but logically it would be more correct way of processing this case.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 27, 2020 11:25 pm
Reply with quote

From the presented data it should not be such a great overhead to incorporate your recommendations here. icon_wink.gif
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Tue Apr 28, 2020 9:58 am
Reply with quote

Code:
//SYSIN    DD *                                                       
  JOINKEYS F1=F1,FIELDS=(1,34,A)                                     
  JOINKEYS F2=F2,FIELDS=(1,34,A)                                     
  JOIN UNPAIRED,F1                                                   
  REFORMAT FIELDS=(F1:1,80,?,F2:35,6,F1:100,8)                       
  SORT FIELDS=(88,8,ZD,A)                                             
  OUTREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),                 
    BUILD=(1,34,82,6,41,40))                                         
  OUTFIL FNAMES=(SORTOUT),
    REMOVECC,                                           
    HEADER1=(50C'*',/,C'* UPDATE AS REQUIRED FOR EACH RUN.',/,C'*',/,
             50C'*',/),                                               
    HEADER2=(C'*MNTH                      START  NEXT',/,             
             C'*ID   VL REGION ARA PRODCT SEQ    SEQ',/,             
             C'*==== == ====== === ====== ====== ======'),           
    SECTIONS=(3,3,7,2,10,6,                                           
      TRAILER3=(X))                                                   
  END                                                                 
/*                                                                   
//JNF1CNTL DD *                                                       
  INCLUDE COND=((3,3,SS,EQ,                                           
                 C'JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC'),
             AND,(7,2,ZD,EQ,NUM),AND,(10,6,ZD,EQ,NUM))               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(100:SEQNUM,8,ZD))                 
  END                                                                 
/*                                                                   
//JNF2CNTL DD *                                                     
  INCLUDE COND=((3,3,SS,EQ,                                         
                 C'JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC'),
             AND,(7,2,ZD,EQ,NUM),AND,(10,6,ZD,EQ,NUM))               
  END                                                               
/*

Output:
Code:
000001 **************************************************   
000002 * UPDATE AS REQUIRED FOR EACH RUN.                   
000003 *                                                   
000004 **************************************************   
000005                                                     
000006 *MNTH                      START  NEXT               
000007 *ID   VL REGION ARA PRODCT SEQ    SEQ               
000008 *==== == ====== === ====== ====== ======             
000009   MAY 12 400972 770 XXXXXA 000502 001456 SEEDS       
000010   MAY 12 400972 781 XXXXXB 000502 004456 SEEDS       
000011   MAY 12 400972 789 XXXXXC 000502 001233 SEEDS       
000012   MAY 12 400972 791 XXXXXD 000506 000045 SEEDS       
000013                                                     
000014   MAY 12 400977 416 XAXAXA 001002 000022 SEEDS       
000015   MAY 12 400977 416 XAXAXB 701002 000001 SEEDS       
000016   MAY 12 400977 421 XAXAXC 201002 034556 SEEDS       
000017   MAY 12 400977 421 XAXAXD 501002 003422 SEEDS       
000018   MAY 12 400977 425 XAXAXE 201002 000099 SEEDS       
000019   MAY 12 400977 425 XAXAXF 501002 007566 SEEDS       
000020   MAY 12 400977 425 XAXAXG 001002 196663 SEEDS       
000021   MAY 12 400977 425 XAXAXH 701002 000301 SEEDS       
000022   MAY 12 400977 426 XAXAXI 201003 000000 SEEDS       
000023   MAY 12 400977 426 XAXAXJ 501004 000000 SEEDS       
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Tue Apr 28, 2020 4:27 pm
Reply with quote

Yes, this is what I meant icon_exclaim.gif

Besides of this, after all non-data records are filtered out, it becomes possible to rely on pre-sorted order of input data. When using FIELDS=(1,34,A),SORTED it would become unnecessary neither to include SEQNUM, nor to re-sort the final result on this SEQNUM field.

This is irrelevant for small sample "toy projects", but becomes much more serious when huge datasets are to be joined.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top