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

Select the records from file 1 that are not in file 2
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Mar 06, 2007 4:02 am
 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:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Tue Mar 06, 2007 4:03 am
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Mar 06, 2007 4:46 am
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Mar 06, 2007 4:56 am
 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
 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
 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: 3156
Location: Tucson AZ
PostPosted: Wed Mar 07, 2007 5:21 am
 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
 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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Mar 07, 2007 6:02 am
 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
 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 Developer


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

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 0
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
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