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

Icetool or Joinkeys example to compare two files


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Sat Aug 20, 2016 2:40 am
Reply with quote

I have a requirement where I need to remove records from one file based on two dates which are present in other file. example :

file 1: (format is date followed by data)

12202016 aaaaaaaaaaaaaa
12212016 bbbbbbbbbbbbbb
12202016 ccccccccccccccccc
12192016 dddddddddddddd
12192016 eeeeeeeeeeeeeee
12212016 fffffffffffffffffffffffff
12212016 gggggggggggggg

file 2: (this file has 2 dates)
1219201612202016

output should be (records from file 1 for dates present in file 2)

12202016 aaaaaaaaaaaaaa
12202016 ccccccccccccccccc
12192016 dddddddddddddd
12192016 eeeeeeeeeeeeeee

can this be done using Icetool or SORT ?
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sat Aug 20, 2016 2:51 am
Reply with quote

Quote:

can this be done using Icetool or SORT ?


Yes.

Quote:

file 2: (this file has 2 dates)
1219201612202016




File 2 always have one record or multiple records ?
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Sat Aug 20, 2016 2:55 am
Reply with quote

file 2 will always have 1 record
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sat Aug 20, 2016 3:14 am
Reply with quote

here is the code for you.
Code:

//SORT1 EXEC PGM=SORT                                 
//SORTIN DD DSN=YOUR FILE 2 HAVING ONE RECORD         
//SORTOUT DD DSN=&&TEMP,DISP=(,CATLG,DELETE),   
//        SPACE=(CYL,(1,1),RLSE)                     
//SYSOUT  DD SYSOUT=*                                 
//SYSIN DD *                                         

  OPTION COPY                                         

  OUTFIL BUILD=(C'REC1,C',X'7D',1,8,X'7D',/,         
                C'REC2,C',X'7D',9,8,X'7D',80:X)   
   
/*
//SORT2 EXEC PGM=SORT                                 
//SYMNAMES DD DSN=&&TEMP,DISP=SHR               
//SORTIN DD DSN=YOUR FILE 1                           
//SORTOUT DD SYSOUT=*                                 
//SYSOUT DD SYSOUT=*                                 
//SYSIN DD * 
                                       
  OPTION COPY                                         
  INCLUDE COND=(1,8,CH,EQ,REC1,OR,1,8,CH,EQ,REC2)   
 
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Sat Aug 20, 2016 3:51 am
Reply with quote

it worked. Thanks a lot for your help. icon_smile.gif
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Sat Aug 20, 2016 3:56 am
Reply with quote

can I also change the output file dates to 01012016

example: records for both the dates should reflect as shown below:

01012016 aaaaaaaaaaaaaa
01012016 ccccccccccccccccc
01012016 dddddddddddddd
01012016 eeeeeeeeeeeeeee

here 01012016 is the replacement for both dates mentioned in file 2 above
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sat Aug 20, 2016 4:05 am
Reply with quote

Add the following code in SORT2 step
Code:

INREC OVERLAY=(1:C'01012016')
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Mon Aug 22, 2016 7:53 pm
Reply with quote

It Worked. Thanks a lot Magesh icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Aug 22, 2016 11:56 pm
Reply with quote

Welcome!!
Please make a use of Code tags. This is what you need to do to achieve through JOINKEYS
Code:
//IN1 DD *                                             
12202016 AAAAAAAAAAAAAA                                 
12212016 BBBBBBBBBBBBBB                                 
12202016 CCCCCCCCCCCCCCCCC                             
12192016 DDDDDDDDDDDDDD                                 
12192016 EEEEEEEEEEEEEEE                               
//IN2 DD *                                             
1219201612220016                                       
//SORTOUT DD SYSOUT=*                                   
//SYSIN DD *                                           
  JOINKEYS F1=IN1,FIELDS=(40,1,A),SORTED,NOSEQCK       
  JOINKEYS F2=IN2,FIELDS=(40,1,A),SORTED,NOSEQCK       
  REFORMAT FIELDS=(F1:1,20,F2:1,16)                     
  JOIN UNPAIRED F1                                     
  INCLUDE COND=(1,8,CH,EQ,21,8,CH,OR,1,8,CH,EQ,29,8,CH)
  OUTREC BUILD=(1,20,60X)                             
  OPTION COPY                                           
//JNF1CNTL DD *           
  INREC OVERLAY=(40:C'1') 
//JNF2CNTL DD *           
  INREC OVERLAY=(40:C'1')
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Aug 23, 2016 12:44 am
Reply with quote

Rohit,

For this problem, joinkey is not at all recommended.

your code is wasting plenty amount of resource because of join keys and unnecessary overlay and pass.

If you are providing this as an example for joinkey, may be, but still there are plenty of examples available for Joinkeys in this forum.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Aug 23, 2016 1:38 am
Reply with quote

Quote:
For this problem, joinkey is not at all recommended.
your code is wasting plenty amount of resource because of join keys and unnecessary overlay and pass.
performance depends on the total volume of the records in context and you don't know yet.
Code:
//JNF1CNTL DD *           
   INREC OVERLAY=(40:C'1') 
 //JNF2CNTL DD *           
   INREC OVERLAY=(40:C'1')

This piece of code can be removed if TS confirms the total file length and also the last byte of the file 1 is always a space. TS looking for JOINKEYS hence the solution is and there are couple of other ways to achieve the same. I have not faced any performance issues with this type of JOINs OR ICETOOL SPICE as yet so it is a choice call.
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Tue Aug 23, 2016 1:48 am
Reply with quote

I had to come back again since I have additional requirement from user. They want this functionality to work only when month from file 2 is "12" in both the dates.

If you think I should open new post. I can do that. I thought since its an additional request, I should add it here.

Thanks,
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Aug 23, 2016 1:53 am
Reply with quote

Quote:
They want this functionality to work only when month from file 2 is "12" in both the dates.
How difficult is for you to modify the above INCLUDE?
Back to top
View user's profile Send private message
ishant chauhan

New User


Joined: 20 Aug 2016
Posts: 7
Location: USA

PostPosted: Tue Aug 23, 2016 1:56 am
Reply with quote

Rohit,
by the time I wrote this new requirement, I had not seen the code you pasted. I wrote it with respect to the code which Magesh suggested since I did not refresh the page.
I can certainly modify the include in the code which you suggested.
Thanks icon_smile.gif
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Aug 23, 2016 2:37 am
Reply with quote

Rohit,

Rohit wrote:

performance depends on the total volume of the records in context and you don't know yet.



Still Joinkey is not recommended, because your are comparing the second file unnecessarily for every record on first file.

for single record compare, Symnames is the ideal way of doing it.

Ishanth,

Here is the modified condition for you

Code:

INCLUDE COND=((1,8,CH,EQ,REC1,OR,1,8,CH,EQ,REC2),AND,1,2,CH,EQ,C'12')
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Aug 26, 2016 1:55 am
Reply with quote

We can discuss on choices as we want but for your point, I don't disagree.
Code:
1,2,CH,EQ,C'12'
The rule for the exclusion should be from file2 that too on both the dates and not from file 1 date.
For JOINKEY's -
Code:
INCLUDE COND=((1,8,CH,EQ,21,8,CH,OR,1,8,CH,EQ,29,8,CH),AND,(1,2,CH,EQ,C'12',AND,9,2,CH,EQ,C'12'))
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Fri Aug 26, 2016 2:37 am
Reply with quote

Rohit,

Rohit wrote:


Code:

INCLUDE COND=((1,8,CH,EQ,21,8,CH,OR,1,8,CH,EQ,29,8,CH),AND,(1,2,CH,EQ,C'12',AND,9,2,CH,EQ,C'12'))




Your include condition is wrong, it should be as below

Code:

INCLUDE COND=((1,8,CH,EQ,21,8,CH,OR,1,8,CH,EQ,29,8,CH),AND,
              (21,2,CH,EQ,C'12',AND,29,2,CH,EQ,C'12'))     


Even if you have corrected the include condition what would be the end result ?

Rohit wrote:

We can discuss on choices as we want but for your point


SYMNAMES solutions is always better than Joinkeys because, SYMNAMES solution can be archived in 2 Pass and Joinkey required three Pass and additional build statements.

The only thing we need to make sure is, SYSMNAMES should always have one record and that can be achieved by having STOPAFT=1 option. Also we can set a return code 04 for empty file, so that we can stop executing next step.

Bill Woodger,

What is your opinion ?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Aug 26, 2016 2:46 am
Reply with quote

Magesh is correct. Even with only one record on F1, JOINKEYS requires resources for two subtasks and the main task.

Searching here should locate confirmation from Kolusu and/or Frank Yaeger. If you get in the habit of keeping things simple, good use of resources often comes with it.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Aug 29, 2016 9:57 pm
Reply with quote

Thanks Magesh for the correction of the offset per REFORMAT.
Thanks Bill and I agree to the point of using SYNAMES as noted before.
No Doubt SYSNAMES is optimal to work but also if TS confirms 'the last byte of file1 is ALWAYS a space' then we can get ride of two subtasks as then we can join on last byte of each file.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Aug 30, 2016 4:34 am
Reply with quote

The subtasks are what processes the data. You can't get rid of them by just not having the JNFnCNTL datasets.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Aug 30, 2016 7:47 am
Reply with quote

Rohit,

Check the spool for Joinkey, you will see three outputs.

Code:

SYSOUT 
JNF1JMSG
JNF2JMSG


JNF1 => 1 Pass.
JNF2 => 1 Pass.
After Join => 1 pass.

ideally any DFSORT Joinkey will have three pass irrespective of JNF1CNTL/JNF2CNTL statements.

In SYMNAMES solution we have only two pass.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Tue Aug 30, 2016 10:37 am
Reply with quote

Nice explanation Mangesh..

I agree with you regardless of volume of data Joinkeys will degrade the performance and it is best practice to use performance efficient code though the impact is minimal

Thanks,
Chandan
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 Shift left VB record without x00 endi... DFSORT/ICETOOL 11
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top