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
 

 

SORT input file based on latest record

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

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Wed Dec 02, 2015 8:02 pm    Post subject: SORT input file based on latest record
Reply with quote

I have a input file which need to be sorted.
suppose I have data in input file in below format:
PO-NBR PO-TIMESTAMP SEQ-ID
099955 2015-09-16-00-00-00-000000 21
099955 2015-09-15-00-00-00-000000 21
099955 2015-09-11-00-00-00-000000 21
077777 2015-09-15-10-00-00-000000 25
077777 2015-09-19-05-00-00-000000 25
077777 2015-09-10-12-00-00-000000 25
077777 2015-09-10-15-00-00-000000 25

so my sorted output file should be like that:
PO-NBR PO-TIMESTAMP SEQ-ID
099955 2015-09-16-00-00-00-000000 21
077777 2015-09-19-05-00-00-000000 25

that means I want the latest record for the PO-NBR & SEQ-ID based on PO-TIMESTAMP.

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

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Wed Dec 02, 2015 9:07 pm    Post subject: Reply to: SORT input file based on latest record
Reply with quote

It's funny but the post doesn't have any question to be answered ;)
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Dec 02, 2015 9:16 pm    Post subject:
Reply with quote

Someone should give him a WARNING.
This has been his 3rd post on the same question since yesterday and the height is inspite of having it answered he is still posting the same question in wrong section( I guess or he changed his mind) and he is still denied to use a code tag or he doesn't know even after 7 years on this forum.
http://ibmmainframes.com/viewtopic.php?t=64603&highlight=
http://ibmmainframes.com/viewtopic.php?t=64602
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Dec 02, 2015 9:35 pm    Post subject:
Reply with quote

Anyways, This is should give you what you want. Now you have a DB2 and SORT both the solution, no more post on the same topic please.
Code:
//STEP0100 EXEC PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//IN       DD *                                       
099955 2015-09-16-00-00-00-000000 21                   
099955 2015-09-15-00-00-00-000000 21                   
099955 2015-09-11-00-00-00-000000 21                   
077777 2015-09-15-10-00-00-000000 25                   
077777 2015-09-19-05-00-00-000000 25                   
077777 2015-09-10-12-00-00-000000 25                   
077777 2015-09-10-15-00-00-000000 25                   
//OUT      DD SYSOUT=*                                 
//TOOLIN   DD *                                       
   SELECT FROM(IN) TO(OUT) ON(1,6,CH) FIRST USING(CTL1)
//CTL1CNTL DD *                                       
   OPTION COPY                                         
//*     
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Dec 02, 2015 10:51 pm    Post subject:
Reply with quote

Use this one with some more samples and added seq-id too which I did not add in my earlier post.
Code:
//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN       DD *                                                     
099955 2015-09-16-00-00-00-000000 21                                 
099955 2015-09-15-00-00-00-000000 21                                 
099955 2015-09-11-00-00-00-000000 21                                 
099955 2015-09-21-00-00-00-000000 22                                 
099955 2015-09-13-00-00-00-000000 22                                 
077777 2015-09-15-10-00-00-000000 25                                 
077777 2015-09-19-05-00-00-000000 25                                 
077777 2015-09-10-12-00-00-000000 25                                 
077777 2015-09-10-15-00-00-000000 25                                 
088888 2015-09-10-15-00-00-000000 26                                 
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                     
 SELECT FROM(IN) TO(OUT) ON(1,6,CH) ON(35,2,CH) FIRST USIN (CTL1) 
//CTL1CNTL DD *                                       
    OPTION COPY                                         
 //*


I think OPTION COPY saves the input order, please correct if not.
Back to top
View user's profile Send private message
boyti ko

New User


Joined: 03 Nov 2014
Posts: 78
Location: Malaysia

PostPosted: Thu Dec 03, 2015 7:19 am    Post subject:
Reply with quote

Hi Rohit,

It's OPTION EQUALS that preserve arrangement, not the OPTION COPY.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Dec 03, 2015 12:44 pm    Post subject: Reply to: SORT input file based on latest record
Reply with quote

Despite the topic title, a SORT is not wanted. Rohit has "turned off" the automatic sequencing which SELECT would do otherwise by using OPTION COPY. So the file is in the same order as the input, just less of it.

OPTION EQUALS preserves the order to the input sequence in the case of duplicate keys in a SORT or MERGE.
Back to top
View user's profile Send private message
maxsubrat

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Thu Dec 03, 2015 4:03 pm    Post subject: Reply to: SORT input file based on latest record
Reply with quote

I thought of using it thru DB2, but some other conflict issues with some data.
so after unloading data from DB2 table, then i can take the latest(max) record for each PO will be better.
But using the above SORT I am getting some error..
Here is the SORT:
================
//STEP03 EXEC PGM=SORT
//IN DD DSN=NA.ACS.TEST.INPUT1,
// DISP=SHR
//*
//OUT DD DSN=NA.ACS.TEST.INPUT1,
// DISP=OLD
//*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,6,CH) FIRST USING(CTL1)
//CTL1CNTL DD *
OPTION EQUALS
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD SYSOUT=*

GETTING THE BELOW ERROR
================
ICE802I 0 BLOCKSET TECHNIQUE IN CONTROL

ICE063A 1 OPEN ERROR SYSIN

ICE751I 0 C5-I21470 E7-I22412

ICE052I 3 END OF DFSORT
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Dec 03, 2015 5:29 pm    Post subject:
Reply with quote

Code Tags
Code:
code tags

code tags

USE THEM!
Back to top
View user's profile Send private message
maxsubrat

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Thu Dec 03, 2015 6:18 pm    Post subject: Reply to: SORT input file based on latest record
Reply with quote

I am using VDI to access mainframe, so difficult to copy paste the code tags...
Back to top
View user's profile Send private message
Garry Carroll

Active Member


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

PostPosted: Thu Dec 03, 2015 8:36 pm    Post subject:
Reply with quote

Quote:
ICE063A 1 OPEN ERROR SYSIN


You are executing SORT not ICETOOL and you have given ICETOOL ddnames in the JCL.

Garry. icon_evil.gif icon_rolleyes.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Dec 03, 2015 9:29 pm    Post subject:
Reply with quote

Bill , Thanks and boyti,I knew OPTION EQUALS preserves the order but wanted to check the if copy too does the same. Hence I choose to go with OPTION COPY

With the above input samples I get following output, So I question does EQUAL really preserved the order?
Code:
 099955 2015-09-16-00-00-00-000000 21                                 
 099955 2015-09-15-00-00-00-000000 21                                 
 099955 2015-09-11-00-00-00-000000 21                                 
 099955 2015-09-21-00-00-00-000000 22                                 
 099955 2015-09-13-00-00-00-000000 22                                 
 077777 2015-09-15-10-00-00-000000 25                                 
 077777 2015-09-19-05-00-00-000000 25                                 
 077777 2015-09-10-12-00-00-000000 25                                 
 077777 2015-09-10-15-00-00-000000 25                                 
 088888 2015-09-10-15-00-00-000000 26


Output:
1. OPTION COPY
Code:
099955 2015-09-16-00-00-00-000000 21
099955 2015-09-21-00-00-00-000000 22
077777 2015-09-15-10-00-00-000000 25
088888 2015-09-10-15-00-00-000000 26

2. OPTION EQUALS
Code:
077777 2015-09-15-10-00-00-000000 25
088888 2015-09-10-15-00-00-000000 26
099955 2015-09-16-00-00-00-000000 21
099955 2015-09-21-00-00-00-000000 22

3. When nothing is given, I believe OPTION EQUALS is chosen by default
Code:
077777 2015-09-15-10-00-00-000000 25
088888 2015-09-10-15-00-00-000000 26
099955 2015-09-16-00-00-00-000000 21
099955 2015-09-21-00-00-00-000000 22
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Dec 04, 2015 12:23 am    Post subject: Reply to: SORT input file based on latest record
Reply with quote

Quote:
EQUALS
Specifies whether the order of records that collate identically is preserved
from input to output.

Read: 'records that collate identically'. As Bill mentioned earlier Option Equals preserves the input sequence in the case of duplicate keys in a SORT or MERGE.

The whole set of records will be sorted based on the Control fields given. But wherever there are a identical/duplicate records (based on control fields), then the order of those records will be preserved by Option Equals (and not the whole set of records).

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Dec 04, 2015 3:18 am    Post subject:
Reply with quote

All Right, Thanks for the clarity.
EQUALS isn't useful here.
Back to top
View user's profile Send private message
maxsubrat

New User


Joined: 27 Feb 2008
Posts: 72
Location: india

PostPosted: Fri Dec 04, 2015 12:37 pm    Post subject: Reply to: SORT input file based on latest record
Reply with quote

Thanks all for the inputs.

Here one point to mention:

For the PO: 077777, it's not picking the latest(max) TS record.
it's picking the 1st record for the PO: 077777 2015-09-15-10-00-00-000000 25

but the latest(max) record for 077777 is:
077777 2015-09-19-05-00-00-000000 25

If the above SORT is based on the order(picking the 1st record), then I need to put the records in an DESC order before using the SORT..
Please correct me if I am wrong..

Thanks
Subrat
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Dec 04, 2015 10:02 pm    Post subject:
Reply with quote

Use this please,
Code:
//CTL1CNTL DD *                             
   SORT FIELDS=(1,6,CH,D,35,2,CH,A,8,26,CH,D)
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Dec 04, 2015 11:51 pm    Post subject:
Reply with quote

Also, you can use this too.
Code:
//STEP0100 EXEC PGM=SORT                         
//SYSOUT   DD SYSOUT=*                           
//SORTIN   DD *                                   
099911 2015-09-12-00-00-00-000000 21             
099911 2015-09-14-00-00-00-000000 21             
099911 2015-09-13-00-00-00-000000 21             
099912 2015-09-27-00-00-00-000000 22             
099912 2015-09-21-00-00-00-000000 22             
099912 2015-09-13-00-00-00-000000 22             
099913 2015-09-16-00-00-00-000000 23             
099913 2015-09-19-00-00-00-000000 23             
//SORTOUT  DD SYSOUT=*                           
//SYSIN    DD *                                   
  OPTION COPY                                     
  OUTFIL REMOVECC,NODETAIL,                       
  SECTIONS=(1,6,35,2,                             
     TRAILER3=(1,6,X,MAX=(8,26,UFF,               
       EDIT=(TTTT-TT-TT-TT-TT-TT-TTTTTT)),X,35,2))
//* 
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm


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