IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to fetch the latest record from a file.


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Dinesh Mani

New User


Joined: 29 Nov 2019
Posts: 5
Location: India

PostPosted: Tue Jun 02, 2020 5:34 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Tue Jun 02, 2020 6:08 pm
Reply with quote

In z/OS there are only datasets.

What have you tried so far? Please use code tags when presenting code/data.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Tue Jun 02, 2020 8:31 pm
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 02, 2020 8:55 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Jun 03, 2020 10:13 am
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Wed Jun 03, 2020 10:42 am
Reply with quote

@Rohit: You will skip primary keys that may have an identical timestamp as your solution only prints the first occurance.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Jun 03, 2020 9:14 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Thu Jun 04, 2020 5:15 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jun 04, 2020 6:32 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top