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
 
Filtering data from file2 that only exist in file1

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

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Thu Jun 12, 2008 4:43 pm    Post subject: Filtering data from file2 that only exist in file1
Reply with quote

hi guys,

i have a master_file(IN1) and a transaction_file(IN2). my requirement for these files is to get the records from the transaction_file that does not exists from the master_file. with that i've used the following code:

Code:

//STEP15   EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN1      DD *
RECORDONE1FROMMASTERFILE
RECORDTWO2FROMMASTERFILE
RECORD3333FROMMASTERFILE
//IN2      DD *
RECORDTWO2FROMTRANSACTIONFILE
RECORD3333FROMTRANSACTIONFILE
RECORDFIVEFROMTRANSACTIONFILE
RECORDFIVEFROMTRANSACTIONFILE
//T1       DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)
//OUT1     DD DSN=XALN.XISDD10.JCLBK.DETHOLID,
//            DISP=(NEW,CATLG,DELETE),AVGREC=K,
//            SPACE=(CYL,(1,1),RLSE),RECFM=FB,LRECL=99
//TOOLIN   DD *
  COPY FROM(IN1) TO(T1) USING(CTL1)
  COPY FROM(IN2) TO(T1) USING(CTL2)
  SELECT FROM(T1) TO(OUT1) ON(1,10,CH) ALLDUPS USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(100:C'1')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(100:C'2')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(100,1,CH,EQ,C'2'),
    BUILD=(1,99)
/*


however, the above code would also get the records with duplicates from the transaction_file (IN2), like RECORDFIVE, which is not correct since this record does not exists from the master_file.

what can you suggest to filter out only those records from the transaction_file that only exists from the master_file. please advise. thank you very much.
Back to top
View user's profile Send private message

hallecodec

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Thu Jun 12, 2008 5:06 pm    Post subject: Reply to: Filtering data from file2 that only exist in file1
Reply with quote

apologies, but my desired output for this requirement should be:
Code:

RECORDTWO2FROMTRANSACTIONFILE
RECORD3333FROMTRANSACTIONFILE


however, the code i've posted displays the following output:
Code:

RECORDTWO2FROMTRANSACTIONFILE
RECORD3333FROMTRANSACTIONFILE
RECORDFIVEFROMTRANSACTIONFILE
RECORDFIVEFROMTRANSACTIONFILE


please advise on what code should i use to come up with my desired output. thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jun 13, 2008 1:41 am    Post subject: Reply to: Filtering data from file2 that only exist in file1
Reply with quote

hallecodec,

The following DFSORT/ICETOOL JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN1      DD *                                             
RECORDONE1FROMMASTERFILE                                     
RECORDTWO2FROMMASTERFILE                                     
RECORD3333FROMMASTERFILE                                     
//IN2      DD *                                             
RECORDTWO2FROMTRANSACTIONFILE                               
RECORD3333FROMTRANSACTIONFILE                               
RECORDFIVEFROMTRANSACTIONFILE                               
RECORDFIVEFROMTRANSACTIONFILE                               
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY FROM(IN1) USING(CTL1)                                 
  COPY FROM(IN2) USING(CTL2)                                 
  SPLICE FROM(T1) TO(OUT) ON(01,10,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(01,100) USING(CTL3)                           
//CTL1CNTL DD *                                             
  OUTFIL FNAMES=T1,BUILD=(1,10,101:1,10)                     
//CTL2CNTL DD *                                             
  OUTFIL FNAMES=T1,OVERLAY=(101:10X)                         
//CTL3CNTL DD *                                             
  OUTFIL FNAMES=OUT,BUILD=(1,100),                           
  INCLUDE=(1,10,CH,EQ,101,10,CH,AND,11,90,CH,NE,C' ')       
/*


Hope this helps...
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 Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am

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