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
 

 

How to handle different key for SORT and SUM ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Sep 09, 2009 3:03 pm    Post subject: How to handle different key for SORT and SUM ?
Reply with quote

I have a input file of length 20 characters.
My sort conditions are,
1. Sort Key positions are : 2:3,20:1,5:12.
2. Omit the records which has certain values in the keys.

I achieved the above mentioned two points using the following step,

Code:
//DFSORT00 EXEC PGM=SORT 
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD DISP=SHR,DSN=OSPASO01
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *       
  OMIT COND=(2,15,CH,EQ,C'016010008213704',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'035010008214921',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'036010008215057',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'040010008214883',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'085010008215111',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'116010008214964',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'117010008214980',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'130010008214956',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'145010008215030',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'156010008215049',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'170010008215103',AND,20,1,CH,C'1',OR,
        2,15,CH,EQ,C'185010008215022',AND,20,1,CH,C'1')
  SORT FIELDS=(2,3,CH,A,20,1,CH,A,5,12,CH,A)


3. Now I also want to do one more level of filtering.
In the sorted output, If any continuous records have duplicate value for the key - (2:15,CH), then I want only the first record to get qualified.

Please find an example below,

Current Sorted Output & the desired record
Code:

11111111111111111111      Yes
11111111111111111111      No
11111111111111111113      No
11112222222222222223      Yes
11111111111111111114      Yes
11111111111111111115      No
11112222222222222225      Yes
11113333333333333336      Yes


Performing this in two steps is a easy option. But I am just curious to merge this along with the above mentioned step. And handle two different keys in a single step. Can anybody help me on this ??
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 Sep 09, 2009 9:43 pm    Post subject:
Reply with quote

I don't see how the input records you show match up with your SORT fields. But assuming you want to SORT on 2,3/20,1/5,12 in one case and you want to SORT on 2/15 in the other case:

You can do it in a single "step" using ICETOOL with two SORT operators.
But you can't do it in a single "pass" over the input file if that's what you're asking.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Sep 10, 2009 12:34 pm    Post subject:
Reply with quote

Thanks for your reply Frank.

First of all I would like to clarify that I meant to ask for a single pass over the input file as you have specified.

Next, about the example shown and the input file, I am not sure where I was unclear about my SORT fields. Because below is the step that I used to test my case.

Code:
//DFSORT00 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                   
11113333333333333336                                             
11112222222222222225                                             
11111111111111111111                                             
11111111111111111115                                             
11112222222222222223                                             
11111111111111111111                                             
11111111111111111114                                             
11111111111111111113                                             
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                   
  OMIT COND=(2,15,CH,EQ,C'016010008213704',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'035010008214921',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'036010008215057',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'040010008214883',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'085010008215111',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'116010008214964',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'117010008214980',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'130010008214956',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'145010008215030',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'156010008215049',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'170010008215103',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'185010008215022',AND,20,1,CH,EQ,C'1')   
  SORT FIELDS=(2,3,CH,A,20,1,CH,A,5,12,CH,A)   


And the output was as I mentioned,

Code:
11111111111111111111
11111111111111111111
11111111111111111113
11112222222222222223
11111111111111111114
11111111111111111115
11112222222222222225
11113333333333333336


And yeah, as you have told this functionality is possible using ICETOOL and two operators in a single step. I achieved the same using the following JCL,

Code:
//DFSORT00 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//IN1      DD *                                             
11113333333333333336                                         
11112222222222222225                                         
11111111111111111111                                         
11111111111111111115                                         
11112222222222222223                                         
11111111111111111111                                         
11111111111111111114                                         
11111111111111111113                                         
//OUTPUT   DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  SORT FROM(IN1) TO(T1) USING(CTL1)                         
  COPY FROM(T1) TO(OUTPUT) USING(CTL2)                       
//CTL1CNTL DD *                                             
  OMIT COND=(2,15,CH,EQ,C'016010008213704',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'035010008214921',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'036010008215057',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'040010008214883',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'085010008215111',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'116010008214964',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'117010008214980',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'130010008214956',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'145010008215030',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'156010008215049',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'170010008215103',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'185010008215022',AND,20,1,CH,EQ,C'1')       
  SORT FIELDS=(2,3,CH,A,20,1,CH,A,5,12,CH,A)                     
  OUTFIL FNAMES=T1,BUILD=(1,16,SEQNUM,2,ZD,RESTART=(2,15),17,4)   
//CTL2CNTL DD *                                                   
  INCLUDE COND=(17,2,ZD,EQ,1)                                     
  OUTFIL FNAMES=OUTPUT,BUILD=(1,16,19,4)     


The output I obtained was as follows,

Code:
11111111111111111111
11112222222222222223
11111111111111111114
11112222222222222225
11113333333333333336


With that being said, I would like to clarify on why I asked for a single pass. I am actually trying to get this done for some elapsed time reduction. As having two passes will have an increased EXCP count and higher elapsed time compared to doing it in a single pass, I asked if there is a possibility of getting it done.

I would like to request your help in having a look at my ICETOOL step to see if it is the efficient (from elapsed time point of view) way of having my requirement done. Or is there a even better way of doing it ?
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 Sep 10, 2009 8:49 pm    Post subject:
Reply with quote

I'm not sure what you're trying to accomplish here. You started off by saying you want to do two different sorts, but your job only does one sort.

Quote:
RESTART=(2,15)


This will only work if the records are already sorted on 2,15 when you do the OUTFIL. If they are, then you don't need the second sort you mentioned or a second pass. If the records aren't already sorted on 2,15 when you do the OUTFIL, then this won't work.

If this works for you, then you don't need two passes. You can do the RESTART with an OUTREC statement and use an OUTFIL statement with INCLUDE and BUILD instead of a second pass.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Sep 10, 2009 9:14 pm    Post subject:
Reply with quote

Frank,

Am sorry if I have mislead you with any of my wrong terminologies.

Quote:
Now I also want to do one more level of filtering.
In the sorted output, If any continuous records have duplicate value for the key - (2:15,CH), then I want only the first record to get qualified.


But from the above point, I just meant to say that if any successive records after the SORT has same values in the field (2,15) i want the records other than the first record to be omitted. And for this reason, I donot want the records to be sorted on (2,15).

I hope I was a bit more clear this time.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Sep 10, 2009 9:15 pm    Post subject:
Reply with quote

And of course, I achieved it using the following step,

Code:
//DFSORT00 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)     
//IN1      DD *                                                   
11113333333333333336                                             
11112222222222222225                                             
11111111111111111111                                             
11111111111111111115                                             
11112222222222222223                                             
11111111111111111111                                             
11111111111111111114                                             
11111111111111111113                                             
//OUTPUT   DD SYSOUT=*                                           
//TOOLIN   DD *                                                   
  SORT FROM(IN1) TO(OUTPUT) USING(CTL1)                           
//CTL1CNTL DD *                                                   
  OMIT COND=(2,15,CH,EQ,C'016010008213704',AND,20,1,CH,EQ,C'1',OR,
        2,15,CH,EQ,C'035010008214921',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'036010008215057',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'040010008214883',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'085010008215111',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'116010008214964',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'117010008214980',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'130010008214956',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'145010008215030',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'156010008215049',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'170010008215103',AND,20,1,CH,EQ,C'1',OR,     
        2,15,CH,EQ,C'185010008215022',AND,20,1,CH,EQ,C'1')       
  SORT FIELDS=(2,3,CH,A,20,1,CH,A,5,12,CH,A)                     
  OUTREC FIELDS=(1,16,SEQNUM,2,ZD,RESTART=(2,15),17,4)           
  OUTFIL FNAMES=OUTPUT,BUILD=(1,16,19,4),INCLUDE=(17,2,ZD,EQ,1)   


Thanks for your help Frank..
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm


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