Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need to extract only the unmatched records

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

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Tue Aug 24, 2010 4:13 pm    Post subject: Need to extract only the unmatched records
Reply with quote

Hi,

I have the following requirement on which i need some help asap.


Input File 1 : (Will never have dups)

111
222
333
444

Input File 2 : (May or may not have dups and may or may not be in sorted manner)

222
222
111
666
666
555

Output File 1 :

333
444

Output file 2 :

555
666

I need to delete records from Input File 1 as mentioned in input file 2 and put the remaining records in output file 1. Any unmatched record from input file 2 like 555 in the example would go to the output file 2.
Inputs for i/p file 2 will user modified so it may not be in sorted order or unique. Again, if the input file 2 has duplicates which are not present in file 1 so it would appear in the o/p file 2 only once like 666.
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Aug 24, 2010 7:11 pm    Post subject:
Reply with quote

Souren,
Please post your DFSort function level. Also please post RECFM and LRECL for both the input file. Is your input file1 always sorted on first 3 bytes?

Use this link to determine your DFSort function level :-
http://www.ibmmainframes.com/viewtopic.php?t=33389

Thanks,
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: Tue Aug 24, 2010 11:41 pm    Post subject:
Reply with quote

Souren,

I assumed your input file has RECFM=FB and LRECL=80, but the jobs can be changed for other attributes.

If you have the DFSORT PTF for JOINKEYS, you can use a DFSORT job like the following.

Code:

//S1    EXEC  PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
111
222
333
444
//IN2 DD *
222
222
111
666
666
555
//OUT1 DD DSN=...  output file1 (FB/80)
//OUT2 DD DSN=...  output file2 (FB/80)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,3,A)
  JOINKEYS F2=IN2,FIELDS=(1,3,A)
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
  OMIT COND=(161,1,CH,EQ,C'B')
  INREC IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80,161,1)),
        IFTHEN=(WHEN=(161,1,CH,EQ,C'2'),BUILD=(81,80,161,1))
  SORT FIELDS=(1,3,CH,A)
  SUM FIELDS=NONE
  OUTFIL FNAMES=OUT1,INCLUDE=(81,1,CH,EQ,C'1'),BUILD=(1,80)
  OUTFIL FNAMES=OUT2,INCLUDE=(81,1,CH,EQ,C'2'),BUILD=(1,80)
/*


If you don't have the DFSORT PTF for JOINKEYS, you can use an ICETOOL job like the following:

Code:

//S2    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
111
222
333
444
//IN2 DD *
222
222
111
666
666
555
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=...  output file1 (FB/80)
//OUT2 DD DSN=...  output file2 (FB/80)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
SELECT FROM(IN2) TO(T1) ON(1,3,CH) FIRST USING(CTL2)
SELECT FROM(T1) TO(OUT1) ON(1,3,CH) NODUPS USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'1')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'2')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(81,1,CH,EQ,C'1'),BUILD=(1,80)
  OUTFIL FNAMES=OUT2,INCLUDE=(81,1,CH,EQ,C'2'),BUILD=(1,80)
/*
Back to top
View user's profile Send private message
ksouren007

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Wed Aug 25, 2010 8:32 pm    Post subject: Reply to: Need to extract only the unmatched records
Reply with quote

Hi Frank,

We dont have DFSORT utility installed in our system so I tried the ICETOOL method and it went fine. It was a great help to me and Thanksss a lot for that!! icon_smile.gif

However one thing if you kindly clarify then i would be able to use the same for any future purpose

SELECT FROM(IN2) TO(T1) ON(1,3,CH) FIRST USING(CTL2)

When its putting a '2' tag on the matched records between this IN1 and IN2 files...what exactly this 'FIRST' keyword is doing? please advice.

Thanks Again for the prompt help!!
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 Aug 25, 2010 10:54 pm    Post subject:
Reply with quote

Quote:
We dont have DFSORT utility installed in our system


Then why did you post in the DFSORT Forum? And why would you expect me to help you further if you don't have DFSORT? BTW, if you have Syncsort, you're using Syncsort's undocumented SYNCTOOL, rather than DFSORT's fully documented ICETOOL.

If you're using Syncsort, please post future questions in the JCL Forum and mention that you're using Syncsort.
Back to top
View user's profile Send private message
ksouren007

New User


Joined: 30 Jun 2010
Posts: 85
Location: Toronto, ON

PostPosted: Thu Aug 26, 2010 10:51 am    Post subject:
Reply with quote

Apologize for that...I newly joined the forum and not much conversant with its different categories of forums...will take care in future..Thanks alot anyways!
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Spool Extract for Z /os 1.04 version Khadhar Basha CLIST & REXX 3 Thu Sep 28, 2017 3:14 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

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