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
 

 

Joining records of two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Joining records of two files
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Apr 17, 2012 7:49 pm    Post subject: Reply to: Merging records of two files having
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    Post subject:
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Apr 17, 2012 8:15 pm    Post subject: Reply to: Merging records of two files having
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Apr 17, 2012 9:06 pm    Post subject: Reply to: Joining records of two files
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: 645
Location: Pennsylvania

PostPosted: Tue Apr 17, 2012 10:10 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Tue Apr 17, 2012 10:26 pm    Post subject: Reply to: Joining records of two files
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: 645
Location: Pennsylvania

PostPosted: Tue Apr 17, 2012 10:28 pm    Post subject:
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Apr 18, 2012 1:15 pm    Post subject: Reply to: Joining records of two files
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: 1703
Location: Australia

PostPosted: Wed Apr 18, 2012 2:01 pm    Post subject:
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    Post subject: Reply to: Joining records of two files
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Apr 18, 2012 4:41 pm    Post subject: Reply to: Joining records of two files
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    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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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