IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

JOINs in SYNCSORT
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
pratiksha
Warnings : 1
New User


Joined: 10 Feb 2006
Posts: 10
Location: Bangalore
PostPosted: Tue May 27, 2008 5:30 pm
 Reply with quote
Hi,

I need help in joining 2 files, Can anyone help me out...


I have 2 files,

1 file is as follows..lrec=96

10000000000000000000000000000000000000000000000000000000000000000000000000000000
20000000000000000000000000000000000000000000000000000000000000000000000000000000
30033333333333333333333300330000000000000000000000000000000000000000000000000000
40044444444444444444444444440000000000000000000000000000000000000000000000000000
60000000000000000000000000000000000000000000000000000000000000000000000000000000

2nd file is as below, lrec=96

10000000000000000000KK0000000000000000000000000000000000000000000000000000000000
20000000000000000000KK0000000000000000000000000000000000000000000000000000000000
30000000000000000000KK0000000000000000000000000000000000000000000000000000000000
40000000000000000000KK0000000000000000000000000000000000000000000000000000000000

The first three field are key fildes...

I want the output file such that

1 to 3rd position i want the key.From 4th to 96th position i want the data which is present in the file 2 where ever the keys match. If there is no match then 1 to 96 position should be same as in file 1.
Can u tell me if it is possible using sort...

tried out this

//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A)
JOIN UNPAIRED,F1,F1
REFORMAT FIELDS=(F1:1,3,F2:4,96)
SORT FIELDS=COPY


But the keys which dont match they get only first three positions. rest is blank. I want that to be same as in File 1.

Thanks,
Pratiksha
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad
PostPosted: Tue May 27, 2008 6:13 pm
 Reply with quote
Pratiksha,
If your second file is always going to have 'KK' ,then the following will work for you.Else,we need to go for some other solution.

Code:


//PS020    EXEC PGM=SYNCTOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//CTL1JNF1 DD *                                                         
10000000000000000000000000000000000000000000000000000000000000000000000A
20000000000000000000000000000000000000000000000000000000000000000000000B
30033333333333333333333300330000000000000000000000000000000000000000000C
40044444444444444444444444440000000000000000000000000000000000000000000D
60000000000000000000000000000000000000000000000000000000000000000000000E
//CTL1JNF2 DD *                                                         
10000000000000000000KK00000000000000000000000000000000000000000000000000
20000000000000000000KK00000000000000000000000000000000000000000000000000
30000000000000000000KK00000000000000000000000000000000000000000000000000
40000000000000000000KK00000000000000000000000000000000000000000000000000
//CTL1OF01 DD DSN=xxx.xxxx.xxx.S.T2,                                 
//            DISP=(,CATLG,DELETE),                                     
//            UNIT=Sysdk,                                               
//            SPACE=(CYL,(55,55),RLSE)                                 
//TOOLIN    DD *                                                       
  SORT FROM(CTL1JNF1) USING(CTL1)                                       
//CTL1CNTL  DD *                                                       
  JOINKEYS FILES=F1,FIELDS=(1,3,A)                                     
  JOINKEYS FILES=F2,FIELDS=(1,3,A)                                     
  JOIN UNPAIRED,F1                                                     
  REFORMAT FIELDS=(F1:1,20,F2:21,2,F1:23,55)                           
  SORT FIELDS=COPY                                                     
  OUTFIL FILES=01,OUTREC=1,20,C'KK',23,55)                                                         


Note : You need to change the position(s) as per your file structure.

Thanks
Krishy
Back to top
View user's profile Send private message
Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai
PostPosted: Tue May 27, 2008 6:17 pm
 Reply with quote
Try this. I tested this with lrecl as 80 just change the lrecl according to your requirements.

Code:

OPTION COPY                                         
JOINKEYS FILES=F1,FIELDS=(1,3,A)                     
JOINKEYS FILES=F2,FIELDS=(1,3,A)                     
JOIN UNPAIRED,F1                                     
REFORMAT FIELDS=(F1:1,80,F2:1,80)                   
OUTFIL FNAMES=MYOUT                                 
OUTREC IFTHEN=(WHEN=(1,3,CH,EQ,81,3,CH),             
                  BUILD=(1,3,84,76)),               
       IFTHEN=(WHEN=(1,3,CH,NE,81,3,CH),             
                  BUILD=(1,80))       
               
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad
PostPosted: Tue May 27, 2008 6:21 pm
 Reply with quote
Sorry,I mis understood your requirement.The above won't work as per your requirement.

Thanks
Krishy
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Tue May 27, 2008 11:13 pm
 Reply with quote
Hello Krishy,

Would you prefer your 2 posts (and your reply to this) be removed ?

I can do this at no extra charge icon_smile.gif

d
Back to top
View user's profile Send private message
pratiksha
Warnings : 1
New User


Joined: 10 Feb 2006
Posts: 10
Location: Bangalore
PostPosted: Wed May 28, 2008 3:06 pm
 Reply with quote
Hi,


The solution provided worked for me Manuneedhi. Thanku so much. Can we join files having more than 1 keys also in the files???

Thanks & Regads,
Pratiksha
Back to top
View user's profile Send private message
Manuneedhi K

Active User


Joined: 07 May 2008
Posts: 115
Location: Chennai
PostPosted: Wed May 28, 2008 3:10 pm
 Reply with quote
You are welcome.

AFAIK You can join more than one keys all you need is to change the FIELDS in the JOINKEYS accordingly.

Thanks
Manu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
No new posts DFSORT/SYNCSORT/ICETOOL JCL & VSAM 8
No new posts Syncsort "Y2C" Function SYNCSORT 1
No new posts Arithmetic division using Syncsort SYNCSORT 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us