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

Need to extract only the unmatched records
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
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
 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: 577
Location: USA
PostPosted: Tue Aug 24, 2010 7:11 pm
 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 :-
www.ibmmainframes.com/viewtopic.php?t=33389

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Aug 24, 2010 11:41 pm
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Aug 25, 2010 10:54 pm
 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
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
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