IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sorting records with variable key length
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
sreedharkonduru

New User


Joined: 25 May 2011
Posts: 7
Location: India
PostPosted: Wed Aug 17, 2011 7:16 pm
 Reply with quote
Hi All,

I have got an assignment where in I need to sort records and also eliminate duplicates which have variable key length in a "|" delimited file. My data looks as below:

Code:


4145000106/17/2011NASA005SC00600|CAR|#NULL#|CICP|.....
534277000106/17/2011EBAC01DFS01586|CAR|#NULL#|CICP|....
56223000106/03/2011NPLPCONV006A0004394|CAR|#NULL#|CICP|...
80099904000106/03/2011NAHA001CV07621|CAR|#NULL#|CICP|.......
20016506012011000106/25/2011NAHA001CV07621|CAR|#NULL#|CICP|...
CLCM916893A000106/25/2011113074|CAR|#NULL#|CICP|.......
34987000106/06/2011#NULL#13855|CAR|#NULL#|CICP|.........
2580419409000106/10/2011NAEA20501TXA15031|CAR|#NULL#|CICP|....




I need to sort based on the field of the record till I encounter the FIRST "|" character.

Please help me
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1216
Location: Dublin, Ireland
PostPosted: Wed Aug 17, 2011 7:20 pm
 Reply with quote
Google "DFSORT Smart Tricks" which shows how to use PARSE to expand fields in delimited files to do just what you want.

Garry.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Aug 17, 2011 8:15 pm
 Reply with quote
sreedharkonduru,
What is the LRECL and RECFM of the input file and your expected output? What is the max. length of the first field or key field on which you want to eliminate duplicates?

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

New User


Joined: 25 May 2011
Posts: 7
Location: India
PostPosted: Wed Aug 17, 2011 10:55 pm
 Reply with quote
LRECL=800, RECFM=FB
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Aug 17, 2011 11:00 pm
 Reply with quote
sreedharkonduru,

What is the maximum length of the key?

What is the expected output?

Given that you have different lengths for the "keys", do you want to pad the shorter key on the right with blanks for sorting? Or do you want to "normalize" the keys in some other way (how)?
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Aug 17, 2011 11:04 pm
 Reply with quote
sreedharkonduru,
Asking again... --> What is the max. length of the first field or key field on which you want to eliminate duplicates?

However, rather than going back and forth on questions, based on sample input data,I am assuming it is 20 bytes alphanumeric field (CLCM916893A000106) , you can change it accordingly if it doesn't suit your requirement.

Code:
//SYSIN    DD *                                                 
  INREC IFTHEN=(WHEN=INIT,PARSE=(%01=(ENDBEFR=C'/',FIXLEN=20)),
               BUILD=(1,800,%01))                               
  SORT FIELDS=(801,20,CH,A),EQUALS                             
  SUM FIELDS=NONE                                               
  OUTFIL BUILD=(1,800)                                         
/*                                                             


Edited :- Frank, sorry I didn't see your response before posting.

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

New User


Joined: 25 May 2011
Posts: 7
Location: India
PostPosted: Wed Aug 17, 2011 11:07 pm
 Reply with quote
Hi Frank,

Thank you for quick turn around. The maximum length of the key is 90.
No padding the key on the right. There should not be any change in the record. Records should only be sorted.

I'll try it sqlcode1
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Aug 17, 2011 11:23 pm
 Reply with quote
Quote:
No padding the key on the right. There should not be any change in the record. Records should only be sorted.


I didn't mean permanent padding of the field. Just how you want the fields normalized for sorting. If you had given all the information asked for (such as the expected output), we wouldn't need to keep asking questions. Assuming you want trailing blanks for normalizing the sort keys, you can use these DFSORT control statements:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/800)
//SORTOUT DD DSN=...  output file (FB/800)
//SYSIN DD *
  OPTION EQUALS
  INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=90)),
    OVERLAY=(801:%01)
  SORT FIELDS=(801,90,CH,A)
  SUM FIELDS=NONE
  OUTREC BUILD=(1,800)
/*


For your input example, SORTOUT would have:


Code:

CLCM916893A000106/25/2011113074|CAR|#NULL#|CICP|.......
20016506012011000106/25/2011NAHA001CV07621|CAR|#NULL#|CICP|...
2580419409000106/10/2011NAEA20501TXA15031|CAR|#NULL#|CICP|....
34987000106/06/2011#NULL#13855|CAR|#NULL#|CICP|.........
4145000106/17/2011NASA005SC00600|CAR|#NULL#|CICP|.....
534277000106/17/2011EBAC01DFS01586|CAR|#NULL#|CICP|....
56223000106/03/2011NPLPCONV006A0004394|CAR|#NULL#|CICP|...
80099904000106/03/2011NAHA001CV07621|CAR|#NULL#|CICP|.......


Note that I used C'|' as the delimiter as you requested, not C'/'.
Back to top
View user's profile Send private message
sreedharkonduru

New User


Joined: 25 May 2011
Posts: 7
Location: India
PostPosted: Thu Aug 18, 2011 3:06 pm
 Reply with quote
Thank you Frank,

They changed the requirement. Based on your suggestion and refering suggestions on various posts I coded but I am getting error.

Current Data Format:

Code:

SFRECEIPTID|SFRECEIPTDATE|SFPARTNBR|SFSUPPLIERID|SFBU
41200001|06/13/2011|EBAC06TXVX|00600|CAR|#NULL#|CICP|
5342770001|06/17/2011|EBAC01DFS|01586|CAR|#NULL#|CICP|
800999040001|06/03/2011|NAHA001CV|07621|CAR|#NULL#|CICP|
CLCM916893A0001|06/25/2011|1|13074|CAR|#NULL#|CICP|
349870001|06/06/2011|#NULL#|13855|CAR|#NULL#|CICP|
25804194090001|06/10/2011|NAEA20501TXA|15031|CAR|#NULL#|CICP|
OS00620110001|06/24/2011|1|24480|CAR|#NULL#|CICP|


Required Format:

The First record should not be disturbed. The rest of the records should be sorted removing duplicates.

Code:

SFRECEIPTID|SFRECEIPTDATE|SFPARTNBR|SFSUPPLIERID|SFBU
CLCM916893A0001|06/25/2011|1|13074|CAR|#NULL#|CICP|
OS00620110001|06/24/2011|1|24480|CAR|#NULL#|CICP|
25804194090001|06/10/2011|NAEA20501TXA|15031|CAR|#NULL#|CICP|
349870001|06/06/2011|#NULL#|13855|CAR|#NULL#|CICP|
41200001|06/13/2011|EBAC06TXVX|00600|CAR|#NULL#|CICP|
5342770001|06/17/2011|EBAC01DFS|01586|CAR|#NULL#|CICP|
800999040001|06/03/2011|NAHA001CV|07621|CAR|#NULL#|CICP|


The code I have written:

Code:
//VARSORT  EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN DD DSN=...  input file (FB/800)
//SORTOUT DD DSN=...  output file (FB/800)     
//SYSIN    DD *                                                   
  OPTION EQUALS                                                   
  INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=20),      max length of field -20
               %02=(ENDBEFR=C'|',FIXLEN=12),       max length of field -12
               %03=(ENDBEFR=C'|',FIXLEN=50),      max length of field -50
               %04=(ENDBEFR=C'|',FIXLEN=20)),      max length of field -20
    OVERLAY=(801:%01,821:%02,833:%03,883:%04)                     
    SORT FIELDS=(801,20,CH,A,821,12,CH,A,833,50,CH,A,883,20,CH,A) 
    SUM FIELDS=NONE                                               
    OUTREC BUILD=(1,800)                                           
/*                                                                 
//     



Job abended with U0016 return code. The sysout is below:

Code:

SYSIN :                                                             
  OPTION EQUALS                                                     
  INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=20),                         
        *                                                           
               %02=(ENDBEFR=C'|',FIXLEN=12),                         
               %03=(ENDBEFR=C'|',FIXLEN=50),                         
               %04=(ENDBEFR=C'|',FIXLEN=20)),                       
    OVERLAY=(801:%01,821:%02,833:%03,883:%04)                       
    SORT FIELDS=(801,20,CH,A,821,12,CH,A,833,50,CH,A,883,20,CH,A)   
    SUM FIELDS=NONE                                                 
    OUTREC BUILD=(1,800)                                             
WER268A  INREC STATEMENT   : SYNTAX ERROR                           
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                       
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE


Please correct my code. Thanks a lot for your help in advance.

PS: My code does affects the first record. Please look into that issue also
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Thu Aug 18, 2011 3:10 pm
 Reply with quote
Hi

Frank's solution is for DFSORT, you are using SYNCSORT.


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

New User


Joined: 25 May 2011
Posts: 7
Location: India
PostPosted: Thu Aug 18, 2011 3:16 pm
 Reply with quote
Hello Gerry,

I was not aware that DFSORT and SYNCSORT are different. Thanks for the information. Now how do I proceed on this as I think SYNCSORT is present on my Client's mainframe.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Thu Aug 18, 2011 3:26 pm
 Reply with quote
sreedharkonduru wrote:
Hello Gerry,

I was not aware that DFSORT and SYNCSORT are different. Thanks for the information. Now how do I proceed on this as I think SYNCSORT is present on my Client's mainframe.


Look into SYNCSORT documentation for your release..
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Aug 18, 2011 10:56 pm
 Reply with quote
Quote:

I was not aware that DFSORT and SYNCSORT are different.


Yes, DFSORT is IBM's sort product and Syncsort is Syncsort's sort product.

I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Mon Aug 22, 2011 5:48 pm
 Reply with quote
sreedharkonduru,

Which Syncsort version are you running?. IIRC Syncsort supports 'PARSE' since its 1.3 release.
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Identify and write records containing... SYNCSORT 11
No new posts Filter records where condition is in ... SYNCSORT 6
No new posts Extract records for a specific condit... DFSORT/ICETOOL 25
No new posts Writing next records after a match an... Compuware & Other Tools 5
No new posts variable in library name within a PROC. JCL & VSAM 11
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us