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
 

 

Get the common values from multiple files - using Syncsort

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

New User


Joined: 24 Oct 2007
Posts: 11
Location: Salem

PostPosted: Fri Jul 04, 2008 12:12 pm    Post subject: Get the common values from multiple files - using Syncsort
Reply with quote

Is there any way to get the common values present in multiple files(more than 2 files) - using Syncsort? The values that are present in all files has to be written to another file.

Example:
File1:
Code:
1
2
3


File2:
Code:
2
5
6
3

File3:
Code:
4
2
1
3


Expected Output:
Code:
2
3
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: Fri Jul 04, 2008 12:33 pm    Post subject:
Reply with quote

Hello,

One way would be to use JOINKEYS and use 2 steps. One to join files 1 & 2, the other to join the matched output from that step to file 3.
Back to top
View user's profile Send private message
Vijayakumar Mani

New User


Joined: 24 Oct 2007
Posts: 11
Location: Salem

PostPosted: Fri Jul 04, 2008 2:36 pm    Post subject:
Reply with quote

Hi dick scherrer,

Thank you for your information. I have tried the JOINKEYS and it works fine. But the number of steps increases as files are in more numbers.

Is there any other way(using Syncsort or other than Syncsort) to get the same result in a single step?
Back to top
View user's profile Send private message
Itanium

Active User


Joined: 22 Jan 2006
Posts: 114
Location: India

PostPosted: Fri Jul 04, 2008 4:51 pm    Post subject:
Reply with quote

you can try the code, SORTXSUM File will have the required values.


Code:
//R010 EXEC PGM=SORT
//SORTIN   DD DSN=FILE1,DISP=SHR
//               DD DSN=FILE2,DISP=SHR
//               DD DSN=FILE3,DISP=SHR
//SORTOUT DD DSN=FILEOUT,
//               DISP=(NEW,CATLG,CATLG),OTHER PARAMETERS
//SORTXSUM DD DSN=FILEXSUM,
//               DISP=(NEW,CATLG,CATLG),OTHER PARAMETERS
//SYSIN DD *
SORT FIELDS=(1,1,CH,A),XSUM
SUM FIELDS =NONE
/*
//SYSOUT DD SYSOUT=*
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Fri Jul 04, 2008 5:21 pm    Post subject:
Reply with quote

Vijayakumar,

Do you have duplicate key values in the same file???

Thanks,
Arun
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 182
Location: hyderabad

PostPosted: Fri Jul 04, 2008 5:56 pm    Post subject:
Reply with quote

Quote:

But the number of steps increases as files are in more numbers.


Vijayakumar Mani,

You can use the SYNCSORT with a ONE STEP for multiple files to achive the desired output.Please provide dummy input and expected output so that you can get quick and correct solution.

Thanks
Krishy
Back to top
View user's profile Send private message
Vijayakumar Mani

New User


Joined: 24 Oct 2007
Posts: 11
Location: Salem

PostPosted: Fri Jul 04, 2008 6:21 pm    Post subject:
Reply with quote

Hi Itanium,

I have tried the code given by you with small change as below.

SORT FIELDS=(1,1,CH,A)
SUM FIELDS=NONE,XSUM

But the output is not as expected. I am getting the below output (Input is as specified in the beginning of my post with 3 files).
Code:
1
2
2
3
3


Quote:
Do you have duplicate key values in the same file???

Hi Arun,
There will not be duplicate values in the same file.

Quote:
Please provide dummy input and expected output

Hi Krishy,
I have provided the dummy input and expected output in the beginning of my post.
Back to top
View user's profile Send private message
Vijayakumar Mani

New User


Joined: 24 Oct 2007
Posts: 11
Location: Salem

PostPosted: Fri Jul 04, 2008 6:32 pm    Post subject:
Reply with quote

Hi Itanium,

The XSUM parameter should be used if you want records deleted by SUM processing to be written to a data set defined by the SORTXSUM DD statement.

Since you have concatenated the 3 files in SORTIN DD statement and specified SUM FIELDS=NONE, the duplicate values will be deleted and those deleted values will be stored in datset defined by the SORTXSUM DD statement.

My requirement is different. I want the particular values found in all files to be written to an output dataset. I think i have specified my requirement clearly in the first post itself.

Please let me know if there is any concern.

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

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Fri Jul 04, 2008 6:53 pm    Post subject:
Reply with quote

Hi Vijyay,

On a first thought can you try using these.............

First step - XSUM step as in your previous post. The output from this goes as next step's input.

Second step card

Quote:

INREC FIELDS=(1,1,2:C'0001')
SORT FIELDS=(1,1,CH,A)
SUM FIELDS=(2,4,ZD)
OUTFIL FNAMES=SORTOUT,INCLUDE=(2,4,ZD,EQ,2),
OUTREC=(1,1)


The bolded value = actual number of input files - 1.

Thanks,
Arun
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 182
Location: hyderabad

PostPosted: Fri Jul 04, 2008 7:08 pm    Post subject:
Reply with quote

Vijayakumar Mani,
check the below code.



Code:

//PS020    EXEC PGM=SYNCSORT                               
//SYSOUT   DD  SYSOUT=*                                     
//TOOLMSG  DD  SYSOUT=*                                     
//DFSMSG   DD  SYSOUT=*                                     
//IN1      DD YOUR I/P FILE 1                               
           DD YOUR I/P FILE 2                               
//T0       DD DSN=&T0,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)   
//T1       DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)   
//CON      DD YOUR INPUT FILE 3                             
//         DD DSN=&T0,DISP=(OLD,PASS),VOL=REF=*.T0         
//OUT      DD SYSOUT=*                                     
//TOOLIN    DD *                                           
    SELECT FROM(IN1) TO(T0) ON(1,1,CH) ALLDUPS             
    SELECT FROM(CON) TO(T1) ON(1,1,CH) ALLDUPS             
    SORT FROM(T1)  TO(OUT) USING(CTL1)                     
//CTL1CNTL  DD *                                           
    SORT FIELDS=(1,1,CH,A),EQUALS                           
    SUM FIELDS=NONE                                         


OUT is going to contain the final o/p.

Thanks
Krishy
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 Jul 05, 2008 12:40 am    Post subject:
Reply with quote

Hello,

I may be mistaken, but i believe this has gone in a different direction than the original request.

If i understood, the output should be only the values that appear in every file. If there were 3 files, only values appearing in all 3 files would go to output. If there were 20 files, onle values appearing in all 20 files would appear in the output.

So far, i believe nothing posted except JOINKEYS will do this. . .

I'm sure we'll let me know if i'm mistaken icon_smile.gif
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Sat Jul 05, 2008 4:41 am    Post subject:
Reply with quote

Hi,
assuming no dups in any file, you can try this
Code:
//S1       EXEC  PGM=ICEMAN                               
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *          FILE1                             
1                                                         
2                                                         
3                                                         
/*                                                         
//         DD *          FILE2                             
2                                                         
5                                                         
6                                                         
3                                                         
7                                                         
/*                                                         
//         DD *          FILE3                             
4                                                         
2                                                         
1                                                         
3     
/*                                                                                       
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                       
  SORT FIELDS=(1,1,CH,A)                               
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,1))       
  OUTFIL INCLUDE=(81,8,ZD,EQ,+3),BUILD=(1,80)         
/*                                                     


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

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Mon Jul 07, 2008 12:51 pm    Post subject:
Reply with quote

Hello Dick,

Quote:
So far, i believe nothing posted except JOINKEYS will do this. . .


I made the posting after testing with sample data. But it needed two steps.
I have an alternative with a single card as below.

Code:
  INREC OVERLAY=(81:C'0001')                   
  SORT FIELDS=(1,1,CH,A)                       
  SUM FIELDS=(81,4,ZD)                         
  OUTFIL FNAMES=SORTOUT,INCLUDE=(81,4,ZD,EQ,'number of input files'),
  OUTREC=(1,80)                               


I believe Gerry's card also needs to be modified with the number of input files.

Thanks,
Arun
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: Mon Jul 07, 2008 1:11 pm    Post subject:
Reply with quote

Hi Arun,

Quote:
I believe Gerry's card also needs to be modified with the number of input files.
Yup, it will icon_smile.gif

If the number of files to process is dynamic, a "front-end" process might detect the number of files and generate the "number of input fles" in whatever code. . .
Back to top
View user's profile Send private message
Vijayakumar Mani

New User


Joined: 24 Oct 2007
Posts: 11
Location: Salem

PostPosted: Mon Jul 07, 2008 5:09 pm    Post subject:
Reply with quote

Quote:
I have an alternative with a single card as below

Hi Arun,

Thanks a lot. Its is working fine and that is what i needed.

Thanks,
Vijay
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 syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
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 Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am


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