|
View previous topic :: View next topic
|
| Author |
Message |
Dinesh Mani
New User
Joined: 29 Nov 2019
Posts: 5
Location: India
|
|
|
|
| I have a file with a primary key and timestamp field. I want to fetch only the records of primary key with the latest time stamp. How to achieve this using sort. |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1338
Location: Bamberg, Germany
|
|
|
|
In z/OS there are only datasets.
What have you tried so far? Please use code tags when presenting code/data. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2145
Location: USA
|
|
|
|
| Dinesh Mani wrote: |
| I have a file with a primary key and timestamp field. I want to fetch only the records of primary key with the latest time stamp. How to achieve this using sort. |
Your topic is "How to fetch the latest record from a file" (Singular!)
Your question is: "I want to fetch only the records of primary key with the latest time stamp" (Plural!)
Please decide, what you actually need?
P.S. And what you've tried so far??? |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
Are you actually trying to access a db2 table using DFSort and not DB2? Or have you posted in the wrong section or do you have an unloaded dataset?
If the last then show sample data along with the DFSort control statements that you have tried. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
Going forward, for any problem please mention the input data, expected output and rules. Most importantly what you tried to get to that.
See if this helps you - since you provided nothing, use your Primary key and timestamp offsets accordingly.
I assume you need to get a primary record with max timestamp when they occur more than once.
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//STEP01 EXEC PGM=SORT
//SORTLIST DD SYSOUT=*
//SORTIN DD *
12345 2020-03-06-12:06:02
12345 2020-03-06-12:06:03
12336 2020-03-06-12:06:06
12336 2020-03-06-12:06:07
12336 2020-03-06-12:06:01
12336 2020-03-06-12:06:03
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,5,CH,A,9,19,CH,D)
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5)))
OUTFILE BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,00000001) |
Output
| Code: |
********************************* TOP OF DATA **********************************
12336 2020-03-06-12:06:07
12345 2020-03-06-12:06:03
******************************** BOTTOM OF DATA ******************************** |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1338
Location: Bamberg, Germany
|
|
|
|
| @Rohit: You will skip primary keys that may have an identical timestamp as your solution only prints the first occurance. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Joerg.Findeisen wrote: |
| @Rohit: You will skip primary keys that may have an identical timestamp as your solution only prints the first occurance. |
Joerg, It will work as expected. Why will it skip or bring dups for the primary keys ? Any sample input that you think is a fail case would help to understand? |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1338
Location: Bamberg, Germany
|
|
|
|
| Code: |
; Key ccyy-mm-dd-hh.mm.ss
;---+----1----+----2----+----3----+----4----+----5
0123456789 2020-06-03-12.06.02
1234567890 2020-06-03-01.02.03
1234567890 2020-06-02-12.12.12
1234567890 2020-06-03-01.02.03
2345678901 2020-06-02-12.06.03 |
I would expect the following:
| Code: |
0123456789 2020-06-03-12.06.02
1234567890 2020-06-03-01.02.03 <* listed twice, because same Timestamp
1234567890 2020-06-03-01.02.03 <* listed twice, because same Timestamp
2345678901 2020-06-02-12.06.03 |
I would have chosen a PUSH attempt for this. As we don't know the exact requirement, this is theoretical.
| Code: |
SORT FIELDS=(1,10,ZD,A,12,19,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(31:12,19))
OUTFIL FNAMES=(SORTOUT),
INCLUDE=(12,19,CH,EQ,31,19,CH),
BUILD=(1,10,X,12,19)
END |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
PUSH would do it too. If you rerun my card with the example you provided it will work too.
But let TS care to respond before we stretch ourselves so far 😊 |
|
| Back to top |
|
|
|
|
