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 

frm 2 files based on key match apnd clm values : SORT

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Tue Mar 07, 2006 4:33 pm    Post subject: frm 2 files based on key match apnd clm values : SORT Reply with quote
Hi All,

I am with two files.

If the key in fileA matches with the key in fileB, I need to append the Field-BX data to Field-AX data using Syncsort( as the shop over here supports only Syncsort).

i.e.

FileA
Code:

ID                    Name              Numb
1234                abc                  1
1235                xyz                  2
1236                uvw                  1
1238                opq                  5


FileB
Code:

ID                    Name              Numb
1234                abc                  4
1236                uvw                  4
1236                uvw                  5

output file: (FileC)
Code:

ID                   Name              Numb
1234                abc                 14
1235                xyz                 2   
1236                uvw                 145
1238                opq                 5


Can someone please suggest me how to get this result

Note: 1) Record layout of all the three files are same.
2) FileA do not have any duplicates in that, all the records are
unique with the Key i.e. ID in the above example

Please do let me know if I need to give any more details

Regards & Thanks,
Ram
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Wed Mar 08, 2006 3:57 am    Post subject: Reply with quote
Ram,

There are lots of posts already in this forum which addresses similar queries. I would have appreciated if you had spent sometime reading thro' these posts.

http://www.mvsforums.com/helpboards/viewtopic.php?t=5399

Thanks,
Phantom
Back to top
View user's profile Send private message
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Wed Mar 08, 2006 9:09 am    Post subject: Reply with quote
Phantom,

I did a search and looked in to the links, before posting. I could find the links while looking Nag's post "combine two files with different fields as 1 record" (feb 10 2006)

But, I am very low in Sort and could not convert these technics for Syncsort, as the shop over here supports only Syncsort.

If possible can you please help me out with Syncsort.

Regards & Thanks,
Ram
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
PostPosted: Wed Mar 08, 2006 10:39 am    Post subject: Reply with quote
Ram22,

Answer ALL of the questions

1. What is the max no: of dups for each key in file B?
2. what is the LRECL and RECFM of both input and output files
3. What is the pos and format of the fields which needs to merged and compared?
4. What version of syncsort do you have. run a sample job and look at the first line in your sysout.

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


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Thu Mar 09, 2006 5:18 pm    Post subject: Reply with quote
Kolusu,

Please find the answers below:

1. What is the max no: of dups for each key in file B?
5

2. what is the LRECL and RECFM of both input and output files
LRECL : 80
RECFM: FB
(same for both)

3. What is the pos and format of the fields which needs to merged and compared?
Pos: 18 and length is 5 & Format : Alphanumeric ( X(05) )

4. What version of syncsort do you have. run a sample job and look at the first line in your sysout.
SYNCSORT FOR Z/OS 1.2.0

Regards & Thanks,
Ram
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
PostPosted: Fri Mar 10, 2006 9:59 am    Post subject: Reply with quote
The following job will give you the desired results. A brief explanation of the job.

In step0100 we take input file with dups and create a seqnum for every record and then based on the seqnum we move the corresponding value as an array of bytes. Since you said you can have a max of 5 dups for each key we need to arrange them as an array of 5 bytes.

ex:
Code:

1236     UVW     1 
1236     UVW     2                                                   
1236     UVW     3                                                   
1236     UVW     4                                                   
1236     UVW     5                                                   


the output from step0100 looks like this

Code:

1236     UVW     12345


We now use this file and using join feature we club all the records with your unique file 1.

Code:

//STEP0100 EXEC PGM=SYNCTOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN       DD *                                                     
1234     ABC     2                                                   
1236     UVW     1                                                   
1236     UVW     2                                                   
1236     UVW     3                                                   
1236     UVW     4                                                   
1236     UVW     5                                                   
//T1       DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//T2       DD DSN=&T2,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//T3       DD DSN=&T3,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//OUT      DD DSN=&OUT,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//CON      DD DSN=&T1,DISP=OLD,VOL=REF=*.T1                         
//         DD DSN=&T2,DISP=OLD,VOL=REF=*.T2                         
//TOOLIN   DD *                                                     
  SORT FROM(IN) USING(CTL1)                                         
  SPLICE FROM(CON) TO(T3) ON(1,4,CH) -                               
    WITH(1,88) WITHALL USING(CTL2)                                   
  SORT FROM(T3) USING(CTL3)
/*
//CTL1CNTL DD *                                                     
  SORT FIELDS=(1,4,CH,A)                                             
  OUTREC FIELDS=(1,17,18,1,18,1,18,1,18,1,18,1,80:X,SEQNUM,8,ZD,8X) 
  OUTFIL FNAMES=T1,NODETAIL,REMOVECC,                               
  SECTIONS=(1,4,TRAILER3=(1,88,MIN=(81,8,ZD,M11,LENGTH=8)))         
  OUTFIL FNAMES=T2
/*
//CTL2CNTL DD *                                         
  OUTFIL FNAMES=T3,                                     
  OUTREC=(1,80,                                         
          ((81,8,ZD,SUB,89,8,ZD),ADD,+1),EDIT=(TTTTTTTT))
/*
//CTL3CNTL DD *                                         
 SORT FIELDS=(1,4,CH,A)                                 
 INREC FIELDS=(1,17,                                     
               18:81,8,CHANGE=(1,C'00000002',X'00',     
                                 C'00000003',X'00',     
                                 C'00000004',X'00',     
                                 C'00000005',X'00'),     
                       NOMATCH=(18,1),                   
               19:81,8,CHANGE=(1,C'00000001',X'00',     
                                 C'00000003',X'00',     
                                 C'00000004',X'00',     
                                 C'00000005',X'00'),     
                       NOMATCH=(19,1),                   
               20:81,8,CHANGE=(1,C'00000001',X'00',     
                                 C'00000002',X'00',     
                                 C'00000004',X'00',     
                                 C'00000005',X'00'),     
                       NOMATCH=(20,1),                   
               21:81,8,CHANGE=(1,C'00000001',X'00',     
                                 C'00000002',X'00',     
                                 C'00000003',X'00',     
                                 C'00000005',X'00'),     
                       NOMATCH=(21,1),                   
               22:81,8,CHANGE=(1,C'00000001',X'00',     
                                 C'00000002',X'00',     
                                 C'00000003',X'00',     
                                 C'00000004',X'00'),     
                       NOMATCH=(22,1),X'00')             
 SUM FIELDS=(18,4,BI,22,2,BI)                           
 OUTFIL FNAMES=OUT                                       
/*
//STEP0200 EXEC PGM=SORT               
//SYSOUT   DD SYSOUT=*                 
//SORTJNF1 DD *                         
1234     ABC     1                     
1235     XYZ     2                     
1236     UVW     0                     
1238     OPQ     5                     
//SORTJNF2 DD DSN=&OUT,DISP=OLD         
//SORTOUT  DD SYSOUT=*                 
//SYSIN    DD *                         
  JOINKEYS FILES=F1,FIELDS=(01,4,A)     
  JOINKEYS FILES=F2,FIELDS=(01,4,A)     
  REFORMAT FIELDS=(F1:1,18,             
                   F2:18,5)             
  SORT FIELDS=(1,4,CH,A)               
  JOIN UNPAIRED                         
/*                                     


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
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Fri Mar 10, 2006 11:19 am    Post subject: Reply with quote
Thanks in tons Kolusu. I shall try this and come back with results. Thanks again.

Regards & Thanks,
Ram
Back to top
View user's profile Send private message
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Mon Mar 13, 2006 9:22 pm    Post subject: Reply with quote
Kolusu,

Its really a wonderful sort. But I am a basic user of Sort. I tried to understand first step of handling the file with duplicates, but believe me it is very complex for me to understand.

Can you please explain me in details. Please note that these details will make to enjoy the beauty of this Sort you drafted.

Regards & Thanks,
Ram
Back to top
View user's profile Send private message
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Mon Mar 13, 2006 11:08 pm    Post subject: Reply with quote
Kolusu,

I just want to add one more clarification in answering one of your questions

3. What is the pos and format of the fields which needs to merged and compared?
Pos: 18 and length is 5 & Format : Alphanumeric ( X(05) )

To be more clear , In my input files:
Fields need to be merged start in postion 18 and length is 5.
Fields need to be compared i.e. key position is 1,17,ch

I think in the example you gave
OUTREC FIELDS=(1,17,18,1,18,1,18,1,18,1,18,1,80:X,SEQNUM,8,ZD,8X)
1,17 means the key and I could not understand the remaing part 18,1....

Kolusu I am getting lost in most areas.

Ram
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
PostPosted: Tue Mar 14, 2006 11:28 am    Post subject: Reply with quote
Ram22,

The first step to reformat the duplicates as a single record which can then be used to join with file1 record.

The biggest challenge is to arrange the duplicates as a single record. For doing that we need to number the duplicates.

ex:
Code:

1236     UVW     a 
1236     UVW     b
1236     UVW     c
1236     UVW     d
1236     UVW     e


i.e
Code:

1236     UVW     a   - dup 1
1236     UVW     b   - dup 2
1236     UVW     c   - dup 3 
1236     UVW     d   - dup 4
1236     UVW     e   - dup 5 


The first sort pass in step0100 takes the input file and reformats like shown below.

Code:

1236     UVW     aaaaa
1236     UVW     bbbbb
1236     UVW     ccccc
1236     UVW     ddddd
1236     UVW     eeeee


The reason for padding the same character 5 times is that we need to create a single record


Code:

1236     UVW     abcde


The generation of seqnum whenever a key changes is explained in detail here

http://www.mvsforums.com/helpboards/viewtopic.php?p=12759#12759

so after the splice your output will look like

Code:

----+----1----+----2----+----8---------
1236     UVW     aaaaa        00000001
1236     UVW     bbbbb        00000002 
1236     UVW     ccccc        00000003 
1236     UVW     ddddd        00000004
1236     UVW     eeeee        00000005


The 3rd pass in step0100 now uses the inrec fields and puts binary zeros based on the dup seqnum.

Code:

----+----1----+----2----+----8---------
1236     UVW     a....        00000001 
1236     UVW     .b...        00000002 
1236     UVW     ..c..        00000003 
1236     UVW     ...d.        00000004 
1236     UVW     ....e        00000005 


Now sorting on the key (1,4,ch,a) we sum up the fields at 18 for 6 bytes so that we can get a single record as follows.


Code:

----+----1----+----2----+----8---------
1236     UVW     abcde        00000001


Once we have this type of record then we can use to join with your unique file1.

There is small PDF which comes along with syncsort titled " exploiting Syncsort Join" which in detail explains the join process with examples. read it.

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
Ram22
Beginner


Joined: 09 Jun 2004
Posts: 33
Topics: 6
PostPosted: Tue Mar 14, 2006 8:06 pm    Post subject: Reply with quote
Kolusu,

This explanation helped me a lot. Thanks a lot again.

Regards & Thanks,
Ram
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
Page 1 of 1

 
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