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
 

 

Compare two input files writing unmatched records to output
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
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Thu Jun 18, 2009 5:55 pm    Post subject: Compare two input files writing unmatched records to output
Reply with quote

Hi,

I have two input files each of LRECL=31, RECFM=FB, KEY=1,5,ZD
Both files will have duplicate records.

First input file:
Code:

----+----1----+----2----+----3-
11111 BHASKAR 2000.00 HYD INDIA
11111 BHASKAR 3000.00 HYD INDIA
22222 SUNIL   1000.00 HYD INDIA
33333 KUMAR   2500.00 VIJ INDIA
44444 TULASI  5000.00 SCT UK   
55555 KTBS    1100.00 VIR US   
55555 KTBS    1200.00 VIR US   


Second input file:
Code:

22222 SUNIL   300.00  HYD INDIA
44444 TULASI  5000.00 SCT UK   
55555 KTBS    1300.00 VIR US   
55555 KTBS    1400.00 VIR US   
66666 BACHI   1110.00 BGL INDIA
66666 BACHI   1000.00 BGL INDIA


First i should eliminate dulicate records from File 1 and then should compare with File 2. After comparison, match and unmatched records should be written to different output files as follows. If there is a match, then all records from file 2 should be written to output match file.

Match:
Code:

22222 SUNIL   300.00  HYD INDIA
44444 TULASI  5000.00 SCT UK   
55555 KTBS    1300.00 VIR US   
55555 KTBS    1400.00 VIR US   


Unmatched records from file 1 to F1ONLY:
Code:

11111 BHASKAR 2000.00 HYD INDIA
11111 BHASKAR 3000.00 HYD INDIA
33333 KUMAR   2500.00 VIJ INDIA


Unmatched records from file 2 to F2ONLY:
Code:

66666 BACHI   1110.00 BGL INDIA
66666 BACHI   1000.00 BGL INDIA


Please guide me how to achieve this.

I was able to achieve this to some extent. But not able to write unmatched records from input FILE1 to F1ONLY output file.

Here is the code:
Code:

//TOOLIN   DD *                                               
  SELECT FROM(FILE1) TO(TEMP1) ON(1,5,PD) FIRST   
  COPY FROM(TEMP1) TO(TEMP) USING(CTL1)                       
  COPY FROM(FILE2) TO(TEMP) USING(CTL2)                       
  SPLICE FROM(TEMP) TO(MATCH) ON(1,5,PD) -                       
  WITH(1,31) WITH(41,1) KEEPBASE WITHALL USING(CTL3)         
/*                                                             
//CTL1CNTL DD *                                               
  INREC OVERLAY=(32:SEQNUM,8,ZD,C'11')                   
/*                                                             
//CTL2CNTL DD *                                               
  INREC OVERLAY=(32:SEQNUM,8,ZD,C'22')                   
/*                                                             
//CTL3CNTL DD *                                               
  OUTFIL FNAMES=MATCH,INCLUDE=(40,2,CH,EQ,C'12'),OUTREC=(1,31)   
  OUTFIL FNAMES=F1ONLY,INCLUDE=(40,2,CH,EQ,C'11'),OUTREC=(1,31)   
  OUTFIL FNAMES=F2ONLY,INCLUDE=(40,2,CH,EQ,C'22'),OUTREC=(1,31)   
/*                                                             


If this is not the proper way of writing the solution, then please help me.
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jun 19, 2009 2:18 am    Post subject:
Reply with quote

bhaskar_kanteti,

The following DFSORT/ICETOOL JCL will give you the desired results. However pay attention to key 4444 from file 1 and 5555 from file 2. I am not writting them to output as of now , but if you want we can write them out.

If do need them in your input then change the following statement
Code:

OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,33)


to

Code:

OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,SS,EQ,C'13,23,33')


Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*
//IN1      DD *                                           
11111 BHASKAR 2000.00 HYD INDIA                           
11111 BHASKAR 3000.00 HYD INDIA                           
22222 SUNIL   1000.00 HYD INDIA                           
33333 KUMAR   2500.00 VIJ INDIA                           
44444 TULASI  5000.00 SCT UK                               
44444 BLAH    5000.00 VST US <-- WILL NOT BE WRITTEN OUT   
55555 KTBS    1100.00 VIR US                               
55555 KTBS    1200.00 VIR US                               
//IN2      DD *                                           
22222 SUNIL   300.00  HYD INDIA                           
44444 TULASI  5000.00 SCT UK                               
55555 KTBS    1300.00 VIR US                               
55555 KTBS    1400.00 VIR US                               
55555 XXXX    1400.00 VST US <-- WILL NOT BE WRITTEN OUT   
66666 BACHI   1110.00 BGL INDIA                           
66666 BACHI   1000.00 BGL INDIA
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)       
//MATCH    DD SYSOUT=*                                             
//ONLYF1   DD SYSOUT=*                                             
//ONLYF2   DD SYSOUT=*                                             
//OUT      DD SYSOUT=* 
//TOOLIN   DD *                                                     
  SORT FROM(IN1) USING(CTL1)                                       
  SORT FROM(IN2) USING(CTL2)                                       
  SORT FROM(T1) USING(CTL3)                                         
//CTL1CNTL DD *                                                     
  SORT FIELDS=(1,5,CH,A)                                           
  OUTFIL FNAMES=T1,BUILD=(1,31,33:C'1',36:SEQNUM,8,ZD,RESTART=(1,5))
//CTL2CNTL DD *                                                     
  SORT FIELDS=(1,5,CH,A)                                           
  OUTFIL FNAMES=T1,BUILD=(1,31,33:C'2',36:SEQNUM,8,ZD,RESTART=(1,5))
//CTL3CNTL DD *                                                     
  SORT FIELDS=(1,5,CH,A,36,8,CH,A),EQUALS                           
  SUM FIELDS=(33,1,ZD)                                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(36:SEQNUM,8,ZD,RESTART=(1,5))),
  IFTHEN=(WHEN=GROUP,BEGIN=(36,8,ZD,EQ,1),PUSH=(34:33,1))           
  OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,33)         
  OUTFIL FNAMES=ONLYF1,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,11)         
  OUTFIL FNAMES=ONLYF2,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,22)         
/*
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Fri Jun 19, 2009 10:46 am    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Hi Skolusu,

Thanks for providing solution. But WHEN=GROUP is not available in my icetool. Is there any alternative solution for this. Please help.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 19, 2009 8:04 pm    Post subject:
Reply with quote

Hello,

Please post the error message(s) along with the message id(s).
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Mon Jun 22, 2009 9:03 am    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Hi Dick scherrer,

The error message i got is shown below.
Code:

ICE000I 0 - CONTROL STATEMENTS FOR 5740-SM1, DFSORT REL 14.0 - 08:52 ON MON JUN
            SORT FIELDS=(1,5,CH,A,36,8,CH,A),EQUALS                             
            SUM FIELDS=(33,1,ZD)                                               
            OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(36:SEQNUM,8,ZD,RESTART=(1,5))),   
            IFTHEN=(WHEN=GROUP,BEGIN=(36,8,ZD,EQ,1),PUSH=(34:33,1))             
                         $                                                     
ICE007A 0 SYNTAX ERROR                                                         
            OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,33)           
            OUTFIL FNAMES=ONLYF1,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,11)           
            OUTFIL FNAMES=ONLYF2,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,22)           
ICE146I 0 END OF STATEMENTS FROM CTL3CNTL - PARAMETER LIST STATEMENTS FOLLOW   
          DEBUG NOABEND,ESTAE                                                   
          OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL3,SORTIN=T1,DY
                         ALLOC                                                 


ICETOOL PTF level is
Code:

ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1


Please let me know if you need any more information.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 22, 2009 9:56 am    Post subject:
Reply with quote

Hello,

You are not yet running the current ptf level which supports WHEN=GROUP:
Quote:
ICE201I F RECORD TYPE ...

the F indicates you have the July, 2008 DFSORT functions (FINDREP, WHEN=GROUP, DATASORT, SUBSET, etc) and all of the earlier functions.
This function level corresponds to z/OS DFSORT V1R5 PTF UK90013 and z/OS DFSORT V1R10 PTF UK90014. You are completely up to date on DFSORT functional PTFs.


If you see:

ICE201I E RECORD TYPE ...

the E indicates you have the April, 2006 DFSORT functions (PARSE, JFY, SQZ, SPLIT1R, etc) and all of the earlier functions. This function level corresponds to z/OS DFSORT V1R5 PTF UK90007. You are behind on DFSORT functional PTFs. Ask your System Programmer to install z/OS DFSORT PTF UK90013.


The update ptf is free - you might ask your management to have the current ptf installed.

There may be an earlier release solution but the IBMers may not be online for a while.
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Mon Jun 22, 2009 11:11 am    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Hi dick scherrer,

Thanks for providing infomation. I already requested regarding this installation to my manager. But i dont think it will happen immediately. Surely it will take time.
Anyways i am still working on this problem. If anyone have solution please share according to your convinicence.
Thanks in advance.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Mon Jun 22, 2009 12:14 pm    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Hi,

Quote:
Thanks for providing infomation. I already requested regarding this installation to my manager. But i dont think it will happen immediately. Surely it will take time


Same here. Found a similar problem and had approched the concerned to install the updated PTF. But am out of the project and still guys are working with the older ptf icon_sad.gif

Wonder what is the reason for the delay in installing this FREE update ptf?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Mon Jun 22, 2009 2:48 pm    Post subject:
Reply with quote

Kolusu,

May be I misunderstood something. But from the original requirement posted by the OP, it seems like he was trying to write the matching records from file-2 alone into the output-file MATCH. For e.g.,the output records for the key 55555 from the OPs expected output is
Code:
55555 KTBS    1300.00 VIR US   
55555 KTBS    1400.00 VIR US   
whereas I think your job might give this for an INCLUDE=(33,2,SS,EQ,C'13,23,33').
Code:
55555 KTBS    1100.00 VIR US
55555 KTBS    1200.00 VIR US
55555 XXXX    1400.00 VST US
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Jun 22, 2009 8:58 pm    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

arcvns,

Did you miss reading this statement in my post?

kolusu wrote:
However pay attention to key 4444 from file 1 and 5555 from file 2. I am not writting them to output as of now , but if you want we can write them out.

If do need them in your input then change the following statement
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Mon Jun 22, 2009 10:32 pm    Post subject:
Reply with quote

Skolusu wrote:
I am not writting them to output as of now , but if you want we can write them out.
If do need them in your input then change the following statement
Code:
OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,33)
to
Code:
OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,SS,EQ,C'13,23,33')
Kolusu,

I did not miss that. But I wonder if that matches with the expected output posted by the TS. Would the above change give the expected output posted by the OP like this for 55555 records?
Code:
55555 KTBS    1300.00 VIR US   
55555 KTBS    1400.00 VIR US 
icon_rolleyes.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Jun 22, 2009 11:00 pm    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

arcvns,

I just gave him an option if he wanted to pick those records as I am not sure about the rules. If he doesn't need the records from file 2 , he can just remove the '23' from the include cond.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Mon Jun 22, 2009 11:33 pm    Post subject:
Reply with quote

bhaskar_kanteti wrote:
If there is a match, then all records from file 2 should be written to output match file.
Match: wrote:
22222 SUNIL 300.00 HYD INDIA
44444 TULASI 5000.00 SCT UK
55555 KTBS 1300.00 VIR US
55555 KTBS 1400.00 VIR US
Skolusu,

As per my understanding, for all unique keys from file-1, he needs to extract all matching records from file-2. Considering that the above file-2 records will be missed out in your output due to the SUM FIELDS. I am just trying to check if my understanding is correct. Correct me if I am wrong. icon_rolleyes.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 23, 2009 12:14 am    Post subject:
Reply with quote

arcvns,

*Sigh* Did you see on what fields I am sorting on to come up with the value of 3? I am not only summing on the keys but also a seqnum for each key at pos 36, thus making the match a 1 to 1 match. If summed value is 3 I am pushing it down it all the other records making 13,23,33.

Code:

----+----1----+----2----+----3----+----4----
22222    FILE-1 RECORD             00000001
44444    FILE-1 RECORD             00000001
55555    FILE-1 RECORD             00000001
55555    FILE-1 RECORD             00000002
                                           
22222    FILE-2 RECORD             00000001
44444    FILE-2 RECORD             00000001
55555    FILE-2 RECORD             00000001
55555    FILE-2 RECORD             00000002


Now after the sum I would have 2 records with 33 for 5555 key and that is written to the output.

Btw I do test the solutions before I post.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Tue Jun 23, 2009 1:14 am    Post subject:
Reply with quote

Quote:
Now after the sum I would have 2 records with 33 for 5555 key and that is written to the output.

Btw I do test the solutions before I post.
I totally agree with that. But after the SUM, will you get the FILE-2 records out of the above input?
Code:
55555    FILE-2 RECORD             00000001
55555    FILE-2 RECORD             00000002
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 23, 2009 1:37 am    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Arcvns,

I am not sure as to what the confusion in here is. The intention is to get the FILE-1 matched duplicate records which the job gets. Why do you need file-2 records? . The FILE-1 records are concatenated first so after the sum you would have 2 records from file1. Since they are matched , you don't have to worry about file-2 records as the intention is to get file-1 matching records.

Quote:

55555 FILE-1 RECORD 00000001
55555 FILE-1 RECORD 00000002


Run this job and let me know if I missed anything

Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//SORTIN   DD *                             
----+----1----+----2----+----3----+----4----+
22222    FILE-1 RECORD          1  00000001 
44444    FILE-1 RECORD          1  00000001 
55555    FILE-1 RECORD          1  00000001 
55555    FILE-1 RECORD          1  00000002 
22222    FILE-2 RECORD          2  00000001 
44444    FILE-2 RECORD          2  00000001 
55555    FILE-2 RECORD          2  00000001 
55555    FILE-2 RECORD          2  00000002 
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                             
  SORT FIELDS=(1,5,CH,A,36,8,CH,A),EQUALS   
  SUM FIELDS=(33,1,ZD)                       
/*
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Tue Jun 23, 2009 2:01 am    Post subject:
Reply with quote

bhaskar_kanteti wrote:
If there is a match, then all records from file 2 should be written to output match file.
Skolusu wrote:
you don't have to worry about file-2 records as the intention is to get file-1 matching records.
The OP was asking for matched records from file-2 and hence the confusion. You never mentioned that we need to swap the input files.
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Wed Jun 24, 2009 9:33 am    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

Hi,

Anyone got alternate solution for my requirement. I am still trying to get solution. But not able to get desired result. Kindly guide me.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jun 24, 2009 8:14 pm    Post subject:
Reply with quote

Hello,

Quote:
Anyone got alternate solution for my requirement.
There has most likely been no solution because there are different understandings of what would be "the right solution".

Suggest you closely re-read all of the posts to this topic and reply with a completely clear description of what you need to accomplish. Your post should clarify any/all of the previous posts that show different or confused understandings.

Post a complete (but small) set of all input data needed to show the requirement and the exact output(s) you want from this new set of input.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 24, 2009 9:18 pm    Post subject: Reply to: Compare two input files writing unmatched records
Reply with quote

bhaskar_kanteti,

Here is a DFSORT/ICETOOL JCL which will give you the desired(based on my understanding of the question ) without the WHEN=GROUP function.

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN1      DD *                                             
11111 BHASKAR 2000.00 HYD INDIA                             
11111 BHASKAR 3000.00 HYD INDIA                             
22222 SUNIL   1000.00 HYD INDIA                             
33333 KUMAR   2500.00 VIJ INDIA                             
44444 TULASI  5000.00 SCT UK                                 
44444 BLAH    5000.00 VST US                                 
55555 KTBS    1100.00 VIR US                                 
55555 KTBS    1200.00 VIR US                                 
//IN2      DD *                                             
22222 SUNIL   300.00  HYD INDIA                             
44444 TULASI  5000.00 SCT UK                                 
55555 KTBS    1300.00 VIR US                                 
55555 KTBS    1400.00 VIR US                                 
55555 XXXX    1400.00 VST US                                 
66666 BACHI   1110.00 BGL INDIA                             
66666 BACHI   1000.00 BGL INDIA                             
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//MATCH    DD SYSOUT=*                                       
//ONLYF1   DD SYSOUT=*                                       
//ONLYF2   DD SYSOUT=*                                       
//TOOLIN   DD *                                                       
  SORT FROM(IN2) USING(CTL2)                                         
  SORT FROM(IN1) USING(CTL1)                                         
  SPLICE FROM(T1) ON(1,5,CH) TO(MATCH) WITHALL WITH(1,33) -           
  KEEPBASE KEEPNODUPS USING(CTL3)                                     
//CTL1CNTL DD *                                                       
  SORT FIELDS=(1,5,CH,A)                                             
  OUTFIL FNAMES=T1,BUILD=(1,31,33:C'11',36:SEQNUM,8,ZD,RESTART=(1,5))
//CTL2CNTL DD *                                                       
  SORT FIELDS=(1,5,CH,A)                                             
  OUTFIL FNAMES=T1,BUILD=(1,31,33:C'22',36:SEQNUM,8,ZD,RESTART=(1,5))
//CTL3CNTL DD *                                                       
  SORT FIELDS=(1,5,CH,A,36,8,CH,A)                                   
  SUM FIELDS=(34,1,ZD)                                               
  OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,23)           
  OUTFIL FNAMES=ONLYF1,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,11)           
  OUTFIL FNAMES=ONLYF2,BUILD=(1,31),INCLUDE=(33,2,ZD,EQ,22)           
/*
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 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 Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am


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