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
 

 

How to Merge 2 columns from two diff DSN & put into 1 DS

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

New User


Joined: 21 Feb 2012
Posts: 8
Location: india

PostPosted: Wed Feb 22, 2012 6:13 pm    Post subject: How to Merge 2 columns from two diff DSN & put into 1 DS
Reply with quote

File 1:


20120219
20120219
20120219
20120219
20120219

File 2:

12
13
15
18
19


Outfile should be:

20120219 12
20120219 13
20120219 15
20120219 18
20120219 19
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Feb 22, 2012 6:36 pm    Post subject:
Reply with quote

go to ftp://ftp.software.ibm.com/storage/dfsort/mvs/sorttrck.pdf

and look at Join fields from two files record-by-record
Back to top
View user's profile Send private message
elango_K

New User


Joined: 18 Aug 2011
Posts: 44
Location: India

PostPosted: Wed Feb 22, 2012 8:42 pm    Post subject:
Reply with quote

On what basis is the mapping done.

Will the rec count always match in both files???
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Feb 22, 2012 8:46 pm    Post subject: Reply to: How to Merge 2 columns from two diff DSN & put
Reply with quote

Quote:
On what basis is the mapping done.

like many other topics posted recently the join is on the <sequence> number
at least that' s what it is reasonable to assume when the TS wisely icon_wink.gif forgets to tell about a common key
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Feb 22, 2012 11:51 pm    Post subject:
Reply with quote

Shanth,

You can use a DFSORT job like the following to do what you asked for:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
20120219
20120219
20120219
20120219
20120219
/*
//IN2 DD *
12
13
15
18
19
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(9,8,A),SORTED,NOSEQCK
  JOINKEYS F2=IN2,FIELDS=(3,8,A),SORTED,NOSEQCK
  REFORMAT FIELDS=(F1:1,8,F2:1,2)
  OPTION COPY
  OUTREC BUILD=(1,8,15:9,2)
/*
//JNF1CNTL DD *
  INREC OVERLAY=(9:SEQNUM,8,BI)
/*
//JNF2CNTL DD *
  INREC OVERLAY=(3:SEQNUM,8,BI)
/*
Back to top
View user's profile Send private message
iniyavan555
Currently Banned

New User


Joined: 21 Feb 2012
Posts: 8
Location: india

PostPosted: Thu Feb 23, 2012 1:12 pm    Post subject:
Reply with quote

Code:


  JOINKEYS F1=IN1,FIELDS=(9,8,A),SORTED,NOSEQCK 
           *                                     
  JOINKEYS F2=IN2,FIELDS=(3,8,A),SORTED,NOSEQCK 
           *                                     
   REFORMAT FIELDS=(F1:1,8,F2:1,2)               
    OPTION COPY                                 
    OUTREC BUILD=(1,8,15:9,2)                   
WER268A  JOINKEYS STATEMENT: SYNTAX ERROR       
WER268A  JOINKEYS STATEMENT: SYNTAX ERROR       

i am getting the following Error for the above code which frank send
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Feb 23, 2012 1:33 pm    Post subject: Reply to: How to Merge 2 columns from two diff DSN & put
Reply with quote

WER messages imply that You are using SYNCSORT

topic moved where it belongs
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Thu Feb 23, 2012 1:51 pm    Post subject: Reply to: How to Merge 2 columns from two diff DSN & put
Reply with quote

Hello iniyavan555,

What Version/Release of Syncsort you are Using??

Thanks
-3nadh
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Feb 23, 2012 3:14 pm    Post subject:
Reply with quote

Hi,

try this
Code:
//S1       EXEC PGM=SYNCTOOL                                     
//IN1      DD *                                                 
20120219                                                         
20120219                                                         
20120219                                                         
20120219                                                         
20120219                                                         
//IN2      DD *                                                 
12                                                               
13                                                               
15                                                               
18                                                               
19                                                               
//IN1O     DD DSN=&&IN1O,DISP=(,PASS,DELETE),UNIT=SYSDA,         
//            SPACE=(TRK,(10,5),RLSE)                           
//IN2O     DD DSN=&&IN2O,DISP=(,PASS,DELETE),UNIT=SYSDA,         
//            SPACE=(TRK,(10,5),RLSE)                           
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//TOOLIN   DD *                                       
  COPY FROM(IN1) TO(IN1O) USING(CTL1)                 
  COPY FROM(IN2) TO(IN2O) USING(CTL1)                 
/*                                                     
//CTL1CNTL DD *                                       
   INREC OVERLAY=(73:SEQNUM,8,ZD)                     
/*                                                     
//S2       EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//SORTJNF1 DD DSN=&&IN1O,DISP=(OLD,DELETE)             
//SORTJNF2 DD DSN=&&IN2O,DISP=(OLD,DELETE)             
//SORTOUT DD SYSOUT=*                                 
//SYSIN DD *                                           
  JOINKEYS FILE=F1,FIELDS=(73,8,A),SORTED             
  JOINKEYS FILE=F2,FIELDS=(73,8,A),SORTED             
  JOIN UNPAIRED                                       
  REFORMAT FIELDS=(F1:1,8,F2:1,2)                     
  OPTION COPY                                         
  OUTREC BUILD=(1,8,15:9,2)                           
/*



Gerry
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts SQL Merge error -254 Robin Sulsona DB2 8 Wed Jun 01, 2016 8:28 pm
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am
No new posts SAS MERGE to DFSORT Musab Ahmed DFSORT/ICETOOL 6 Mon Feb 01, 2016 5:44 pm


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