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
 

 

Comparing Keys in Different Datasets and copy records
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 12:41 am    Post subject: Comparing Keys in Different Datasets and copy records
Reply with quote

Hi,

I have 2 files

FILE-1 : VB 133. KEY : 1st 4 position.

0001 ABCD 2013-05-01
0005 SDSA 2013-05-01
0004 SDAD 2013-05-01
0002 DASD 2013-05-01

FILE-2 : VB 140. KEY : 1st 4 position.

0006 ADAS 2013-05-02
0001 ABCD 2013-05-02
0008 DSDA 2013-05-02
0007 SDAW 2013-05-02
0002 DASD 2013-05-01

Need to compare the Keys in the FILE-1 and FILE-2 and created the 3rd file which will have all non matching records and if we have matching key records then copy the record from FILE-B to the output file.

FILE-OUT may look like this:

0001 ABCD 2013-05-02
0002 DASD 2013-05-01

0004 SDAD 2013-05-01
0005 SDSA 2013-05-01
0006 ADAS 2013-05-02
0007 SDAW 2013-05-02
0008 DSDA 2013-05-02


Please help to know if this can be achieved using ICETOOL and how ?

Thanks !!!
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue May 21, 2013 12:52 am    Post subject:
Reply with quote

Hi,

If I understand your requirement
You need matching records from file 2 and non matched from both files correct?
Also do you expect the output in the order given above?
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 1:00 am    Post subject:
Reply with quote

Yes, That would be correct. Matching records from FILE-2 and non matching from both the files.

If its possible to have it in the sorted order that would be preferred otherwise can always add a SORT.

Thanks !
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue May 21, 2013 1:33 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

Does it have to be ICETOOL? A plain SORT with JOINKEYS will do it.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 2:14 am    Post subject:
Reply with quote

No. I am just looking for a solution to achieve this also the inputs are VB with different lengths
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 3:37 am    Post subject:
Reply with quote

I never used JOINKEYS or ICETOOL.

Can anyone help with the solution ? Or let know if its not possible to implement in 1 or 2 steps ?



Thanks !
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue May 21, 2013 4:14 am    Post subject:
Reply with quote

ranjitbhingare wrote:
I never used JOINKEYS or ICETOOL.

Can anyone help with the solution ? Or let know if its not possible to implement in 1 or 2 steps ?



Thanks !


Go to page 3 of this section and look up the topic "JOINKEYS isssue with RECFM" and look up the solution in page 2 of that topic.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue May 21, 2013 4:15 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

Well, there are lots of examples here, and there are some good manuals (link in the forum, and at the top of the page).

It is a JOINKEYS you need, you'll need the ? (which tells you whether the current REFORMAT record has data from File "1" or File "2" or "B"oth files) on a REFORMAT statement (along with the data you need from the two files).

It is a simple JOINKEYS process.

The only "complicated" thing is that both inputs are VB. So, leave that aside for the moment, and do your processing with "DD *" for your input (Fixed, LRECL 80). Get the process working, if you get stuck post back here with what you have tried, and the problem you have encountered.

Once working with Fixed Length Records, sort it out for Variable Length. Here it is more likely to be problematic, but if you get stuck, there'll be someone here to assist.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 6:52 am    Post subject:
Reply with quote

Hi,

By going through the manual I came up with following card :


//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:5,15,F2:5,15),FILL=C'$'
SORT FIELDS=COPY
OUTFIL INCLUDE=(21,1,CH,NE,C'$',AND,1,1,CH,NE,C'$'),
BUILD=(16,15)
/*

1. This card gives me only matching keys from F2
2. When I try to add the entire records from F1 and F2 in reformat (both are 133 byte VB) then the REFORMAT goes out of bounds.

3. How to add Non matching records from F1 and F2 in the same output ?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue May 21, 2013 7:05 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

That was a SyncSort manual, wasn't it? SyncSort questions live in the JCL forum.

If 16,1 (in your example) is $, you want to BUILD the data from F1. Otherwise, you want to build the data from F2 (which will be for F2 only or for a match, where you want the F2 record only).

Then you need to make a variable-length REFORMAT record, with F1 temporarily fixed-length and F2 variable, then when you BUILD the output records, use the RDW from the REFORMAT record, and the fixed F1 data, variable F2 data, and try VLTRIM for the OUTFIL dataset.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Tue May 21, 2013 10:21 pm    Post subject:
Reply with quote

The both input files F1 & F2 are VB 133 bytes.

When I mentioned the length of 133 byte in reformat, sort does not accept it and get error that its out of bounds.

Will it be possible to compare to VB 133 byte files on the key and copy matching records from F2 and non matching from both the files.

Help would be appreciated as I am not familiar either with ICETOOL or JONKEYS .

Thanks !
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 22, 2013 3:11 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

Code:
  REFORMAT FIELDS=(F1:1,4,5,129,F2,1,4,5)


OK, there's your variable-length REFORMAT statement.

You have to make one of the records "fixed" (since they are the same LRECL, I have arbitrarily chosen to do it with F1).

The REFORMAT record needs an RDW, either from F1 or F2. The correct length will appear there once the REFORMAT record is complete.

Now, I've guessed you have SyncSort, so continue to the "character which can't appear" approach. I'd suggest in bytes 5 for absence of 136 for absence of F2.

You'll need two OUTFILs, and one one you'll need VLTRIM to get rid of the trailing characters for unmatched records which have come from F1.

Code:
  OUTFIL FNAMES=MISMATCH,VLTRIM=C'$',
    INCLUDE=(5,1,CH,EQ,C'$',
               OR,136,1,CH,EQ,C'$'),
    IFTHEN=(WHEN=(5,1,CH,EQ,C'$',
             BUILD=(1,4,136)),
    IFTHEN=(WHEN=NONE,
             BUILD=(1,4,5,129))
  OUTFIL FNAMES=MATCH,SAVE


Something like that.

This is untested, so get to it.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Wed May 22, 2013 4:29 am    Post subject:
Reply with quote

Thanks for the reply !

I have tried REFORMAT this way but it throws the error 'WER230A REFORMAT FIELD OUTSIDE RANGE'
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 22, 2013 4:42 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

Show the full sysout from the step which failed. Psychic day this week is... er.. not sure, so it can't be today.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Wed May 22, 2013 5:22 am    Post subject:
Reply with quote

Code:
REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'                           
   SORT FIELDS=COPY                                                           
   OUTFIL VLTRIM=C'$',                                                         
          IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),                 
                       BUILD=(1,4,135)),                                       
          IFTHEN=(WHEN=NONE,BUILD=(1,4,5,129))                                 
                                                                               
                                                                               
WER276B  SYSDIAG= 1083168, 1636367, 1636367, 3515100                           
WER164B  102,444K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,           
WER164B     40K BYTES RESERVE REQUESTED, 1,004K BYTES USED                     
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED                               
WER230A  REFORMAT FIELD OUTSIDE RANGE
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                 
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE


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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 22, 2013 6:04 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

Please use the Code tags. Please also respond fully to requests. Post the full sysout from the step please, as you have missed some of the Control Cards.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Wed May 22, 2013 6:13 am    Post subject:
Reply with quote

Code:
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 6EDFD, MODEL 2096 P04             
SYSIN :                                                                   
   JOINKEYS FILE=F1,FIELDS=(5,15,A)                                       
   JOINKEYS FILE=F2,FIELDS=(5,15,A)                                       
   JOIN UNPAIRED,F1,F2                                                   
   REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'                     
   SORT FIELDS=COPY                                                       
   OUTFIL VLTRIM=C'$',                                                   
          IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),             
                       BUILD=(1,4,134)),                                 
          IFTHEN=(WHEN=NONE,BUILD=(1,4,5,129))                           
                                                                         
                                                                         
WER276B  SYSDIAG= 1083168, 1636367, 1636367, 3515100                     
WER164B  102,444K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER164B     40K BYTES RESERVE REQUESTED, 1,004K BYTES USED               
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED                           
WER230A  REFORMAT FIELD OUTSIDE RANGE                                     
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                             
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                 
WER482I  JNF1 STATISTICS                                                     
WER483B  6,900K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,           
WER483B     0 BYTES RESERVE REQUESTED, 1,000K BYTES USED                     
WER108I  SORTJNF1 : RECFM=VB   ; LRECL=    92; BLKSIZE= 27993                 
WER073I  SORTJNF1 : DSNAME=MNV.TESTS.QA.D36PSP01.S36PS01.TSTUNL.T0521G1       
WER482I  JNF2 STATISTICS                                                     
WER483B  6,900K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,           
WER483B     0 BYTES RESERVE REQUESTED, 1,000K BYTES USED                     
WER108I  SORTJNF2 : RECFM=VB   ; LRECL=    92; BLKSIZE= 27993                 
WER073I  SORTJNF2 : DSNAME=MNV.TESTS.QA.D36PSP01.S36PS01.TSTUNL.T0521G2       
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 22, 2013 6:32 am    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

There we go, much easier now we can see everything. You have LRECL of 92 for both input files, not the 133 you claimed.

Either use the correct files, or change the lengths of the data you use in the Control Cards.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Wed May 22, 2013 7:06 am    Post subject:
Reply with quote

I got the output after using correct length input files.

Code:
//SYSIN    DD  *                                                       
   JOINKEYS FILE=F1,FIELDS=(5,15,A)                                     
   JOINKEYS FILE=F2,FIELDS=(5,15,A)                                     
   JOIN UNPAIRED,F1,F2                                                 
   REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'                   
   SORT FIELDS=COPY                                                     
   OUTFIL VLTRIM=C' ',                                                 
          IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),           
                       BUILD=(1,4,138,129)),                           
          IFTHEN=(WHEN=(134,1,CH,EQ,C'$'),BUILD=(1,4,9,129))           
                                                                       
                                                                       


But the output contains only matching and non matching records from F2 and blank records in place of non matching records from F1.

What change is required so that non matching from F1 will be added to outout. with the added condition I am getting blank records instead

Output :

Code:
1111114|2002|1|.00|.00|0|2003-08-01-15.18.28.265416
1111114|2003|1|.00|.00|0|2003-08-01-15.18.28.265527
                                                   
1111114|2007|2|.00|.00|2|2007-07-14-15.46.59.513033
                                                   
1111114|2008|2|.00|.00|1|2008-03-29-17.49.24.833665
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 22, 2013 12:10 pm    Post subject: Reply to: Comparing Keys in Different Datasets and copy reco
Reply with quote

You are already getting non-matching F1 records on your output.

Code:
   OUTFIL VLTRIM=C'$',                                                 
          IFTHEN=(WHEN=(1,1,CH,EQ,C'$'),BUILD=(1,4,C'F2 ONLY|',138)),
          IFTHEN=(WHEN=(134,1,CH,EQ,C'$'),BUILD=(1,4,C'F1 ONLY|',9,129)),
          IFTHEN=(WHEN=NONE,
                       BUILD=(1,4,C'F1 AND 2',138))                         


Try the simplification above. Why were you trimming blanks when you FILLed with $s?
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 -> JCL & VSAM 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 syncsort: copy lines after the keyword shreya19 SYNCSORT 1 Fri Dec 02, 2016 9:47 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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