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
 

 

Icetool or Joinkeys example to compare two files
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Icetool or Joinkeys example to compare two files
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: 159
Location: Chennai

PostPosted: Sat Aug 20, 2016 2:51 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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    Post subject:
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: 159
Location: Chennai

PostPosted: Sat Aug 20, 2016 3:14 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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    Post subject:
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    Post subject:
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: 159
Location: Chennai

PostPosted: Sat Aug 20, 2016 4:05 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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    Post subject:
Reply with quote

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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Aug 22, 2016 11:56 pm    Post subject:
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: 159
Location: Chennai

PostPosted: Tue Aug 23, 2016 12:44 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Aug 23, 2016 1:38 am    Post subject:
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    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Tue Aug 23, 2016 1:53 am    Post subject:
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    Post subject:
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: 159
Location: Chennai

PostPosted: Tue Aug 23, 2016 2:37 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Aug 26, 2016 1:55 am    Post subject:
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: 159
Location: Chennai

PostPosted: Fri Aug 26, 2016 2:37 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Aug 26, 2016 2:46 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Aug 29, 2016 9:57 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Tue Aug 30, 2016 4:34 am    Post subject: Reply to: Icetool or Joinkeys example to compare two files
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm


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