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
 

 

Using Joinkeys in Syncsort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Mon Jan 04, 2010 5:00 pm    Post subject: Using Joinkeys in Syncsort
Reply with quote

Hi,

Can anyone tell me how to do this using Joinkeys in Syncsort. there are two files F1 and F2. both files are compared with their keys, if the key matches then i have to inserted a mark, say 'D' at the end of the record else space in the file F1.

I tried joinkeys and outrec, but not able get the actual result
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Jan 04, 2010 5:07 pm    Post subject:
Reply with quote

Unless you post the code that you are using, along with an explanation of what is not happening, the release level of the product, the RECFM & LRECL of the two files, the position, length and format of the key fields, there isn't too much people can say to help you.
Back to top
View user's profile Send private message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Mon Jan 04, 2010 8:23 pm    Post subject:
Reply with quote

File F1 - LRECL = 100 and KEY length is 23, position 1
File F2 - LRECL = 100 and KEY length is 23, position 14

i need to compare the key in both files and if it matches then i need to insert a mark for instance 'D' in the file F1.

I tried joinkeys - syncsort

JOINKEYS FILE=F1,
FIELDS=(1,23,A)
JOINKEYS FILE=F2,
FIELDS=(14,23,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,100)
INREC FIELDS=(1:1,100,101:C'D')
SORT FIELDS=(1,4,CH,A)

i need to insert 'D' only in the matched record. this code inserting 'D' in both matched and unmatched record in File F1.
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad

PostPosted: Mon Jan 04, 2010 11:28 pm    Post subject: Reply to: Joinkeys - Syncsort
Reply with quote

Hi,
Remove the JOIN UNPAIRED,F1 and do the outrec.Here is the simple example.

Code:

//S1       EXEC  PGM=SORT           
//TOOLMSG  DD SYSOUT=*               
//DFSMSG   DD SYSOUT=*               
//SYSOUT   DD SYSOUT=*               
//SORTJNF1 DD *                     
123  A                               
234  B                               
//SORTJNF2 DD *                     
123  A                               
456  B                               
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                     
  JOINKEYS FILES=F1,FIELDS=(01,03,A)
  JOINKEYS FILES=F2,FIELDS=(01,03,A)
  REFORMAT FIELDS=(F1:1,6)           
  SORT FIELDS=(1,3,CH,A)             
  OUTREC FIELDS=(1,6,1x,C'D')           


Code:


output :

123  A D




Note : added a space(1X in outrec) after the first 6 characters and the character 'D'

Thanks
Krishy
Back to top
View user's profile Send private message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Tue Jan 05, 2010 8:33 am    Post subject: Reply to: Joinkeys - Syncsort
Reply with quote

Thanks Krishy,

By this code i will get only matched records with mark 'D' in the output.
But in my case, I want both matched and unmatched records, with mark 'D' only in matched records in the output file.


Thanks,
Deepan
Back to top
View user's profile Send private message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Tue Jan 05, 2010 10:55 am    Post subject:
Reply with quote

Krishy,

I want output as

123 A D
234 B
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 183
Location: hyderabad

PostPosted: Tue Jan 05, 2010 5:30 pm    Post subject: Reply to: Joinkeys - Syncsort
Reply with quote

Hi,
Try this.

Code:

//PS020    EXEC PGM=SYNCTOOL               
//TOOLMSG  DD SYSOUT=*                     
//DFSMSG   DD SYSOUT=*                     
//CTL1JNF1 DD *                           
123  A                                     
234  B                                     
//CTL1JNF2 DD *                           
123  A                                     
456  B                                     
//CTL1OF01 DD DSN=AA.T1,                   
//             DISP=(,CATLG,DELETE),       
//             UNIT=SYSID,                 
//             SPACE=(CYL,(1,1),RLSE)     
//FINAL    DD DSN=BB.T1,                   
//             DISP=(,CATLG,DELETE),       
//             UNIT=SYSID,                 
//             SPACE=(CYL,(1,1),RLSE)     
//TOOLIN   DD *                           
  SORT FROM(CTL1JNF1) USING(CTL1)         
  SORT FROM(CTL1OF01) TO(FINAL) USING(CTL2)
//CTL1CNTL DD *                           
 SORT FIELDS=COPY                         
 JOINKEYS FILES=F1,FIELDS=(1,3,A)         
 JOINKEYS FILES=F2,FIELDS=(1,3,A)         
 JOIN UNPAIRED,F1                         
 REFORMAT FIELDS=(F1:1,6,F2:1,6)           
 OUTFIL FILES=01                           
 //CTL2CNTL DD *                           
   OUTREC IFTHEN=(WHEN=(8,2,CH,NE,C'  '),   
          BUILD=(1,6,C'D')),               
          IFTHEN=(WHEN=NONE,               
                  BUILD=(1,6))             
 SORT FIELDS=COPY                         



Thanks
Krishy
Back to top
View user's profile Send private message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Tue Jan 05, 2010 8:37 pm    Post subject:
Reply with quote

Hi Krishy,

Yours working fine. Thanks a lot.
Since this sort the file two times, i tried this

JOINKEYS FILE=F1,
FIELDS=(1,4,A)
JOINKEYS FILE=F2,
FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,20,F2:1,20)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(21,4,CH,NE,C' '),
BUILD=(1:1,10,11:C'D',12:11,10)),
IFTHEN=(WHEN=NONE,
BUILD=(1:1,10,11:C' ',12:11,10))

This also doing the same work..
Thanks for ur idea..[/img]
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Fri Jan 08, 2010 11:44 pm    Post subject: Small Clarification on this
Reply with quote

Hi,

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

Based on the first condition we are going to get the output as

123 A
234 B


Can any one explain me how this condition works using the above o/p from first .

CTL2CNTL DD *
OUTREC IFTHEN=(WHEN=(8,2,CH,NE,C' '),
BUILD=(1,6,C'D')),
IFTHEN=(WHEN=NONE,
BUILD=(1,6))
SORT FIELDS=COPY

Pls help
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jan 09, 2010 1:40 am    Post subject:
Reply with quote

Hello,

What happens when you run it?
Back to top
View user's profile Send private message
Deepan Raj

New User


Joined: 30 Dec 2009
Posts: 9
Location: India

PostPosted: Mon Jan 11, 2010 10:44 am    Post subject:
Reply with quote

Hi Nethaji,

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

Based on the first condition u will get output as

123 A123 A
234 B

CTL2CNTL DD *
OUTREC IFTHEN=(WHEN=(8,2,CH,NE,C' '),
BUILD=(1,6,C'D')),
IFTHEN=(WHEN=NONE,
BUILD=(1,6))
SORT FIELDS=COPY

Based on second condition u will get output as

123 AD
234 B

watz ur output???
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am


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