Page 1 of 2

Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 12:23 pm
by jags
Hi,
I have two GDG generations.Each contains some set of user ids.
Pls See below for details.

Note:There are no keys present other than the user id i.e no other info in the datasets except user ids

What I need is unpaired user ids present in GDG-1 alone with duplicates.
i.e as per the below info in fig-1, CCCC,DDDD,SSSS is present only in GDG-1 so I get that in the output
but AAAA is present twice in GDG-1 and only once in GDG-2 hence I need AAAA once in the ouput dataset along with (CCCC,DDDD,SSSS) but am unable to achieve it.

I used Join keys in SORT PGM

JOIN KEYS F1=(12,4,A)
JOIN KEYS F2=(22,4,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1,1:80)

(Please forgive for syntax errors)

Fig-1
GDG-1(data in cols 12-15)
-------------------------------------
AAAA
BBBB
CCCC
DDDD
AAAA
SSSS




GDG-2 (data in cols 22-25)
-------------------------------------
AAAA
BBBB
EEEE
SSSS


Output GDG what I Need
------------------------------------
AAAA
CCCC
DDDD
SSSS


But what I actually get by using join keys is below


Output GDG what I get using JOIN keys
---------------------------------------
CCCC
DDDD
SSSS


I tried using join keys also please let me know if you have any other approach.
I have a very minimal experience in coding..but still I tried to a extent.Unfortunately couldn't able to achieve the results.
Looking forward for some help.Thank you.

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 1:14 pm
by BillyBoyo
You've not done badly. JOINKEYS is the correct way to go.

However, where you have JOIN UNPAIRED,F1,ONLY that means "only give me unmatched records from F1, nothing else".

JOIN UNPAIRED,F1 will give you matched records and unpaired F1 records.

Now you need to be a little clearer with your sample input and expected output. You need to show single match (one matching key on F1 and F2) multiple one-to-one match (two or more but same number for each key), single to multiple (one to two should be enough) and multiple to single (two to one). Then expected output for those.

Also can you include this temporarily in your step:

//JNF1CNTL DD *
INREC OVERLAY=(12:C'HI')


And post the full sysout from your step, inside the Code tags if possible (if you want to see how it works, click on the Quote button and look at the above two lines). This will help us determine which SORT product you have and its capabilities.

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 3:51 pm
by jags
sorry it's not possible for me to post the sysout here
If you need my SORT product details, I see it as SYNCSORT for Z/os 1.4.1.0N

I need only unmatched records in GDG1 only (along with one instance of AAAA)

AAAA is present twice in GDG-1 whereas it is present only once in GDG2.
As per the code I have written what is happening is that its removing duplicates then trying to compare
and hence I am not getting the AAAA id which is present for the second time in GDG1.



Hope I made you clear

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 4:49 pm
by Akatsukami
jags wrote:sorry it's not possible for me to post the sysout here

Why not?

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 4:51 pm
by jags
I use a VM

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 4:52 pm
by BillyBoyo
OK.

I don't know if JNFnCNTLs work for your SyncSort. You'll find out...

On JNF1CNTL and JNF2CNTL, specify the addition of a sequence number to each record, with a RESTART= for the correct key positions for the respective inputs.

Then add the position of the sequence numbers to the JOINKEYS key.

Where you have multiple AAAA they will no longer all match to the single AAAA.

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 6:03 pm
by jags
I am not sure If I made any wrong.Below is what I coded and the output I got


Sort Step..
----------
SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(12,6,A)
JOINKEYS F2=IN2,FIELDS=(22,6,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FEILDS=(F1:1,80)
SORT FIELDS=COPY
/*
//JNF1CNTL DD *
INREC OVERLAY=(69:SEQNUM,3,ZD,RESTART=(12,6))
/*
//JNF2CNTL DD *
INREC OVERLAY=(69:SEQNUM,3,ZD,RESTART=(22,6))
/*


But the o/p i got is
--------------------

BBBB 001
AAAA 001
AAAA 001
AAAA 002
CCCC 001
CCCC 002


GDG-1 contained
---------------
BBBB
AAAA
AAAA
AAAA
CCCC
CCCC


GDG-2 Contained
---------------

BBBB
AAAA
AAAA
CCCC

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 6:52 pm
by BillyBoyo
Your data isn't getting sorted. Why can't we see the sysout again?

You need to include the sequence number as a second part of the JOINKEYS key for each file.

Try adding SORT FIELDS=(with the joinkeys keys) into each of the JNFnCNTL files.

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 8:58 pm
by jags
When I include - SORT fields=(12,6,A) & SORT fields=(22,6,A) in JNF1CNTL,JNF2CNTL statements
I get 'SORT statement - syntax error'. But I think am giving it right.Searched a lot but couldn't find anything.

Sysout with SORT statment in JNF1&2 says
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER486A ERROR IN JNF1 PROCESSING



Below is the sysout without SORT statment in JNF1&2

WER211B SYNCSMF CALLED BY SYNCSORT;RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER416B SORTOUT: EXCP'S=1,UNIT=....
WER416B TOTAL OF 1 EXCP'S ISSUED FOR COPYING
WER054I RCD IN 9, OUT 9
WER072I NOEQUALS, BALANCE IN EFFECT
WER482I JNF1 STATISTICS

DCB DETAILS
DSN NAME

WER483B G=116
WER484I IN1 : RCD IN=9,OMITTED=0,PAIRED=0,UNPAIRED=9
WER416B TOTAL OF 1 EXCP'S ISSUED FOR SORTING

SIMILARLY JNF2 STATS

Apologies for improper SYSOUT details..

Re: Comapre & Sort with duplicates

PostPosted: Wed Sep 25, 2013 9:23 pm
by BillyBoyo
I've not used SyncSort with JOINKEYS. The way DFSORT works the SORT is automatic, on the keys. If you can't specify SORT in the JNFnCNTL, then it looks like SyncSort is the same.

So, why was the output you showed not SORTed? Plan B is that you don't have the correct start positions. You also have the RESTART on the same positions, and if you look at the output you showed the AAAA get number 1, 1, 2 and should have been 1, 2, 3.

Is your file an FB, or a VB? (I was hoping to see on the sysout).

//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(12,6,A,69,3,A)
  JOINKEYS F2=IN2,FIELDS=(22,6,A,69,3,A)
  JOIN UNPAIRED,F1,ONLY
  REFORMAT FEILDS=(F1:1,80)
  SORT FIELDS=COPY
/*
//JNF1CNTL DD *
  INREC OVERLAY=(69:SEQNUM,3,ZD,RESTART=(12,6))
/*
//JNF2CNTL DD *
  INREC OVERLAY=(69:SEQNUM,3,ZD,RESTART=(22,6))
/*


Try with that, and show the full input records, and the full output records. Check your start positions. Show the full sysout including all the message numbers please.