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
 

 

Need help on join keys of syncsort

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

New User


Joined: 04 Dec 2006
Posts: 63
Location: Hyderabad

PostPosted: Wed May 02, 2012 9:16 pm    Post subject: Need help on join keys of syncsort
Reply with quote

Hi can some one plz provide syncsort card for below requirement?


Scenario 1:
Code:
----+----1----+----2----+---
11111 22222 KIRAN—i/pfile-1
11111 22222 33333–i/p file-2

Expected output files

Code:
----+----1----+----2----+---
11111 22222 KIRAN --- expected o/p file-1
11111 22222 KIRAN --- expected o/p file-2

Scenario 2:
Input files
Code:
----+----1----+----2----+---
11111 KIRAN 33333—i/pfile-1
11111 22222 33333 –i/p file-2


Expected output files
Code:
----+----1----+----2----+---
11111 KIRAN 33333—- expected o/p file-1
11111 22222 33333-- expected o/p file-2

Scenario 3:
Input files
Code:
----+----1----+----2----+---
KIRAN 22222 33333—i/pfile-1
11111 22222 33333–i/p file-2

Expected output files
Code:
----+----1----+----2----+---
KIRAN 22222 33333 —- expected o/p file-1
KIRAN 22222 33333 -- expected o/p file-2


Scenario 4:
Input files
Code:
----+----1----+----2----+---
11111 22222 33333—i/pfile-1
11111 22222 33333–i/p file-2

Expected output files
Code:
----+----1----+----2----+---
11111 22222 33333 —- expected o/p file-1
11111 22222 33333 -- expected o/p file-2

Note: out put file-2 has be create strictly from i/p file-2 only, why because fields of i/p file-1 and i/p file-2 may not be same after those comparing fields(I will take care of other fields apart from those 3 comparing fields)


Finally
i/p file-1 is master file which will not have any changes for it
We have to change i/p file-2 according to i/p file-1(master file)
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: Wed May 02, 2012 10:12 pm    Post subject:
Reply with quote

Hello,

In addition to the examples, you need to post the "rules" in sentence/paragraph form and explain what is actually going on.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 02, 2012 10:29 pm    Post subject: Reply to: Need help on join keys of syncsort
Reply with quote

In all your four examples, the record on the 2nd file is the same one. Is that what you want? Four output records from one input?

Is there a single value on file one (KIRAN in the example) or does that represent "any" value?

RECFM/LRECL of the files?

Plus the full description that Dick has asked for.
Back to top
View user's profile Send private message
vicharapusrinu
Warnings : 1

New User


Joined: 04 Dec 2006
Posts: 63
Location: Hyderabad

PostPosted: Wed May 02, 2012 11:13 pm    Post subject:
Reply with quote

Thanks Dick and Bill for looking in to this issue.

Please find below examples with explanation.

i/p file-1 lecl=17
i/p file-2 lecl=17

o/p file-1 lecl=17
o/p file-2 lecl=17

Scenario 1:
Code:
----+----1----+----2----+---
11111 22222 KIRAN —i/pfile-1
11111 22222 33333 –i/p file-2

Expected output files
Code:

----+----1----+----2----+---
11111 22222 KIRAN --- expected o/p file-1
11111 22222 KIRAN --- expected o/p file-2

Explanation of Scenario 1:

i/p file-1 and i/p file-2 is having 3 fields, if first 2 fields of i/p file-1 and i/p file-2 is matching and if 3rd filed of i/p file-1 is KIRAN then, in output
1. i/p file-1 has to come as it is, as o/p file-1,
2. But where as 3rd field of i/p file-2 has to replace with text “KIRAN”, and first 2 fields of i/p file-2 has to come first 2 fields of o/p file-2

Scenario 2:Input files
Code:
----+----1----+----2----+---
11111 KIRAN 33333—i/pfile-1
11111 22222 33333 –i/p file-2

Expected output files
Code:
----+----1----+----2----+---
11111 KIRAN 33333—- expected o/p file-1
11111 KIRAN 33333-- expected o/p file-2

Explanation of Scenario 2:

i/p file-1 and i/p file-2 is having 3 fields, if first 1st and 3rd fields of i/p file-1 and i/p file-2 is matching and if 2nd filed of i/p file-1 is KIRAN then, in output
1.i/p file-1 has to come as it is, as o/p file-1,
2.But where as 2nd field of I/P file-2 has to replace with text “KIRAN”, and 1st and 3rd fields of i/p file-2 has to come 1st and 3rd fields of o/p file-2


Scenario 3:Input files
Code:
----+----1----+----2----+---
KIRAN 22222 33333—i/pfile-1
11111 22222 33333–i/p file-2

Expected output files
Code:
----+----1----+----2----+---
KIRAN 22222 33333 —- expected o/p file-1
KIRAN 22222 33333 -- expected o/p file-2

Explanation of Scenario 3:

i/p file-1 and i/p file-2 is having 3 fields, if 2nd and 3rd fields of i/p file-1 and i/p file-2 is matching and if 1st filed of file-1 is KIRAN then, in output
1.i/p file-1 has to come as it is, as o/p file-1,
2.But where as 1st field of i/p file-2 has to replace with text “KIRAN”, and 2nd and 3rd fields of i/p file-2 has to come 2nd and 3rd fields of o/p file-2



Scenario 4:Input files
Code:
----+----1----+----2----+---
11111 22222 33333—i/pfile-1
11111 22222 33333–i/p file-2

Expected output files
Code:
----+----1----+----2----+---
11111 22222 33333 —- expected o/p file-1
11111 22222 33333 -- expected o/p file-2

Explanation of Scenario 4:

i/p file-1 and i/p file-2 is having 3 fields, if all 3 fields of i/p file-1 and i/p file-2 is matched then in output
1.i/p file-1 has to come as it is, as o/p file-1,
2.i/p file-2 has to come as it is, as o/p file-2

Note: out put file-2 has be create strictly from i/p file-2 only, why because fields of i/p file-1 and i/p file-2 may not be same after those comparing fields(I will take care of other fields apart from those 3 comparing fields)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed May 02, 2012 11:37 pm    Post subject: Reply to: Need help on join keys of syncsort
Reply with quote

You have not made things much clearer.

Each of your examples shows the "same" record on file two, treated in four different ways. With KIRAN in position 1, position 2, position 3 and with no KIRAN. How do you want that to happen? Do you want four output records from one input?

We'll assume Fixed for the record format.
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: Thu May 03, 2012 12:05 am    Post subject:
Reply with quote

Hello,

In what sequence are the input files sorted?

What should happen when there are duplicates in one or both files?
Back to top
View user's profile Send private message
vicharapusrinu
Warnings : 1

New User


Joined: 04 Dec 2006
Posts: 63
Location: Hyderabad

PostPosted: Thu May 03, 2012 12:24 am    Post subject:
Reply with quote

Hi Bill,

Treat each scenario as different test case.

Scenario 1:
Code:
----+----1----+----2----+---
11111 22222 KIRAN —i/pfile-1
11111 22222 33333 –i/p file-2

Expected output files
Code:
----+----1----+----2----+---
11111 22222 KIRAN --- expected o/p file-1
11111 22222 KIRAN --- expected o/p file-2

For example, scenario 1 is having 2 i/p files and is expecting 2 o/p files with below conditions/text

Format of i/p filels
1. i/p file-1 and i/p file-2 is having 3 fields,

Condition to check
1.if first 2 fields of i/p file-1 and i/p file-2 is matching and
2.if 3rd filed of i/p file-1 is KIRAN then,
expected o/p is
1. i/p file-1 has to come as it is, as o/p file-1,
2. But where as 3rd field of i/p file-2 has to replace with text “KIRAN”, and first 2 fields of i/p file-2 has to come first 2 fields of o/p file-2

NOTE: if conditions are matched the only change in 3rd field of file-2(in this case), rest all other field are same.
Back to top
View user's profile Send private message
vicharapusrinu
Warnings : 1

New User


Joined: 04 Dec 2006
Posts: 63
Location: Hyderabad

PostPosted: Thu May 03, 2012 12:34 am    Post subject:
Reply with quote

Hi Dick,

There will not be any chance of having duplicated in both files.

And two input files are sorted against 1st field
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu May 03, 2012 12:50 am    Post subject: Reply to: Need help on join keys of syncsort
Reply with quote

I still don't fully understand, but here is something for example 1.

Code:
//STEP0100 EXEC PGM=SORT
//CHKOUT DD DUMMY
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  OPTION COPY
  JOINKEYS F1=INA,FIELDS=(1,11,A),SORTED
  JOINKEYS F2=INB,FIELDS=(1,11,A),SORTED
  REFORMAT FIELDS=(F1:1,17,?,F2:1,17)
  INREC BUILD=(19,12,13,5)
//INA      DD *
11111 22222 KIRAN
33333 22222 KIRAN
//INB      DD *
11111 22222 33333
33333 22222 33333


Output is:
Code:

11111 22222 KIRAN
33333 22222 KIRAN


If we are treating these as seperate things, you then need to just change the keys and the build to the appropriate values, and probably remove the SORTED, so that a sort is performed before the match.

I don't get the "output" of file one, because it is unchanged. If you want a new identical copy of the file, it is simple with OUTFIL.
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: Thu May 03, 2012 12:57 am    Post subject:
Reply with quote

Hello,

Quote:
And two input files are sorted against 1st field

Code:
----+----1----+----2----+---
KIRAN 22222 33333—i/pfile-1
11111 22222 33333–i/p file-2


Then i believe there is a design problem. . .

Given that the file will have many records, they will not be in sequence to "match" on the first field.
Back to top
View user's profile Send private message
vicharapusrinu
Warnings : 1

New User


Joined: 04 Dec 2006
Posts: 63
Location: Hyderabad

PostPosted: Thu May 03, 2012 1:09 am    Post subject:
Reply with quote

Hi Bill,

Thanks for helping nature.
But I am not getting expecting output.

Could you please take my i/p as it is, as I have posted in my previous post?

May I know what exactly “?” will do in below code?
Quote:
REFORMAT FIELDS=(F1:1,17,?,F2:1,17)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu May 03, 2012 1:23 am    Post subject: Reply to: Need help on join keys of syncsort
Reply with quote

Sorry about the ?, it is a DFSORT thing not available in Syncsort. It tells you the "match status" of the current reformat record.

When you say "a different test case" and given the complication that you have mentioned nothing about with the multiple use of one record, I'm assuming you want to do four seperate runs.

If that is not the case, give example input files and expected output which cover all your processing requirements for one run. Explain, not just by describing what you show, how the matching occurs for your sample records.
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: Thu May 03, 2012 8:59 am    Post subject:
Reply with quote

Hello,

Suggest you re-read my previous reply and explain how the process will match the files when they have different key values. . . icon_confused.gif
Back to top
View user's profile Send private message
kratos86

Active User


Joined: 17 Mar 2008
Posts: 148
Location: Anna NGR

PostPosted: Thu May 03, 2012 11:15 am    Post subject:
Reply with quote

Hello,

Does input files have only single record? if not then what should be the key to match the records. Only then we can match the input file1 with file2 records.

We cannot help you much without all these details.
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 1 Fri Dec 02, 2016 9:47 am
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts ICETOOL/SYNCSORT - SYSPRINT output nartcr DFSORT/ICETOOL 17 Wed Oct 05, 2016 10:46 pm


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