MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Match and merge records from 2 files using syncsort
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 5:31 am    Post subject: Match and merge records from 2 files using syncsort Reply with quote
Hi all,
I have requirement as below

File -1
--------
Name Group
ABC 1111
ABC 1111
DEF 2222

File-2
--------
Name Rate
ABC 0000
ABC 1234
DEF 5678

OUTPUT
---------
Group Rate
1111 1234
2222 5678

Output file should have Group (file-1) and Rate(file-2) in the output file.

We need to get those records in output file which have non zero rate corresponding to Group from Input-1.

Also we in the output file there should be only 1 entry for each group(i.e. Group should be not be repeated in output file).




If possible i want to do this using syncsort.

Please let me know if the req. is not clear.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 6:20 am    Post subject: Reply with quote
rajen,

Please search before posting. Search for the forum for JOINKEYS and you will find many examples or matching the records using syncsort

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 6:50 am    Post subject: Reply with quote
Hi Kolusu,
I know we can do this using JOINKEYS,FORMAT FIELDS but here i need to check for extra condition as well.
Here as i have mentioned the rate should not be zero and there should be only 1 record for 1 group.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 8:57 am    Post subject: Reply with quote
Rajen,

JOINKEYS with Include will give you the desired results. Unless you explore you aint gonna learn anything.

try this
Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*                           
//SORTJNF1 DD *                                 
ABC 1111                                         
ABC 1111                                         
DEF 2222                                         
//SORTJNF2 DD *                                 
ABC 0000                                         
ABC 1234                                         
DEF 5678                                         
//SORTOUT  DD SYSOUT=*                           
//SYSIN    DD *                                 
  SORT FIELDS=(1,5,CH,A)                         
                                                 
  JOINKEYS FILE=F1,FIELDS=(1,3,A)               
  JOINKEYS FILE=F2,FIELDS=(1,3,A),               
         OMIT=(05,4,CH,EQ,C'0000')               
                                                 
  REFORMAT FIELDS=(F1:5,5,                       
                   F2:5,5)                       
                                                 
  OUTREC FIELDS=(01,10,SEQNUM,8,ZD,RESTART=(1,5))
                                                 
  OUTFIL INCLUDE=(11,8,ZD,EQ,1),                 
  OUTREC=(1,10)                                 
                                                 
/*                     


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 10:14 am    Post subject: Reply with quote
Hi Kolusu,
Thanks for the timely response.
I tried to run your sort query and it abended with Syntax errors.
Code:

SYNCSORT FOR Z/OS  1.2.0.1R   
                                                      z/OS   1
PRODUCT LICENSED FOR CPU SERIAL NUMBER 6522F, MODEL 2064 106 
SYSIN :                                                       
  SORT FIELDS=(1,5,CH,A)                                     
  JOINKEYS FILE=F1,FIELDS=(1,3,A)                             
  JOINKEYS FILE=F2,FIELDS=(1,3,A),                           
         OMIT=(05,4,CH,EQ,C'0000')                           
    REFORMAT FIELDS=(F1:5,5,                                 
                     F2:5,5)                                 
    OUTREC FIELDS=(01,10,SEQNUM,8,ZD,RESTART=(1,5))           
                                     *                       
    OUTFIL INCLUDE=(11,8,ZD,EQ,1),                           
    OUTREC=(1,10)                                             
WER268A  OUTREC STATEMENT  : SYNTAX ERROR                     
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                 


Apart from this can you please guide me where would i get manual for JOINKEYS?
I couldnot understand RESTART.
Back to top
View user's profile Send private message
amargulies
Beginner


Joined: 10 Jan 2007
Posts: 123
Topics: 0
PostPosted: Tue Mar 13, 2007 10:38 am    Post subject: Reply with quote
Rajen,

RESTART for SEQNUM was introduced in SyncSort for z/OS 1.2.1. You appear to be back-leveled.
_________________
Alissa Margulies
SyncSort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 10:38 am    Post subject: Reply with quote
Quote:

Apart from this can you please guide me where would i get manual for JOINKEYS?
I couldnot understand RESTART.


Rajen,

I don't think your version of syncsort supports RESTART parameter.RESTART=(p,m) tells sort to start numbering the records from the start value which by default is 1. This will be helpful for numbering duplicate records.

As for documentation if your shop has a valid license then you request the manuals from www.syncsort.com

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 10:48 am    Post subject: Reply with quote
Thanks Kolusu/Amargulies,
Do you any other alternate method for this so that i can solve my problem using 1.2.0.1 version.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 10:53 am    Post subject: Reply with quote
rajen wrote:
Thanks Kolusu/Amargulies,
Do you any other alternate method for this so that i can solve my problem using 1.2.0.1 version.


try this

Code:

//STEP0100 EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*           
//SORTJNF1 DD *                   
ABC 1111                         
ABC 1111                         
DEF 2222                         
//SORTJNF2 DD *                   
ABC 0000                         
ABC 1234                         
DEF 5678                         
//SORTOUT  DD SYSOUT=*           
//SYSIN    DD *                   
  OPTION EQUALS                   
  SORT FIELDS=(01,5,CH,A)         
  SUM FIELDS=NONE                 
                                 
  JOINKEYS FILE=F1,FIELDS=(1,3,A)
  JOINKEYS FILE=F2,FIELDS=(1,3,A),
         OMIT=(05,4,CH,EQ,C'0000')
                                 
  REFORMAT FIELDS=(F1:5,5,       
                   F2:5,5)       
/*     


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 11:14 am    Post subject: Reply with quote
Hey Kolusu,
Thanks a lot for your reply. It worked for me.
I was tryin this field with different datatypes to under more about that and couldn't get it for packed fields.

So I was wondering Do we need to use P'0000' in place of C'0000'?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 11:19 am    Post subject: Reply with quote
rajen wrote:
Hey Kolusu,
Thanks a lot for your reply. It worked for me.
I was tryin this field with different datatypes to under more about that and couldn't get it for packed fields.

So I was wondering Do we need to use P'0000' in place of C'0000'?


For packed decimal fields you just need this

Code:

OMIT=(05,4,PD,EQ,0)


Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Tue Mar 13, 2007 11:26 am    Post subject: Reply with quote
Embarassed

Kolusu,
In my data I have NAME field in different position for both the input file.
In one input file (File-1) it is starting at position 13 and for second(File-2) it is starting at position 12.

I am really sorry for not making this clear. I noticed that just now while testing with actual data.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Mar 13, 2007 1:06 pm    Post subject: Reply with quote
rajen,

Now you are asking to spoon feed. You have a sample job which you need to tailor to according to your needs. Trust me you will learn a lot from this approach.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Wed Mar 14, 2007 8:14 am    Post subject: Reply with quote
Hi Kolusu,
Tried my hands on it but its not working.Steps I followed was --
1) sort file1 on 12th position -- SORT FIELDS=(12,4,CH,A)
2) sort file2 on 13th position -- SORT FIELDS=(13,4,CH,A)
3) Then i need to use SORTJNF1 and SORTJNF2 to REFORMAT the records.

I used below,

SORT FROM(IN1) USING CTL1 --> CTL1 point 12,4 sort card
SORT FROM(IN2) USING CTL2 --> CTL2 points 13,4 sort card

i am not getting where will i put SORTJNF1 and 2 sortcard.
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Wed Mar 14, 2007 9:32 am    Post subject: Reply with quote
Ok...
I tried below mentioned sort card (Taken and modified from earlier post).
Code:

//IN1      DD DSN=XUTC536.INPUT1.SORT,DISP=SHR 
//IN2      DD DSN=XUTC536.INPUT2.SORT,DISP=SHR           
//T1       DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(100,50),RLSE)       
//T2       DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(100,50),RLSE)       
//CON      DD DSN=&T1,DISP=(OLD,PASS),VOL=REF=*.T1                 
//         DD DSN=&T2,DISP=(OLD,PASS),VOL=REF=*.T2                 
//OUT      DD DSN=XUTC536.TTX.OTUPUT,DISP=(NEW,CATLG,DELETE),     
//            UNIT=SYSDA,SPACE=(CYL,(500,100),RLSE)               
//TOOLIN   DD *                                                   
   COPY FROM(IN1) USING(CTL1)                                     
   COPY FROM(IN2) USING(CTL2)                                     
   SORT FROM(CON) USING(CTL3)                                     
//CTL1CNTL DD *                                                   
   OUTFIL FNAMES=T1,OUTREC=(5,4,12,4,3Z)   --> Taking 5-8 and 12-15 fields                 
//CTL2CNTL DD *                                           --> and adding binary zeros. 
   OUTFIL FNAMES=T2,OUTREC=(8Z,44,3)        --> Making first 8 bytes zeros 12-15
//CTL3CNTL DD *                                           --> and taking packed decimal fields.
   OPTION EQUALS                                                   
   SORT FIELDS=(04,4,CH,A,09,3,BI,A),           --> sort on key and packed decimal
        OMIT=(44,3,PD,EQ,0)                            --> Omit recs with zeros (packed field)
   SUM FIELDS=NONE                                     --> Remove duplicates.


It cried in the 3rd control card and threw below mentioned messages.

            SORT FROM(CON) USING(CTL3)                                         
SYT020I  SYNCSORT CALLED WITH IDENTIFIER "0003"                                 
SYT012E  SYNCSORT COMPLETED UNSUCCESSFULLY                                     
SYT030I  OPERATION COMPLETED WITH RETURN CODE 16                               
                                                                               
SYT015I  PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE
                                                                               
SYT004I  SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 16                     

Please let me know how to do that.



//
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group