Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

only unmatched records of two files with duplicates..

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

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Thu Apr 07, 2011 4:50 pm    Post subject: only unmatched records of two files with duplicates..
Reply with quote

Hi,

Here is my two files say File1 & File2 & both the files are having Duplicate records. File1 & File 2 are of different Record lengths.

(only the first 29 bytes which is the matching field for the two files)

File1:

004085993|560000260|20110126|
004085993|560000260|20110126|
004085993|560000260|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|


File2:

004020438|560000052|20110228|
004020438|560000052|20110228|
004085993|560000260|20110126|
004064634|560000264|20110126|
004064634|560000264|20110126|
004062810|560000272|20110316|
004062810|560000272|20110316|


I need only the records from the File1 that are not matching with File2 records with File1's format..

Here the output should be only

004060475|560001338|20110126|
004060475|560001338|20110126|
004060475|560001338|20110126|



I tried with the jcl:


Code:

//SORTJNF1  DD DSN=File1,DISP=SHR                                     
//SORTJNF2  DD DSN=File2,DISP=SHR                                                   
//SORTOUT   DD DSN=SORTOUT,           
//             UNIT=SYSDA,                                 
//             SPACE=(CYL,(500,50),RLSE),                   
//             DISP=(,CATLG,CATLG)   
//SYSIN     DD *                                           
  JOINKEYS FILES=F1,FIELDS=(1,29,A)                         
  JOINKEYS FILES=F2,FIELDS=(1,29,A)                         
  JOIN UNPAIRED,F1,ONLY                                     
  REFORMAT FIELDS=(F1:1,144)                               
  OPTION COPY   



But giving positional error so need help..

1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47
JOINKEYS FILES=F1,FIELDS=(1,29,A)
$
0 STATEMENT DEFINER ERROR
JOINKEYS FILES=F2,FIELDS=(1,29,A)
$
0 STATEMENT DEFINER ERROR
JOIN UNPAIRED,F1,ONLY
$
0 STATEMENT DEFINER ERROR
REFORMAT FIELDS=(F1:1,144)
$
0 STATEMENT DEFINER ERROR
OPTION COPY
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 07, 2011 5:04 pm    Post subject:
Reply with quote

First of all.. using below link let us know your DFSORT level

http://www.ibmmainframes.com/viewtopic.php?t=33389
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 07, 2011 5:16 pm    Post subject:
Reply with quote

Also,
LRECL\RECFM of input file1?
LRECL\RECFM of input file2?
LRECL\RECFM of output file?
Back to top
View user's profile Send private message
D Rishi

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Thu Apr 07, 2011 5:34 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

File1: RECFM = FB / LRECL = 144
File2: RECFM = FB / LRECL = 133 or 152 (not decided yet)

Output is equal to File1
RECFM = FB / LRECL = 144
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Apr 07, 2011 5:37 pm    Post subject:
Reply with quote

Quote:
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 07, 2011 5:47 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Quote:
1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 03:47


I don't think thats enough to find which PTFs are applied.. icon_sad.gif

Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*


D Rishi, Execute above at your shop and show us SYSOUT.(please use code tag while posting...
Back to top
View user's profile Send private message
D Rishi

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Thu Apr 07, 2011 5:48 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

Hi Sambhaji,

Thanx for the info.. Seems ICEtool is working.. as per the links test code you sent.

Can you please give the code if its handy for you in ICETOOL..

Thanks,

D Rishi
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Thu Apr 07, 2011 7:17 pm    Post subject:
Reply with quote

You still have not posted the output that Escapa requested. It is required to see what level of DFSORT PTFs you have installed. Once that is known then the experts can give you a solution that will work with your level.
Back to top
View user's profile Send private message
D Rishi

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Thu Apr 07, 2011 7:32 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 05:15 ON THU AP
OPTION COPY
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K90013 E9-K90013 C9-BASE E5-K31980
ICE142I 0 SRTMSG NOT FOUND - SYSOUT USED
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED
ICE088I 0 I04273BX.STEP020 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ER
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXIT
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAM

ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-K10929 F0-K30362 E8-K90013
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE052I 0 END OF DFSORT


Here is the SYSOUT msg i am copy pasting. Don't know if its okay for you to apprehend.

Thank you Nic.[/img]
Back to top
View user's profile Send private message
Garry Carroll

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Apr 07, 2011 8:26 pm    Post subject:
Reply with quote

DFSORT V1.5 introduced JOINKEYS by PTF s UK51706 and UK51707. Are these applied to your installation?

Garry.
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: Thu Apr 07, 2011 9:21 pm    Post subject:
Reply with quote

D R,

The ICE201I F message indicates you do NOT have the JOINKEYS PTF. So here's a DFSORT/ICETOOL SPLICE job that will do what you asked for:

Code:

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

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Fri Apr 08, 2011 1:11 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

Hi Frank,

I tried with this query but seems to be not working as required as all the records of both the files have merged.

What I need is that the only those records from File1 which all are not matching with File2.
(In other words: File2 may have many records but only those records that are also in File1 should be excluded from File1)

Don't know If my requirement is clear now. icon_confused.gif

Can you please help..

Thanks,
D R
Back to top
View user's profile Send private message
D Rishi

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Fri Apr 08, 2011 1:31 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

Hi,

In the above code I tried to do a little change like..

SPLICE FROM(T1) TO(OUT) ON(1,29,CH) WITHALL KEEPNODUPS KEEPBASE -
WITH(1,145) USING(CTL1)

&

//CTL1CNTL DD *
INREC OVERLAY=(145:C'BB')
OUTFIL FNAMES=T1,REMOVECC


But it also not worked. I don't have much idea icon_sad.gif Please help!!

Thanks,
D R.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Apr 08, 2011 5:02 pm    Post subject: Re: Reply to: only unmatched records of two files with dupli
Reply with quote

D Rishi wrote:
Hi Frank,

I tried with this query but seems to be not working as required as all the records of both the files have merged.

What I need is that the only those records from File1 which all are not matching with File2.
(In other words: File2 may have many records but only those records that are also in File1 should be excluded from File1)

Don't know If my requirement is clear now. icon_confused.gif

Can you please help..

Thanks,
D R


Solution given by Frank works as per your requirement.
Did you notice, IN2 is copied to temp file first and then IN1?
Back to top
View user's profile Send private message
D Rishi

New User


Joined: 01 Mar 2010
Posts: 21
Location: India

PostPosted: Fri Apr 08, 2011 6:12 pm    Post subject: Reply to: only unmatched records of two files with duplicate
Reply with quote

Thank you Sambhaji, for pointing out...

I got the difference & it is working fine...


Frank, Like every time before, again thanking you but with my sincere apologies for previously statement as I was somehow manually validating the files & found my mistakes.


Thanks,

D R.
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: Fri Apr 08, 2011 10:22 pm    Post subject:
Reply with quote

Well, I'm glad you reported that you found your mistake before I read through this thread. Saved me some time. icon_smile.gif
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us