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
 

 

Need help in selecting Input file data

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

New User


Joined: 06 May 2008
Posts: 90
Location: Chennai

PostPosted: Wed Sep 01, 2010 4:43 pm    Post subject: Need help in selecting Input file data
Reply with quote

I have Input files as

Code:

G123456K J0086475  2:25:33 2010.243
G123456K J0086445 12:25:33 2010.243
G123452K J0086475 12:25:33 2010.243
G123452K J0086445  2:25:33 2010.243
G123451K J0086425  2:25:33 2010.243


I want ot modify my output in this format.

Code:

G123456K J0086445 12:25:33 2010.243
G123452K J0086475 12:25:33 2010.243
G123451K J0086425  2:25:33 2010.243


Output will be sorted on basis of
1. JOBNAME (If we have two enteries for JOBNAME, then it will delete one entry from that)
2. The entry which is going to be deleted will be on basis of field
from 19-20, that row will be retained which will have entry higher )

Eg

G123456K J0086475 2:25:33 2010.243
G123456K J0086445 12:25:33 2010.243

from above two rows , it will retain second row only. Since it has 12(Clumn 19-20) which is higher than 2 in first row.


Can someone help
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Sep 01, 2010 5:28 pm    Post subject:
Reply with quote

And the saga continues.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 01, 2010 7:42 pm    Post subject:
Reply with quote

scorp_rahul23,
Assuming 80 byte input file, see if below jcl works for you...
Code:

//SORT01   EXEC  PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                               
 SELECT FROM(IN) TO(OUT) ON(01,08,CH) FIRST USING(CTL1)       
/*                                                             
//IN      DD *                                                 
G123456K J0086475  2:25:33 2010.243                           
G123456K J0086445 12:25:33 2010.243                           
G123452K J0086475 12:25:33 2010.243                           
G123452K J0086445  2:25:33 2010.243                           
G123451K J0086425  2:25:33 2010.243                           
/*                                                             
//CTL1CNTL DD *                                               
  INREC OVERLAY=(81:1,8,19,8,UFF,M11,LENGTH=6)                         
  SORT FIELDS=(81,8,CH,D,89,6,CH,D),EQUALS                     
  OUTFIL BUILD=(1,80)                                         
/*                                                             
//*                                                           

OUTPUT
Code:

G123456K J0086445 12:25:33 2010.243
G123452K J0086475 12:25:33 2010.243
G123451K J0086425  2:25:33 2010.243


Alternatively you may also try below...

Code:

//SORT01   EXEC PGM=SORT                                               
//SORTIN   DD  *                                                       
G123456K J0086475  2:25:33 2010.243                                   
G123456K J0086445 12:25:33 2010.243                                   
G123452K J0086475 12:25:33 2010.243                                   
G123452K J0086445  2:25:33 2010.243                                   
G123451K J0086425  2:25:33 2010.243                                   
/*                                                                     
//SORTOUT  DD  SYSOUT=*                                               
//SYSIN DD *                                                           
  INREC OVERLAY=(81:1,8,19,8,UFF,M11,LENGTH=6)                         
  SORT FIELDS=(81,8,CH,D,88,6,CH,D),EQUALS                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(94:SEQNUM,8,ZD,RESTART=(1,8)))   
  OUTFIL INCLUDE=(94,8,ZD,EQ,1),BUILD=(1,80)                           
/*                                                                     
//SYSOUT DD SYSOUT=*                                                   
//*                                                                   


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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Sep 01, 2010 9:34 pm    Post subject:
Reply with quote

scorp_rahul23 wrote:

G123456K J0086475 2:25:33 2010.243
G123456K J0086445 12:25:33 2010.243

from above two rows , it will retain second row only. Since it has 12(Clumn 19-20) which is higher than 2 in first row.


scorp_rahul23,

What would you do if the job names span more than a day
Code:

G123456K J0086475  2:25:33 2010.243
G123456K J0086445 12:25:33 2010.243
G123456K J0086445 23:24:25 2010.242


Here the last row has a prior date(2010.242) and the time is 11:24 PM . Now if you sort these 3 records in descending order of the time , you would get the prior date record which is not what I think you want.

So you need to consider the date also. Considering the above conditions here is a DFSORT/ICETOOL job which will give you the desired results.
Code:

//STEP0100 EXEC  PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//IN       DD *                                         
G123456K J0086475  2:25:33 2010.243                     
G123456K J0086445 12:25:33 2010.243                     
G123456K J0086445 23:24:25 2010.242                     
G123452K J0086475 12:25:33 2010.243                     
G123452K J0086445  2:25:33 2010.243                     
G123451K J0086425  2:25:33 2010.243                     
//OUT      DD SYSOUT=*                                 
//TOOLIN   DD *                                         
 SELECT FROM(IN) TO(OUT) ON(01,08,CH) FIRST USING(CTL1)
//CTL1CNTL DD *                                         
  SORT FIELDS=(1,8,CH,A,28,8,CH,D,19,8,UFF,D)           
//*


Sqlcode1,

You don't have to reformat the time column , you can use UFF format to sort it
You don't need EQUALS with Select operator , as Equals is the default for SELECT operator.
I am not sure as to why you decided to sort on the jobname descending.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 01, 2010 10:07 pm    Post subject:
Reply with quote

Skolusu,
Quote:
You don't need EQUALS with Select operator , as Equals is the default for SELECT operator.
Understand EQUALS is default for select but shouldn't I preserve original order of the job in case of duplicates for the job name? For example, If input data is as shown below, using EQUALS, I am trying to select first record with job id J0086475 irrespective of the date field. I think you have this already covered in your post but since OP didn't mention anything regarding date part (2010.243,2010.242...),I assumed he would want to keep first record irrespective of date. Isn't it true that SELECT gets output of CNTL1 and then applies all the default options?
Code:

G123456K J0086475  2:25:33 2010.243
G123456K J0086474  2:25:33 2010.242
G123456K J0086476  2:25:33 2010.244

Quote:
I am not sure as to why you decided to sort on the jobname descending.

This was because OP said, he wanted output sorted on Job (didn't mention ASC or DESC) and his expected output,though in original order, shows jobid descending. Had he wanted ASC his expected output would be different.
Quote:
Output will be sorted on basis of
1. JOBNAME (If we have two enteries for JOBNAME, then it will delete one entry from that)

OUTPUT from the job you provided is as below, which shows G123451K as the first record but in OP's expected output shows G123451K as the last one. Please correct me If I am wrong.
I won't be surprised if he comes back and says he doesn't really care for Job name order.
Code:
G123451K J0086425  2:25:33 2010.243
G123452K J0086475 12:25:33 2010.243
G123456K J0086445 12:25:33 2010.243

Quote:
You don't have to reformat the time column , you can use UFF format to sort it
My mistake here. Don't know why I decided to reformat record. Thanks for the correction.

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Sep 01, 2010 11:16 pm    Post subject: Reply to: Need help in selecting Input file data
Reply with quote

Quote:

Understand EQUALS is default for select but shouldn't I preserve original order of the job in case of duplicates for the job name?


sqlcode1,

When you use SELECT operator, it passes EQUALS as one of the parm for SORTING. Look at the ICE146I message and read all the parameters that are passed. So you coding EQUALS on CTL1CNTL is not necessary.

Quote:

Isn't it true that SELECT gets output of CNTL1 and then applies all the default options?


I am not even sure what that meant.
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 2 Fri Mar 24, 2017 3:25 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 4 Fri Mar 24, 2017 11:51 am
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm


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