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
 

 

Sorting records with variable key length

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Sorting records with variable key length
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

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Wed Aug 17, 2011 7:20 pm    Post subject:
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: 578
Location: USA

PostPosted: Wed Aug 17, 2011 8:15 pm    Post subject:
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    Post subject:
Reply with quote

LRECL=800, RECFM=FB
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Aug 17, 2011 11:00 pm    Post subject:
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: 578
Location: USA

PostPosted: Wed Aug 17, 2011 11:04 pm    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Aug 17, 2011 11:23 pm    Post subject:
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    Post subject:
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: 1703
Location: Australia

PostPosted: Thu Aug 18, 2011 3:10 pm    Post subject:
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    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 18, 2011 10:56 pm    Post subject:
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: 2158
Location: @my desk

PostPosted: Mon Aug 22, 2011 5:48 pm    Post subject:
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    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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am


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