Sort on the basis of diferent sort position in a single file



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sort on the basis of diferent sort position in a single file

Postby gauravnnl » Tue Jan 27, 2015 5:24 pm

Hi All,

Looking for quick help in finding approach to get solution. My input file is a VSAM file with LRECL=500 and key=100 have a set of records and the file has to be sorted on those sets i.e one set of records needs to be sorted on one position and other set of records to be sorted on other. Just for example below are the 4 types and sort criteria

ACCOUNT_TYPE --> sort position (105,1),(20,60)
BILL_CLASS --> sort position (101,47)
BILL_SUB_CLASS --> sort position (20,3),(151,1),(100,44)
COUNTRIES --> sort position (105,1)

Below is the sample file, just for reference. Since the file length is 500. I just copied first 72 bytes from the files which are a part o key.

<===+====10===+====2====+====3====+====4====+====5====+====6====+====7==
****  Top of data  ****                                                 
ACCOUNT_TYPE        smile current account                               
ACCOUNT_TYPE        smile personal loan                                 
BILL_CLASS          015                                                 
BILL_CLASS          016                                                 
BILL_CLASS          017                                                 
BILL_CLASS          018                                                 
BILL_CLASS          019                                                 
BILL_SUB_CLASS      001001                                             
BILL_SUB_CLASS      002001   
COUNTRIES           Abudhabi     
COUNTRIES           Afghanistan   
COUNTRIES           Aland Islands
COUNTRIES           Albania       
COUNTRIES           Algeria       
COUNTRIES           American Samoa   


Please hep to find the approach and let me know if need any other details.Thanks
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Sort on the basis of diferent sort position in a single

Postby Aki88 » Wed Jan 28, 2015 9:43 am

Hello Gaurav,

gauravnnl wrote: My input file is a VSAM file

the file has to be sorted on those sets i.e one set of records needs to be sorted on one position and other set of records to be sorted on other


If I have understood the requirement correctly (and correct me if I have it wrong), then a query before we dive into this, you want to extract the data into an o/p PS DS, or you want the records sorted in place - in a VSAM file?

If requirement is to sort the data on anything other than keys - in the VSAM file, thereby o/p'ing a VSAM file of same properties, then imho we're out of luck, the DFSORT might just fail with ICE077A.

Though, if you are fine with writing the data to an o/p seq file, you can use a simple SORT FIELDS=<Your Multiple SORT keys here>; and that should do the trick.

Hth.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Sort on the basis of diferent sort position in a single

Postby BillyBoyo » Wed Jan 28, 2015 2:08 pm

You will need to temporarily extend the record by 47 bytes, then use IFTHEN=(WHEN=(logical expression to put different data (what you want to sort on) for different records. Then SORT on the extension.

These users thanked the author BillyBoyo for the post (total 2):
gauravnnl (Wed Jan 28, 2015 4:32 pm) • Aki88 (Wed Jan 28, 2015 2:18 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Sort on the basis of diferent sort position in a single

Postby gauravnnl » Wed Jan 28, 2015 4:35 pm

Thanks Billy... I managed to write the sort card. Below is the card for reference:-

//SYSIN     DD *                                                   
   INREC IFTHEN=(WHEN=INIT,                                         
                  OVERLAY=(501:100C'0')),                           
          IFTHEN=(WHEN=(1,20,CH,EQ,C'ACCOUNT_TYPE        '),       
                  OVERLAY=(501:1,20,105,1,21,40)),                 
          IFTHEN=(WHEN=(1,20,CH,EQ,C'BILL_CLASS          '),       
                  OVERLAY=(501:1,20,101,50)),                       
          IFTHEN=(WHEN=(1,20,CH,EQ,C'BILL_SUB_CLASS      '),       
                  OVERLAY=(501:1,20,21,3,151,1,101,50)),           
          IFTHEN=(WHEN=(1,20,CH,EQ,C'COUNTRIES           '),       
                  OVERLAY=(501:1,20,105,1)),                       
          IFTHEN=(WHEN=NONE,                                       
                  OVERLAY=(501:1,20))                               
    SORT FIELDS=(501,100,CH,A)                                     
                                                                   
   OUTREC FIELDS=(1:1,500)                                         
/*                                                                 
gauravnnl
 
Posts: 21
Joined: Tue Jul 15, 2014 2:25 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Sort on the basis of diferent sort position in a single

Postby BillyBoyo » Wed Jan 28, 2015 5:04 pm

Thanks for posting your solution, it may help others in the future.

Your code is correct, but I've made some suggestions below.

Your extension seems to be a maximum of 74 bytes. If so, make it that, otherwise there's 26 bytes extra per record being sorted.

Since 1,20 is needed for all records, it can be done on the INIT, removing the need for the NONE.

54Z will give you 54 zoned-decimal zeros (X'F0').

DFSORT will initialise all the extended areas to blanks. If the zeros are not needed for your sort order, you can ditch the 54Z anyway, and use the IFOUTLEN (sets the length of records after IFTHEN processing). If the zeros are needed, keep the 54Z and remove the IFOUTLEN.

OUTREC BUILD is clearer than OUTREC FIELDS, because FIELDS is "overloaded" (it means different things in different contexts).

The 1: is not needed on the BUILD (or FIELDS) because the default is to start the first byte of data.

//SYSIN     DD *                                                   
   INREC IFOUTLEN=574,
          IFTHEN=(WHEN=INIT,                                         
                  OVERLAY=(501:1,20,54Z)),                           
          IFTHEN=(WHEN=(1,20,CH,EQ,C'ACCOUNT_TYPE        '),       
                  OVERLAY=(521:105,1,21,40)),                 
          IFTHEN=(WHEN=(1,20,CH,EQ,C'BILL_CLASS          '),       
                  OVERLAY=(521:101,50)),                       
          IFTHEN=(WHEN=(1,20,CH,EQ,C'BILL_SUB_CLASS      '),       
                  OVERLAY=(521:21,3,151,1,101,50)),           
          IFTHEN=(WHEN=(1,20,CH,EQ,C'COUNTRIES           '),       
                  OVERLAY=(521:105,1))
                       
    SORT FIELDS=(501,74,CH,A)                                     
                                                                 
   OUTREC BUILD=(1,500)       

These users thanked the author BillyBoyo for the post:
gauravnnl (Thu Jan 29, 2015 10:21 am)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post