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

only unmatched records of two files with duplicates..


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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
Reply with quote

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

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
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
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
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
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
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: 2455
Location: Hampshire, UK

PostPosted: Thu Apr 07, 2011 7:17 pm
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
Reply with quote

ICE250I 0 VISIT 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

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Thu Apr 07, 2011 8:26 pm
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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Thu Apr 07, 2011 9:21 pm
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
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
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
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
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 Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Fri Apr 08, 2011 10:22 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top