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 Quick References
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: 1337
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: 1337
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: 2141
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: 1337
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: 1337
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: 2141
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 JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts JES datasets IO Error ABENDS & Debugging 3
No new posts Concatenate 2 input datasets and give... JCL & VSAM 2
No new posts To Omit records based n SORT condition DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us