Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Compare Two datasets and Override Matched records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Compare Two datasets and Override Matched records
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

Active User


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

PostPosted: Mon Apr 27, 2020 8:13 pm    Post subject:
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    Post subject:
Reply with quote

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

Active User


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

PostPosted: Mon Apr 27, 2020 9:19 pm    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 859
Location: Maryland

PostPosted: Mon Apr 27, 2020 11:08 pm    Post subject:
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

Active User


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

PostPosted: Mon Apr 27, 2020 11:25 pm    Post subject:
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

Active User


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

PostPosted: Tue Apr 28, 2020 9:58 am    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 859
Location: Maryland

PostPosted: Tue Apr 28, 2020 4:27 pm    Post subject:
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    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 JCL to extract repeating records saikarthik94 DFSORT/ICETOOL 6 Mon Oct 05, 2020 8:05 pm
No new posts AN OVERRIDE MAY NOT TARGET OVERRIDING... HithaSri JCL & VSAM 1 Mon Sep 21, 2020 1:11 am
No new posts Group selective VB records - Trailer ... Premnath_TJ DFSORT/ICETOOL 16 Sun Sep 13, 2020 3:38 am
No new posts Compare two character variables Head ... Harold Barnes CLIST & REXX 3 Fri Aug 28, 2020 12:38 am
This topic is locked: you cannot edit posts or make replies. SORT the records based on the second ... Ramsee SYNCSORT 1 Wed Aug 19, 2020 3:01 pm

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us