View previous topic :: View next topic
|
Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
It's funny but the post doesn't have any question to be answered ;) |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
boyti ko
New User
Joined: 03 Nov 2014 Posts: 78 Location: Malaysia
|
|
|
|
Hi Rohit,
It's OPTION EQUALS that preserve arrangement, not the OPTION COPY. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Code Tags
code tags
USE THEM! |
|
Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
I am using VDI to access mainframe, so difficult to copy paste the code tags... |
|
Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
|
|
|
|
Quote: |
ICE063A 1 OPEN ERROR SYSIN
|
You are executing SORT not ICETOOL and you have given ICETOOL ddnames in the JCL.
Garry. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
All Right, Thanks for the clarity.
EQUALS isn't useful here. |
|
Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
Use this please,
Code: |
//CTL1CNTL DD *
SORT FIELDS=(1,6,CH,D,35,2,CH,A,8,26,CH,D) |
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
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 |
|
|
|