Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to fetch the latest record from a file.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How to fetch the latest record from a file.
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

Active User


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

PostPosted: Tue Jun 02, 2020 6:08 pm    Post subject:
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

Active Member


Joined: 29 Apr 2008
Posts: 825
Location: Maryland

PostPosted: Tue Jun 02, 2020 8:31 pm    Post subject: Re: How to fetch the latest record from a file.
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: 2408
Location: Hampshire, UK

PostPosted: Tue Jun 02, 2020 8:55 pm    Post subject: Reply to: How to fetch the latest record from a file.
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: 2467
Location: NY,USA

PostPosted: Wed Jun 03, 2020 10:13 am    Post subject:
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

Active User


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

PostPosted: Wed Jun 03, 2020 10:42 am    Post subject:
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: 2467
Location: NY,USA

PostPosted: Wed Jun 03, 2020 9:14 pm    Post subject:
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

Active User


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

PostPosted: Thu Jun 04, 2020 5:15 am    Post subject:
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: 2467
Location: NY,USA

PostPosted: Thu Jun 04, 2020 6:32 am    Post subject:
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    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 TWS - ETT File triggering does not wo... me00266 IBM Tools 4 Mon Sep 07, 2020 11:58 pm
No new posts How to process dependent file based o... HithaSri JCL & VSAM 8 Sun Sep 06, 2020 12:42 pm
No new posts EIBRESP =16 & EIBRESP2 = 01 while... Farheen CICS 4 Mon Aug 24, 2020 2:05 pm
No new posts Abend deletes last record written julietspringsteen ABENDS & Debugging 10 Fri Aug 21, 2020 7:45 pm
No new posts Dynamic file read via JCL abdulrafi JCL & VSAM 4 Fri Aug 14, 2020 11:51 am

Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us