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
 
Select the records from file 1 that are not in file 2

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

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Tue Mar 06, 2007 3:04 am    Post subject: Select the records from file 1 that are not in file 2
Reply with quote

I have two sequential files

//IN1 DD *
ABCDAAA4445011500001013 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
//IN2 DD *
ABCD AAA 4445011500001013 XXXXXXXXXXXXXXXXXXXXXX
ABCD ACB 5140290165263531 XXXXXXXXXXXXXXXXXXXXXX

I want to get the complete records from file 1 (IN1) which are not present in file 2(IN2) and write to ouotput file.

For example in the above case I need only
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
in the out put.

The keys on which I want to compare these files are

Key1 :-
file 1 position 5 length 3
file 2 position 6 lenght 3

key2:-
file 1 position 8 length 20
file 2 postion 10 lenght 20

record lenght of file 1 :- 1600
record lenght of file 2 :- 80

I am new to DFSORT. Can anyone guide me on this.

David
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 Mar 06, 2007 4:02 am    Post subject:
Reply with quote

David,

Welcome to the wonderful world of DFSORT. icon_cool.gif

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed, as in the example you showed, that you do not have any duplicates on the two keys within file1, or within file2.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/1600)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/1600)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(OUT) ON(5,3,CH) ON(8,20,CH) NODUPS USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1601:C'1')
/*
//CTL2CNTL DD *
  INREC BUILD=(5:6,3,8:10,20,1601:C'2')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(1601,1,CH,EQ,C'1'),
    BUILD=(1,1600)
/*


If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 06, 2007 4:03 am    Post subject:
Reply with quote

Hello,

Please see the following - this request is asked regularly.

Quote:
I suspect you can use some variation of the technique shown in the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/


If after reviewing the material, you have questions, please let us know.
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 Mar 06, 2007 4:46 am    Post subject:
Reply with quote

Note that the Smart DFSORT Trick Dick refers to is a general example that uses SPLICE to create three different files - one with the records found only in file1, one with the records found only in file2, and one with the records found in file1 and file2.

This is a simpler case - one output file with the records found only in file1 - so we can use the simpler solution shown with SELECT instead of SPLICE.
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Tue Mar 06, 2007 4:47 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

Hi Frank,

I tried the solution you provided on a sample test data (changing the length)

Code:

//IN1     DD *                                     
ABCDEFG4445011500001013    XXXXXXXXXXXXXXXXXXXXXXX
ABCDJKL4445011500001047    XXXXXXXXXXXXXXXXXXXXXXX
//IN2     DD *                                     
ABCD EFG 4445011500001013                         
ABCD MNO 5140290165263531                         


using the below cards

Code:

//TOOLIN  DD *                                                   
COPY FROM(IN1) TO(T1) USING(CTL1)                                 
COPY FROM(IN2) TO(T1) USING(CTL2)                                 
SELECT FROM(T1) TO(OUT) ON(5,3,CH) ON(8,20,CH) NODUPS USING(CTL3)
//CTL1CNTL DD *                                                   
  INREC OVERLAY=(51:C'1')                                         
//CTL2CNTL DD *                                                   
  INREC BUILD=(5:6,3,8:10,20,51:C'2')                             
//CTL3CNTL DD *                                                   
OUTFIL=FNAMES=OUT,INCLUDE=(51,1,CH,EQ,C'1'),BUILD=(1,50)         


But in the out put I am getting (two records)

Code:

ABCDJKL4445011500001047    XXXXXXXXXXXXXXXXXXXXXXX
       MNO5140290165263531                           


instead of only (one record)

Code:

ABCDJKL4445011500001047    XXXXXXXXXXXXXXXXXXXXXXX


May I know where am I missing anything?

-thanks
Davod.
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 Mar 06, 2007 4:56 am    Post subject:
Reply with quote

I had:

Code:

   OUTFIL FNAMES=OUT,INCLUDE=(1601,1,CH,EQ,C'1'),
     BUILD=(1,1600)


Notice that there are two spaces before 'OUTFIL" and one space after it. This is the correct syntax for the OUTFIL statement.

You have:

Code:

OUTFIL=FNAMES=OUT,INCLUDE=(51,1,CH,EQ,C'1'),BUILD=(1,50)


Notice there are no spaces before OUTFIL and an '=' after it. This is not the correct syntax and, in fact, DFSORT treats the entire statement as a label and ignores it. You need to change your OUTFIL statement to:

Code:

  OUTFIL FNAMES=OUT,INCLUDE=(51,1,CH,EQ,C'1'),BUILD=(1,50)


When I did that for your job, I got one record.

Also, make sure that you use MOD for the //T1 DD statement.
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Tue Mar 06, 2007 5:53 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

Thanks frank..

I got it. I changed it and it worked.

Thanks a lot
David..
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Wed Mar 07, 2007 4:50 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

Though my problem is solved, but I would like to know what if the
input file 1 has duplicates on the key positions mentioned above and I want to have all the records (including duplicates) of input file 1 which are not present in input file 2.

I tried to look into the manual and also the links provided in this post, couldn't find any.

Can some one tell me how to achieve this.

-thanks
David P.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Mar 07, 2007 5:21 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

David P wrote:
I tried to look into the manual and also the links provided in this post, couldn't find any.
Any what, manuals or answers?
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Wed Mar 07, 2007 5:33 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

I couldn't find any answer for the case I mentioned above with duplicates in input file 1.

Hope its clear now... icon_razz.gif

-thanks
David.
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 Mar 07, 2007 6:02 am    Post subject:
Reply with quote

David P,

Please show me an example of the records in each input file and what you expect for the output records. Include all relevant variations.
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Wed Mar 07, 2007 9:03 pm    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

Hi frank,

For example if I have the data in input files as

//IN1 DD *
ABCDAAA4445011500001013 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX

//IN2 DD *
ABCD AAA 4445011500001013 XXXXXXXXXXXXXXXXXXXXXX
ABCD ACB 5140290165263531 XXXXXXXXXXXXXXXXXXXXXX

I want to get the out put as records which are in file 1 only but it should also have all the duplicates also from file 1.

out put should look like:
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX
ABCDAAA4445011500001047 XXXXXXXXXXXXXXXXXXXXXX

Record length and keys positions for the files mentioned as before.

Hope my question is clear now.

-thanks
David P.
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 Mar 07, 2007 10:47 pm    Post subject:
Reply with quote

David,

Here's a DFSORT/ICETOOL job for your new request:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/1600)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/1600)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(5,3,CH) ON(8,20,CH) -
  KEEPBASE KEEPNODUPS WITHALL WITH(1,1601) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(5:6,3,8:10,20,1601:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(1601:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(1601,2,CH,EQ,C'VV'),
    BUILD=(1,1600)
/*
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Thu Mar 08, 2007 3:01 am    Post subject: Re: Select the records from file 1 that are not in file 2
Reply with quote

Thanks Frank,

It worked perfectly fine.

-thanks again
David P
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 Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
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 Garbage on output file Hervey Martinez SYNCSORT 4 Wed Sep 27, 2017 12:50 am

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