Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Syncsort JOINKEYS help needed

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

New User


Joined: 27 Apr 2009
Posts: 26
Location: addison

PostPosted: Fri Jun 05, 2009 7:49 pm    Post subject: Syncsort JOINKEYS help needed
Reply with quote

Hi,

I tried to check in Forum. But i could not figure out how to do.
Please help me with this. Here is how my I/P is and O/P needed.
I want to do it in JOINKEYS. But any other approach is also good for me.
If possible, please give me solution in one step.

I/P:
----
File 1: KEY + DATA - UNIQUE ROWS
File 2: KEY + DATA - UNIQUE ROWS

O/P:
-----
File 3: (File 1 data not in File 2) + (File 2 data not in File1)
File 4: File 2 data not in File1

Thanks,
Chandramouli Ravi
Dallas - TX
Back to top
View user's profile Send private message

Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Fri Jun 05, 2009 11:19 pm    Post subject:
Reply with quote

Are you saying that you want the records reformatted so that it appears as follow:

KEY+DATA from FILE1+DATA from FILE2

OR do you simply want the unique records (records where the KEY only appears in FILE1 or FILE2, but not both)?

Please also specify the RECFM and LRECL of both input files, along with the appropriate position and lengths of all key fields.
Back to top
View user's profile Send private message
chandramouliravi

New User


Joined: 27 Apr 2009
Posts: 26
Location: addison

PostPosted: Fri Jun 05, 2009 11:37 pm    Post subject:
Reply with quote

I simply want the unique records in the output file.
To be easy to discuss, i would like to create just File3 for now.

File 3 should have unique records from File 1 or File 2.
If same Key is present in both files, we have to read record from File 1.
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Sat Jun 06, 2009 12:12 am    Post subject:
Reply with quote

Here is a SyncSort for z/OS job that will produce the requested results.
Since you did not specify the RECFM or the position and length of the KEY fields, I assumed FB with KEY starting in position 1 for length of 5.
Code:
//STEP1  EXEC PGM=SORT                       
//SORTJNF1 DD DSN=File1.input                     
//SORTJNF2 DD DSN=File2.input   
//SORTOUT  DD DSN=File3.output                 
//SYSOUT   DD SYSOUT=*             
//SYSIN    DD *                 
  JOINKEYS FILES=F1,FIELDS=(1,5,A) 
  JOINKEYS FILES=F2,FIELDS=(1,5,A) 
  JOIN UNPAIRED,ONLY               
  SORT FIELDS=COPY   
/*

If you need to create File4 from your original post, let me know and I can modify this job and provide the REFORMAT and OUTFIL statements to accomplish that.
Back to top
View user's profile Send private message
chandramouliravi

New User


Joined: 27 Apr 2009
Posts: 26
Location: addison

PostPosted: Fri Jun 12, 2009 12:03 am    Post subject:
Reply with quote

Hi Alissa,
I have started with code you you gave and started modifying to suit my requirement.

Let me show my i/ps and o/p which i am looking for.
Code:

//*                 
//SORTJNF1 DD *     
2009-10-051000     
2009-10-052000     
2009-10-053000     
2009-10-054000     
/*                 
//*                 
//SORTJNF2 DD *     
2009-10-062000     
2009-10-065000     
/*                 
//*                 
//SORTOF02  DD SYSOUT=*   
//SYSIN    DD *                                             
     JOINKEYS FILE=F1,FIELDS=(11,4,A)                       
     JOINKEYS FILE=F2,FIELDS=(11,4,A)                       
     JOIN UNPAIRED                                         
     REFORMAT FIELDS=(F1:1,14,F2:1,14),FILL=X'FF'           
         SORT FIELDS=COPY                                   
**********************************
     OUTFIL FILES=02,                                       
     OMIT=(1,14,CH,EQ,15,14,CH),                           
     OUTREC=(1:1,14,15:15,14)         --> FROM FILE 2       
/*                                                         
//*                                                         

O/P NEEDED:
--------------
2009-10-051000                 
2009-10-062000      --> 2000 key in file 2
                                   matches with file1.
                                   So this record is taken from File2.
2009-10-053000                 
2009-10-054000                 
2009-10-065000    --> This record is absent in File1. So copied from File 2.

All i trying to do is, read all data from File1 and applies inserts/updates from file2 and write to a new file.
Let me know if i need to explain better.
Back to top
View user's profile Send private message
chandramouliravi

New User


Joined: 27 Apr 2009
Posts: 26
Location: addison

PostPosted: Fri Jun 12, 2009 12:25 am    Post subject:
Reply with quote

Hi Alissa,

The SORT logic which i gave is not working right now.
But igave hte i/ps which i have and o/p which is needed.
Could you please check and let me know if we can do this in SORT.

Thanks,
chandramouli
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Fri Jun 12, 2009 12:39 am    Post subject:
Reply with quote

Here is a SyncSort for z/OS job that will produce your requested output:
Code:
//STEP1  EXEC PGM=SORT               
//SORTJNF1 DD *                       
2009-10-051000                       
2009-10-052000                       
2009-10-053000                       
2009-10-054000                       
//SORTJNF2 DD *                       
2009-10-062000                       
2009-10-065000                       
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                       
   JOINKEYS FILE=F1,FIELDS=(11,4,A)   
   JOINKEYS FILE=F2,FIELDS=(11,4,A)   
   JOIN UNPAIRED                                     
   REFORMAT FIELDS=(F1:1,14,F2:1,14),FILL=X'FF'       
   SORT FIELDS=COPY                                   
   OUTFIL IFTHEN=(WHEN=(15,1,BI,EQ,X'FF'),BUILD=(1:1,15)),
          IFTHEN=(WHEN=NONE,BUILD=(1:15,14))
/*                                                   
Back to top
View user's profile Send private message
chandramouliravi

New User


Joined: 27 Apr 2009
Posts: 26
Location: addison

PostPosted: Fri Jun 12, 2009 1:13 am    Post subject:
Reply with quote

Hi Alissa,

I have tried for couple of days and gaveup infact.
I am so happy to see the answer. Thanks a lot.

Thanks,
Alissa
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Fri Jun 12, 2009 1:26 am    Post subject:
Reply with quote

Glad I could help... when you clearly specify your requirements, as you had done here in your 3rd post, including sample input, desired output, and what you have tried, it is much easier to provide you with a proper solution icon_smile.gif
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 JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us