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 files on a key record & write into a new fil

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

New User


Joined: 16 May 2009
Posts: 12
Location: Bangalore

PostPosted: Sat Feb 16, 2013 3:50 pm    Post subject: Compare TWO files on a key record & write into a new fil
Reply with quote

Hi Guys,

I have requirement where I have compare TWO files with first file of length 5551 and second of 9(which will have only the key record which will be compared with first file) .Then compare the key(of length 9) in first file in the position 422 and second file in position 1.

If key record matches in both files then include a comment 'Amendment' and if its a new record and doesn't match the key record then include a comment 'New' - This will be written to third file which will of length 5560 (Where last 9 places will be the 2 comments which I have mentioned in quotes above).

P.S : The third file contents is always a copy of FIRST file + comments in the last 9 position.
FILE 1:
Code:

Pos1           Pos 422                          Pos 5551
Harsha ......111111111...................................
Pradeep.....222222222...................................


FILE 2:
Code:

Pos1
111111111


OUT FILE :

Code:
Pos1           Pos 422                          Pos 5551     Pos 5560
Harsha ......111111111....................................Amendment
Pradeep.....222222222.....................................New

This I tried to do using COBOL program using SEARCH and two READ statements. Somehow all the time I am ending up in getting only NEW as comment in the output irrespective of the key match and un-match.

So started testing using ICETOOL and the used below common JCL, which is roaming around in forum for quite a long.But somehow it's becoming little hard to fit into my requirement.

Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input File1
//IN2 DD DSN=... input File2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5))
//* USE MOD FOR T1
// DISP=(MOD,PASS)
//OUT13 DD SYSOUT=* records in File1 and File2
//OUT1 DD SYSOUT=* records in File1 only
//OUT3 DD SYSOUT=* records in File2 only
//TOOLIN DD *
* Add '11' identifier for FILE1 records.
COPY FROM(IN1) TO(T1) USING(CTL1)
*Add '22' identifier for FILE2 records.
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE to match up records and write them to their appropriate output files.
SPLICE FROM(T1) TO(OUT12) ON(422,9,CH) WITH(1,9) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
* Mark FILE1 records with '11'
INREC OVERLAY=(12:C'11')
/*
//CTL2CNTL DD *
* Mark FILE2 records with '33'
INREC OVERLAY=(12:C'33')
/*
//CTL3CNTL DD *
**** Write matching records to OUT13 file and removes id.
OUTFIL FNAMES=OUT13,INCLUDE=(12,2,CH,EQ,C'13'),BUILD=(1,1*)

**** Write FILE1 only records to OUT1 file and removes id.
OUTFIL FNAMES=OUT1,INCLUDE=(12,2,CH,EQ,C'11'),BUILD=(1,1*)

**** Write FILE2 only records to OUT3 file and removes id.
OUTFIL FNAMES=OUT3,INCLUDE=(12,2,CH,EQ,C'33'),BUILD=(1,1*)
/*
 


Could you please guide with the right JCL or valuable comments ??

Code'd
Back to top
View user's profile Send private message

mandyaharsha

New User


Joined: 16 May 2009
Posts: 12
Location: Bangalore

PostPosted: Sat Feb 16, 2013 3:55 pm    Post subject: Sorry,this post should go to 'DFSORT/ICETOOL'
Reply with quote

DFSORT/ICETOOL
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sat Feb 16, 2013 4:29 pm    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

You need to find an example of JOINKEYS.

Using the "match marker", it is easy.


Code:
  JOIN UNPAIRED,F1,F2
  REFORMAT FIELDS=(F1:1,5551,F2:1,9,?)


Code:
  INREC IFOUTLEN=55560,
               IFTHEN=(WHEN=(5561,1,CH,EQ,C'B'),
                    OVERLAY=(5552:C'Amendment')),
               IFTHEN=(WHEN=(5561,1,CH,EQ,C'1'),
                    OVERLAY=(5552:C'New')),
               IFTHEN=(WHEN=OTHER,
                    OVERLAY=(1:5552,9,5552:C'Pickle'))
Back to top
View user's profile Send private message
mandyaharsha

New User


Joined: 16 May 2009
Posts: 12
Location: Bangalore

PostPosted: Sun Feb 17, 2013 3:22 pm    Post subject: Compare TWO files on a key record & write into a new fil
Reply with quote

Hi Bill and All,

Below is the JCL I coded :

Code:
//STEP013  EXEC PGM=SORT                                       
//SORTJNF1 DD DSN=INPUT1,DISP=SHR         --> Length 5551 - Key in 422 Pos
//SORTJNF2 DD DSN=INPUT2,DISP=SHR         --> Length 9      - Key in 1st Pos
//SORTOUT  DD DSN=OUTPUT,                                       
//            SPACE=(TRK,(51,5),RLSE),DISP=(NEW,CATLG,CATLG), 
//            DCB=(LRECL=5560,RECFM=FB,BLKSIZE=0)             
//SYSOUT   DD SYSOUT=*                                         
//SYSIN DD *                                                   
  JOINKEYS FILE=F1,FIELDS=(1,5551,A)                           
  JOINKEYS FILE=F2,FIELDS=(1,9,A)                             
  JOIN UNPAIRED,F1,F2                                         
  OPTION COPY                                                 
  REFORMAT FIELDS=(F1:1,5551,F2:1,9)                           
  INREC IFOUTLEN=5560,                                         
                IFTHEN=(WHEN=(5551,1,CH,EQ,C'B'),             
                     OVERLAY=(5552:C'Amendment')),             
                IFTHEN=(WHEN=(5551,1,CH,EQ,C'1'),             
                     OVERLAY=(5552:C'New'))                   
/*                                                         


I'm getting below error in sysout though the length of the file I've mentioned is right.Also,want to know if the above JCL is right for the requirement I've mentioned in my first post.

Code:
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 09:40 ON SUN FE
            JOINKEYS FILE=F1,FIELDS=(1,5551,A)                                 
            JOINKEYS FILE=F2,FIELDS=(1,9,A)                                     
            JOIN UNPAIRED,F1,F2                                                 
            OPTION COPY                                                         
            REFORMAT FIELDS=(F1:1,5551,F2:1,9)                                 
            INREC IFOUTLEN=5560,                                               
                          IFTHEN=(WHEN=(5551,1,CH,EQ,C'B'),                     
                               OVERLAY=(5552:C'Amendment')),                   
                          IFTHEN=(WHEN=(5551,1,CH,EQ,C'1'),                     
                               OVERLAY=(5552:C'New'))                           
ICE405A 0 JOINKEYS STATEMENTS HAD MISMATCH IN NUMBER, LENGTH OR ORDER OF KEYS   
ICE751I 0 C5-K51707 C6-K51707 C7-K51707 E7-K51707                               
ICE052I 3 END OF DFSORT


Thank you.
Harsha

Code'd
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sun Feb 17, 2013 3:32 pm    Post subject: Re: Compare TWO files on a key record & write into a new
Reply with quote

mandyaharsha wrote:
ICE405A 0 JOINKEYS STATEMENTS HAD MISMATCH IN NUMBER, LENGTH OR ORDER OF KEYS
Is not this hint enough, provided you've coded this:
Code:
JOINKEYS FILE=F1,FIELDS=(1,5551,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sun Feb 17, 2013 3:33 pm    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

You have specified your F1 key as starting in position 1 for a length of 5551.

Please use the Code tags in future posts where you need to preserve spacing.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sun Feb 17, 2013 4:12 pm    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

Having "Code'd" your post, I now see that you have changed the REFORMAT and IFTHENs that I provided.

How do you expect that to work, even when you have changed your key to 442,9?

The ? in the REFORMAT statement is documented in the DFSORT manuals. I suggest you look it up, and understand it.
Back to top
View user's profile Send private message
mandyaharsha

New User


Joined: 16 May 2009
Posts: 12
Location: Bangalore

PostPosted: Mon Feb 18, 2013 12:59 pm    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

Hi Bill,

I could resolve the error I was getting and below is what I have coded.

Code:
//SYSIN DD *                                           
  JOINKEYS FILE=F1,FIELDS=(422,9,A)                   
  JOINKEYS FILE=F2,FIELDS=(1,9,A)                     
  JOIN UNPAIRED,F1,F2                                 
  OPTION COPY
  REFORMAT FIELDS=(F1:1,5551,F2:1,9,?)                 
  INREC IFOUTLEN=5560,                                 
                IFTHEN=(WHEN=(5551,1,CH,EQ,C'B'),     
                     OVERLAY=(5552:C'Amendment')),     
                IFTHEN=(WHEN=(5551,1,CH,EQ,C'1'),     
                     OVERLAY=(5552:C'New'))           
/*


Alas!! The output doesn't giving the data I'm expecting.The first file is getting copied completely inopt output and for the matching keys,the key field is copied from (5552 to 5560) instead of 'Amendment' and 'New'.

P.S : I removed WHEN=OTHERS statement as I was getting errros and I feel this statement won't make any difference.

Please guide me meanwhile I'm trying with trial and error method by referring the DFSORT manual.

Thank you.
Harsha

Code'd. Again
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Mon Feb 18, 2013 1:14 pm    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

Please use the Code tags.

Change your tests to 5561.

The "OTHER" was a joke, as you'd not explained how to deal with on file 2, not on file 1.

You have on your JOIN statement F1 and F2, so what do you want to do with the unpaired F2s?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue Feb 19, 2013 12:02 am    Post subject: Reply to: Compare TWO files on a key record & write into
Reply with quote

mandyaharsha,

Your control cards are a mess. As bill pointed out

1. Your match indicator is at position 5561 and you are checking for 5552.

2. When you use JOIN UNPAIRED,F1,F2 you would also get the non matching records from FILE2 and you don't have a condition to handle that.

3. I am not even sure why you need to pad the key from File2 at position 5552 as you don't seem to have any use for it.

Assuming you only wanted the matched and unmatched records from just FILE 1 with constants appended at the end then use the following DFSORT control cards.

Code:

//SYSIN    DD *                                                 
  JOINKEYS FILE=F1,FIELDS=(422,9,A)                             
  JOINKEYS FILE=F2,FIELDS=(001,9,A)                             
  JOIN UNPAIRED,F1                                               
  OPTION COPY                                                   
  REFORMAT FIELDS=(F1:1,5551,?)                                 
  INREC IFTHEN=(WHEN=(5552,1,CH,EQ,C'B'),OVERLAY=(5552:C'AMENDMENT')), 
        IFTHEN=(WHEN=(5552,1,CH,EQ,C'1'),OVERLAY=(5552:C'NEW'))             
//* 
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 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
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm


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