MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Getting first occurence of the record using SYNCSORT.
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 2:33 am    Post subject: Getting first occurence of the record using SYNCSORT. Reply with quote
Hi,

I need only records with "STD" or "REF" at position 6. This can be done by INCLUDE, but for "REF" records I need only the first occurence. Can this be done by SEQNUM ?? Can I reset the SEQNUM in SYNCSORT ??

We have SYNCSORT at our shop.
Code:

----+----1----+----2
AABHYSTDPERE
BAMQHSTDPERE
BAMQHREFOPER
BAMQHREFUPER
BAMQHSTDPERE
BBCBKREFUPER
BBCBKREFUPEF
BBCBZREFFERE
BBCBZREFR342


Thanks !
_________________
-kr
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 3:15 am    Post subject: Reply with quote
krkcs,

Quote:

Can I reset the SEQNUM in SYNCSORT ??


The answer is "Yes" & "No". Yes - If you have latest version of syncsort (v 1.1 or 1.2). No - if not.

Quote:

I need only records with "STD" or "REF" at position 6. This can be done by INCLUDE, but for "REF" records I need only the first occurence.


From your statement, I understand that you need all occurrences of STD but only one occurrence of REF. Correct me if I am wrong.

This being the case and if you are not concerned with the order of records in the output file then the following SORT should get you desired results.

I'm assuming that your SORTIN is of RECFM=FB & LRECL=80.
Code:

//R010   EXEC  PGM=SORT
//SORTIN   DD  *
AABHYSTDPERE
BAMQHSTDPERE
BAMQHREFOPER
BAMQHREFUPER
BAMQHSTDPERE
BBCBKREFUPER
BBCBKREFUPEF
BBCBZREFFERE
BBCBZREFR342
/*
//SORTOUT  DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSIN    DD  *
  OPTION EQUALS
  INCLUDE COND=(6,3,SS,EQ,C'STD,REF')       * INCLUDE ONLY STD & REF *
  INREC FIELDS=(1,80,                       * COPY INPUT DATA   *
                6,3,CHANGE=(1,C'REF',C'0'), * CHANGE REF TO '0' *
                   NOMATCH=(C'1'))          * ALL OTHERS TO '1' *
  SORT FIELDS=(81,1,ZD,A)                   * SORT ON CONSTANT @ 81 *
  OUTREC FIELDS=(1,81,SEQNUM,8,ZD)          * INCLUDE SEQNUM @ 82-89 *
  OUTFIL INCLUDE=((6,3,CH,EQ,C'REF',AND,82,8,ZD,EQ,1),OR,
                   6,3,CH,EQ,C'STD')
/*


Description:
Code:

1.  Include only records with STD & REF @ column 6 (INCLUDE COND)
2.  Insert a constant character at Position 81 depending on the value in column 6-8.
     a.  For 'REF' insert '0' @ pos 81
     b.  For 'STD' insert '1'
3.  Sort on this constant char so that REFs comes first followed by STDs.
4.  Using OUTREC, include SEQNUM @ position 82-89.  Now first record (which is a REF) will have seqnum 00000001.
5.  Using OUTFIL INCLUDE, for REFs include only one record which has seqnum 1 and for STDs include everything.


Hope this helps,

Thanks,
Phantom
Back to top
View user's profile Send private message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 3:42 am    Post subject: Reply with quote
Thanks for the reply Phantom. I tried your code and I'm getting only STD records as output. Where as my output should be like this.
Code:

AABHYSTDPERE
BAMQHSTDPERE
BAMQHREFOPER
BAMQHSTDPERE
BBCBKREFUPER
BBCBZREFFERE


I think I have right version of Syncsort itself.
Code:

----+----1----+----2----+----3----+
1 SYNCSORT FOR Z/OS  1.1DR   TPF3 

_________________
-kr
Back to top
View user's profile Send private message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 3:48 am    Post subject: Reply with quote
Phantom,
Where are we resetting the SEQNUM here ?? I think we should reset the SEQNUM counter whenever it encounters the new data at pos-1, len-5. Huh..am I making some point Exclamation
_________________
-kr
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 4:15 am    Post subject: Reply with quote
Krkcs,

Quote:

Huh..am I making some point


Ofcourse yes. But the code I gave you does not require a Seqnum RESET and it works absolutely fine for me.

This is the output I got. 1 REF record is copied to the output and all STD records follow it.

Code:

---+----1----+----2----+----3---
********************************
BAMQHREFOPER                   
AABHYSTDPERE                   
BAMQHSTDPERE                   
BAMQHSTDPERE                   
********************************


Quote:

Where as my output should be like this.

AABHYSTDPERE
BAMQHSTDPERE
BAMQHREFOPER
BAMQHSTDPERE
BBCBKREFUPER
BBCBZREFFERE


There are 2 REFs in your output. You said you wanted only 1 REF.

Quote:

I think we should reset the SEQNUM counter whenever it encounters the new data at pos-1, len-5.


First 5 characters (you never mentioned anything about these in your original post). Is this the key ?

Please be clear in your request.

Thanks,
Phantom
Back to top
View user's profile Send private message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 4:19 am    Post subject: Reply with quote
Yes Phantom. The first five characters of my input file is the Key for which there should be only a single "REF". I have two "REF" in my output, this is because I need only first occurence of "REF" record, for each and every unique key.

I should have been more clear with my question... Mad
_________________
-kr
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 4:34 am    Post subject: Reply with quote
Krkcs,

With Syncsort v 1.1 You can break seqnum based on a field. Read this topic (5th post from Bottom - By Kolusu)

http://www.mvsforums.com/helpboards/viewtopic.php?t=2628&highlight=breaking+sequence

Thanks,
Phantom
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Oct 11, 2005 4:39 am    Post subject: Reply with quote
Phantom,

hmm isn't it simple as this

Code:

   OPTION EQUALS
   INCLUDE COND=(6,3,SS,EQ,C'STD,REF')
   SORT FIELDS=(1,5,CH,A,
                6,3,CH,A)
   SUM FIELDS=NONE


For every key on first 5 bytes , he will get one 'ref' and 'std' record.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 4:46 am    Post subject: Reply with quote
Kolusu,

Quote:

For every key on first 5 bytes , he will get one 'ref' and 'std' record.


Nope!...He wants only 1 REF per key but all STD recs.

Thanks,
Phantom
Back to top
View user's profile Send private message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 4:47 am    Post subject: Reply with quote
Phantom,
In this post, Kolusu has given solution using ICETOOL, will it be working for SYNCSORT too ?? Anyway I'm checking it...thanks for the info. Phantom !
_________________
-kr
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Oct 11, 2005 4:49 am    Post subject: Reply with quote
Quote:

Nope!...He wants only 1 REF per key but all STD recs.


phantom,

I re-read the post once again and I did not see that. Even his sample output does not mention it.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 4:53 am    Post subject: Reply with quote
Krkcs,

Quote:

In this post, Kolusu has given solution using ICETOOL, will it be working for SYNCSORT too ??


You need to change PGM=ICETOOL to PGM=SYNCTOOL. I believe Kolusu must have mentioned that. (He always does mention that).

Anyway, I have devised another solution without SPLICE. It takes 2 passes. See below.

Code:

//R010   EXEC  PGM=SYNCTOOL                         
//TOOLMSG  DD  SYSOUT=*                             
//DFSMSG   DD  SYSOUT=*                             
//INPUT    DD  *                                   
AABHYSTDPERE                                       
BAMQHSTDPERE                                       
BAMQHREFOPER                                       
BAMQHREFUPER                                       
BAMQHSTDPERE                                       
BBCBKREFUPER                                       
BBCBKREFUPEF                                       
BBCBZREFFERE                                       
BBCBZREFR342                                       
/*                                                 
//TEMP     DD  DSN=&&T1,DISP=(,PASS)               
//OUTPUT   DD  SYSOUT=*                             
//TOOLIN   DD  *                                   
  COPY FROM(INPUT)  TO(TEMP)    USING(CTL1)         
  SORT FROM(TEMP)   TO(OUTPUT)  USING(CTL2)         
//CTL1CNTL DD  *                                                     
  INCLUDE COND=(6,3,SS,EQ,C'STD,REF')       * INCLUDE ONLY STD & REF *
  INREC FIELDS=(1,80,                       * COPY INPUT DATA        *
                SEQNUM,8,ZD)                * INCLUDE SEQNUM * 82-89 *
  OUTREC FIELDS=(1,80,                                               
                 6,3,CHANGE=(8,C'REF',C'00000000'),                   
                    NOMATCH=(81,8))                                   
/*                                                                   
//CTL2CNTL DD  *                                                     
  OPTION EQUALS                                                       
  SORT FIELDS=(1,5,CH,A,                                             
               6,3,CH,A,                                             
               81,8,ZD,A)                                             
  SUM FIELDS=NONE                                                     
/*                                                                   


Pass 1:
1. Include a 8 digit sequence number at pos 82-89.
2. Reset this seqnum to ZEROES for records which have REF @ pos 6.

Pass 2:
1. Sort on key + REF/STD + Seqnum
2. Eliminate duplicates. Now that seqnum is ZEROES for all REF records irrespective of key, duplicate are ignored (Note: Since Key is part of the SORT criteria, one REF per key is maintained).
3. None of the duplicate STDs are rejected since their SEQNUM are always different.

Does this solve your requirement ?

Also, With the new code, I got 3 REFs in the output.
Code:

********************************* TOP OF D
AABHYSTDPERE                             
BAMQHREFOPER                             
BAMQHSTDPERE                             
BAMQHSTDPERE                             
BBCBKREFUPER                             
BBCBZREFFERE                             
******************************** BOTTOM OF


Thanks,
Phantom

Last edited by Phantom on Tue Oct 11, 2005 4:59 am; edited 1 time in total
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 4:57 am    Post subject: Reply with quote
Kolusu,

This is his original message
Quote:

This can be done by INCLUDE, but for "REF" records I need only the first occurence


Also, this is what he gave as sample output.
Code:

AABHYSTDPERE
BAMQHSTDPERE
BAMQHREFOPER
BAMQHSTDPERE
BBCBKREFUPER
BBCBZREFFERE


Look at the 2nd and 4th record. They have key as 'BAMQH' and 6th position contains 'STD'.

Krkcs,
Please clarify which way you want us to eliminate dups ? for both REF & STD or only for REF ?

Thanks,
Phantom
Back to top
View user's profile Send private message
krkcs
Beginner


Joined: 30 May 2004
Posts: 27
Topics: 5
PostPosted: Tue Oct 11, 2005 4:58 am    Post subject: Reply with quote
Kolusu,

I need all the STD records for all the keys(key is 1 to 5 bytes) but REF records only the first occurence of the key.
Code:

AABHYSTDPERE - This should be written to output (since it's STD)
BAMQHSTDPERE - This should be written to output (since it's STD)
BAMQHREFOPER - Since this is the first occurence of BAMQH with REF, this should be written
BAMQHREFUPER - Omit
BAMQHSTDPERE - This should be written to output (since it's STD)
BBCBKREFUPER - Since this is the first occurence of BBCBK with REF, this should be written
BBCBKREFUPEF - Omit
BBCBZREFFERE - Since this is the first occurence of BBCBZ with REF, this should be written
BBCBZREFR342 - Omit


Hope I'm bit more clear now..!!
_________________
-kr
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Oct 11, 2005 5:01 am    Post subject: Reply with quote
Krkcs,

This being the case, the last solution that I gave should work fine for you. Try and let me know.

Thanks,
Phantom
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group