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

Joining records of two files using SORT
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Tue Apr 17, 2012 7:25 pm
 Reply with quote
Hi,

I am using below SORT step to join records from 2 files.


Code:
                           
 //SORT10   EXEC PGM=SORT                                     
 //SORTJNF1 DD DSN=TCS.TEST.SORT.VISHAL.LRECL783,DISP=SHR     
 //SORTJNF2 DD DSN=TCS.TEST.SORT.VISHAL1.LRECL783,DISP=SHR   
 //SORTOUT  DD DSN=TCS.TEST.SORT.VISHAL3.LRECL783,DISP=SHR   
 //SYSOUT   DD   SYSOUT=*                                                             
 //SYSIN    DD *                                             
   JOINKEYS FILES=F1,FIELDS=(1,5,A),SORTED                   
   JOINKEYS FILES=F2,FIELDS=(1,5,A),SORTED                   
   REFORMAT FIELDS=(F1:1,36,F2:66,14,F1:49,733)               
   OPTION COPY                                               
 /*                                                           


Currently it is joining records where 1st five characters from both the file are matching.... and only those matched records are getting reformatted in the output file

I want to change the control card such that even if the record from File 1 doesn't match to File 2 it should write File 1 record as it is in the output file.
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Tue Apr 17, 2012 7:33 pm
 Reply with quote
I have added below card and it worked.

Code:

JOIN UNPAIRED,F1



Now, i don't wan to sort these two files or neither want to match them on any keys just want to merge the fileds record by record ...

File 1 is going to have more records for sure so once file 2 reaches at end all the rest of the records from File 1 should be copied as it is in the output file.

keeping the length of the records i.e. 783 in mind could someone please, suggest what control card should I use?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Apr 17, 2012 7:49 pm
 Reply with quote
If you make yourself a couple of JNFnCNTL files, and append sequence numbers to the files in each, then use those sequence numbers for the key, you should get what you want.
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Tue Apr 17, 2012 7:55 pm
 Reply with quote
Hi Bill,

I tried the same :

Code:


//JNF1CNTL DD *                                     
  INREC OVERLAY=(780:SEQNUM,3,ZD)                   
/*                                                   
//JNF2CNTL DD *                                     
  INREC OVERLAY=(780:SEQNUM,3,ZD)                   
/*                                                   
//SYSIN    DD *                                     
  JOINKEYS FILES=F1,FIELDS=(780,3,A),SORTED         
  JOINKEYS FILES=F2,FIELDS=(780,3,A),SORTED         
  REFORMAT FIELDS=(F1:1,36,F2:66,14,F1:49,733)       
  JOIN UNPAIRED,F1                                   
  OPTION COPY                                       
/*                                                   


File 1

Code:

00001|CPP000562712  |0800000000   |+             |0100           |HAYLEY
00002|CPP000562772  |0800000004   |+             |0055           |PAULIN
00003|CPP000562712  |0800000018   |+             |0041           |HILLAR
00005|CPP000562712  |0800000017   |+             |0041           |MATHEW
00004|CPP000562712  |0800000019   |+             |0041           |HALE 
00002|CPP000562752  |0800000020   |+             |0041           |ADRIAN


File 2

Code:

00001|CPP000562712  |0800000000                                   HAYLEY



I got output as

Code:

00001|CPP000562712  |0800000000   |+ HAYLEY EMMA   |0100           |HAYL
00002|CPP000562772  |0800000004   |+ HAYLEY EMMA   |0055           |PAUL
00003|CPP000562712  |0800000018   |+ HAYLEY EMMA   |0041           |HILL
00005|CPP000562712  |0800000017   |+ HAYLEY EMMA   |0041           |MATH
00004|CPP000562712  |0800000019   |+ HAYLEY EMMA   |0041           |HALE
00002|CPP000562752  |0800000020   |+ HAYLEY EMMA   |0041           |ADRI


As you can see the name (i.e. F2:66,14) is copied in all the File 1 record not just 1st one ,

I want to write control card such that it should just leave File 1 record as it is if file 2 records are complete.
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Tue Apr 17, 2012 7:58 pm
 Reply with quote
Basically the desired output in this case where file 2 just have 1 record should look like below:

Code:

0001|CPP000562712  |0800000000   |+ HAYLEY EMMA   |0100           |HAYL
0002|CPP000562772  |0800000004   |+               |0055           |PAUL
0003|CPP000562712  |0800000018   |+               |0041           |HILL
0005|CPP000562712  |0800000017   |+               |0041           |MATH
0004|CPP000562712  |0800000019   |+               |0041           |HALE
0002|CPP000562752  |0800000020   |+               |0041           |ADRI
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Apr 17, 2012 8:15 pm
 Reply with quote
OK, this would work with DFSORT but you are using Syncsort.

Your JNFnCNTLs are being ignored, and the join is done just on whaterver is lying around at 780, probably has the same value, so replicates the same data as with a "multiple" match.

Put the sequences numbers on in prior steps, or talk to Syncsort.
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Tue Apr 17, 2012 8:36 pm
 Reply with quote
Hi Bill,

Could you please, suggest any other option to do this using SYNCSORT?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Apr 17, 2012 9:06 pm
 Reply with quote
I've already suggested the other option.

Read file 1, add a sequence, create new file 1+seq.

Read file 2, add a sequence, create new file 2+seq.

Join with the two new files.

Three steps, but there you go.

I don't have Syncsort documentation or access to the product.

You could write a program in Cobol, as I see you know it, if you don't like three steps.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
PostPosted: Tue Apr 17, 2012 10:10 pm
 Reply with quote
Bill, Bill, Bill, why can't you provide the code for same?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Apr 17, 2012 10:26 pm
 Reply with quote
:-)

New step 1

Code:
//SYSIN DD *
  SORT FIELDS=COPY                                     
  INREC OVERLAY=(780:SEQNUM,3,ZD)                   


New step 2
Code:
//SYSIN DD *
  SORT FIELDS=COPY                                     
  INREC OVERLAY=(780:SEQNUM,3,ZD)


To be fair, TS/OP already came up with the code themselves, so just for fun :-)

This is untested.

You want it with SYMNAMES, Dave? :-)
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
PostPosted: Tue Apr 17, 2012 10:28 pm
 Reply with quote
Quote:
You want it with SYMNAMES, Dave? :-)


Good one, too complicated for me. icon_redface.gif
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Wed Apr 18, 2012 1:00 pm
 Reply with quote
Hi,

I have tried 3 step JCL with below inputs :

File 1 :

Code:

00001|CPP000562712  |0800000000   |+ AAAAAAAAAAAA|0100           |HAYLEY
00002|CPP000562772  |0800000004   |+ CCCCCCCCCCCC|0055           |PAULIN
00003|CPP000562712  |0800000018   |+ AAAAAAAAAAAA|0041           |HILLAR
00005|CPP000562712  |0800000017   |+ CCCCCCCCCCCC|0041           |MATHEW
00004|CPP000562712  |0800000019   |+ AAAAAAAAAAAA|0041           |HALE 
00002|CPP000562752  |0800000020   |+ CCCCCCCCCCCC|0041           |ADRIAN



File 2

Code:

00001|CPP000562712  |0800000000                                   HAYLEY


I want output

Code:

 00001|CPP000562712  |0800000000   |+ HAYLEY EMMA   |0100           |HAYL
 00002|CPP000562772  |0800000004   |+ CCCCCCCCCCCCCC|0055           |PAUL
 00003|CPP000562712  |0800000018   |+ AAAAAAAAAAAAAA|0041           |HILL
 00005|CPP000562712  |0800000017   |+ CCCCCCCCCCCCCC|0041           |MATH
 00004|CPP000562712  |0800000019   |+ AAAAAAAAAAAAAA|0041           |HALE
 00002|CPP000562752  |0800000020   |+ CCCCCCCCCCCCCC|0041           |ADRI


as first record having data from File 1 and File 2 but rest should be AS IT IS from File1

I have tried 3 steps jCL as suggested :

but the Out put I got is

Code:


00001|CPP000562712  |0800000000   |+ HAYLEY EMMA   |0100           |HAYL
00002|CPP000562772  |0800000004   |+               |0055           |PAUL
00003|CPP000562712  |0800000018   |+               |0041           |HILL
00005|CPP000562712  |0800000017   |+               |0041           |MATH
00004|CPP000562712  |0800000019   |+               |0041           |HALE
00002|CPP000562752  |0800000020   |+               |0041           |ADRI



I have used below JCL with all 3 steps :

Code:

//TCSVBSTS JOB 0000,'SEQNM',CLASS=7,MSGCLASS=U,               
//    NOTIFY=&SYSUID,MSGLEVEL=(1,1)                           
//* $ACFJ219 ACF2 ACTIVE I003                                 
//SORT10   EXEC PGM=SORT                                     
//SORTIN   DD DSN=TCS.TEST.SORT.VISHAL.LRECL783,DISP=SHR     
//SORTOUT  DD DSN=&&TEMP1,                                   
//            DISP=(NEW,PASS,DELETE),SPACE=(TRK,(5,5))       
//SYSOUT   DD   SYSOUT=*                                     
//SYSOUZ   DD   SYSOUT=*                                     
//SORTWK01 DD   SPACE=(TRK,(1,1))                             
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                           
  INREC FIELDS=(1,783,SEQNUM,3,ZD,START=1)                   
/*                                                           
//SORT20   EXEC PGM=SORT                                     
//SORTIN   DD DSN=TCS.TEST.SORT.VISHAL1.LRECL783,DISP=SHR     
//SORTOUT  DD DSN=&&TEMP2,                                   
//            DISP=(NEW,PASS,DELETE),SPACE=(TRK,(5,5))       
//SYSOUT   DD   SYSOUT=*                                 
//SYSOUZ   DD   SYSOUT=*                                 
//SORTWK01 DD   SPACE=(TRK,(1,1))                       
/*                                                       
//SYSIN    DD *                                         
  SORT FIELDS=COPY                                       
  INREC FIELDS=(1,783,SEQNUM,3,ZD,START=1)               
/*                                                       
//SORT30   EXEC PGM=SORT                                 
//SORTJNF1 DD DSN=&&TEMP1,DISP=OLD                       
//SORTJNF2 DD DSN=&&TEMP2,DISP=OLD                       
//SORTOUT  DD DSN=TCS.TEST.SORT.VISHAL3.LRECL783,DISP=SHR
//*           DISP=(NEW,CATLG,DELETE),SPACE=(TRK,(5,5)) 
//SYSOUT   DD   SYSOUT=*                                 
//SYSOUZ   DD   SYSOUT=*                                 
//SORTWK01 DD   SPACE=(TRK,(1,1))                       
//SYSIN    DD *                                         
  JOINKEYS FILES=F1,FIELDS=(784,3,A),SORTED             
  JOINKEYS FILES=F2,FIELDS=(784,3,A),SORTED       
  REFORMAT FIELDS=(F1:1,36,F2:66,15,F1:50,732)   
  JOIN UNPAIRED,F1                               
  OPTION COPY                                     
/*                                               


Is their a way I can tell Syncsort not to touch File 1 records and copy them as it is if there are not matching with File 2 or File2 has reached at end ?

At the moment as you can see I am getting Spaces overwritten in my file 1 data in the output if they are not matching.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Wed Apr 18, 2012 1:15 pm
 Reply with quote
DFSORT has an indicator telling you about the current JOINKEYS result.

Don't think it exists in Syncsort.

I don't have documentation, but look in yours for any examples, or search here with JOINKEYS and FILL, I think.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Wed Apr 18, 2012 2:01 pm
 Reply with quote
Hi,

I would change your REFORMAT line to
Code:
REFORMAT FIELDS=(F1:1,783,F2:1,783)


Then I would use the
Code:
OUTREC IFTHEN=(WHEN
to format your output

Gerry
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)
PostPosted: Wed Apr 18, 2012 2:15 pm
 Reply with quote
Hi vishalbshah,

Try this snippet. Change the fields according to your requirment.

Code:

//STEP1A    EXEC PGM=SORT                                               
//SORTJNF1  DD *                                                       
00001|CPP000562712  |0800000000   |+ AAAAAAAAAAAA|0100           |HAYLEY
00002|CPP000562772  |0800000004   |+ CCCCCCCCCCCC|0055           |PAULIN
00003|CPP000562712  |0800000018   |+ AAAAAAAAAAAA|0041           |HILLAR
00005|CPP000562712  |0800000017   |+ CCCCCCCCCCCC|0041           |MATHEW
00004|CPP000562712  |0800000019   |+ AAAAAAAAAAAA|0041           |HALE 
00002|CPP000562752  |0800000020   |+ CCCCCCCCCCCC|0041           |ADRIAN
//SORTJNF2  DD *                                                       
00001|CPP000562712  |0800000000                                   HAYLEY EMMA
//SORTOUT   DD SYSOUT=*                                                 
//SYSOUT    DD SYSOUT=*                                                 
//SYSPRINT  DD SYSOUT=*                                                 
//SYSIN     DD *                                                       
  JOINKEYS FILE=F1,FIELDS=(1,05,A)                                     
  JOINKEYS FILE=F2,FIELDS=(1,05,A)                             
  REFORMAT FIELDS=(F1:1,80,F2:1,80)                           
  JOIN UNPAIRED,F1                                             
  OPTION COPY                                                 
  INREC IFTHEN=(WHEN=(1,1,CH,NE,C' ',AND,81,1,CH,NE,C' '),     
              BUILD=(1,37,38:147,12,50,29))               
             


And Your Output:-

Code:

00001|CPP000562712  |0800000000   |+ HAYLEY EMMA |0100           |HAYLEY       
00002|CPP000562772  |0800000004   |+ CCCCCCCCCCCC|0055           |PAULIN       
00002|CPP000562752  |0800000020   |+ CCCCCCCCCCCC|0041           |ADRIAN       
00003|CPP000562712  |0800000018   |+ AAAAAAAAAAAA|0041           |HILLAR       
00004|CPP000562712  |0800000019   |+ AAAAAAAAAAAA|0041           |HALE         
00005|CPP000562712  |0800000017   |+ CCCCCCCCCCCC|0041           |MATHEW       


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

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune
PostPosted: Wed Apr 18, 2012 3:11 pm
 Reply with quote
Hi,

Thanks for the suggestion for my record length :

Code:


JOINKEYS FILES=F1,FIELDS=(784,3,A),SORTED     
JOINKEYS FILES=F2,FIELDS=(784,3,A),SORTED     
REFORMAT FIELDS=(F1:1,786,F2:1,786)           
JOIN UNPAIRED,F1                             
OPTION COPY                                   
INREC IFTHEN=(WHEN=(787,1,CH,NE,C' '),       
             BUILD=(1,37,38:853,12,50,733)), 
      IFTHEN=(WHEN=NONE,BUILD=(1,783))       



and I have got the desired output :

Code:

00001|CPP000562712  |0800000000   |+ HAYLEY EMMA |0100           |HAYLEY
00002|CPP000562772  |0800000004   |+ CCCCCCCCCCCC|0055           |PAULIN
00003|CPP000562712  |0800000018   |+ AAAAAAAAAAAA|0041           |HILLAR
00005|CPP000562712  |0800000017   |+ CCCCCCCCCCCC|0041           |MATHEW
00004|CPP000562712  |0800000019   |+ AAAAAAAAAAAA|0041           |HALE 
00002|CPP000562752  |0800000020   |+ CCCCCCCCCCCC|0041           |ADRIAN
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Wed Apr 18, 2012 4:41 pm
 Reply with quote
Without DFSORT's matching indicator, you always have to be careful that the "fill" character cannot exist as data in that particular position. You are probably OK in this case, so just a warning for now.
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Sort SMF records? SYNCSORT 6
No new posts To Sort detail records in a file with... SYNCSORT 5
No new posts Identify and write records containing... SYNCSORT 11
No new posts Filter records where condition is in ... SYNCSORT 6
No new posts Extract records for a specific condit... DFSORT/ICETOOL 25
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