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
 

 

Selecting records comparing with previous record

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

New User


Joined: 16 May 2005
Posts: 54

PostPosted: Thu Dec 08, 2011 10:33 am    Post subject: Selecting records comparing with previous record
Reply with quote

I have an input file of record length of 80 and Record format is fixed block. The layout is Jobname, Ran Date, ran time.
I want select the record where the jobname is unique OR if it is duplicate (on Jobname),
(i) the difference between the two run is 8 hours or less then select both the record and
(ii) if the diffrence is more than 8 hours then select the record which has greater ran date and time.

Suppose below is the input file
Code:
PBRD642D,2011/12/04,23:00:10
PBRD642D,2011/12/04,17:00:10
PBRD643D,2011/12/04,23:00:00
PBRD643D,2011/12/04,13:00:00
PBRD644D,2011/12/04,11:00:00
PBRD645D,2011/12/04,02:00:00
PBRD645D,2011/12/03,23:00:00


The output file should be as below

Code:
PBRD642D,2011/12/04,23:00:10 
PBRD642D,2011/12/04,17:00:10

Both the above records are selected as the difference between the two run time is less than 8 hours

Code:
PBRD643D,2011/12/04,23:00:00

This record is selected as the difference between the two run time is greater than 8 hours

Code:
PBRD644D,2011/12/04,11:00:00

This is the unique record.
Code:
PBRD645D,2011/12/04,02:00:00
PBRD645D,2011/12/03,23:00:00

Both the above records are selected as the difference between the two run time is less than 8 hours
Back to top
View user's profile Send private message

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1458
Location: Azeroth

PostPosted: Thu Dec 08, 2011 12:11 pm    Post subject:
Reply with quote

Hi,
What can be the maximum number of occurences of a individual unique job name?
Back to top
View user's profile Send private message
suzeet

New User


Joined: 16 May 2005
Posts: 54

PostPosted: Thu Dec 08, 2011 4:10 pm    Post subject:
Reply with quote

Maximum twice.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Dec 08, 2011 11:58 pm    Post subject:
Reply with quote

suzeet,

The following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//INA      DD DSN=your input FB 80 byte file,DISP=SHR
//INB      DD DSN=same input FB 80 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(1,8,A),SORTED,NOSEQCK                     
  JOINKEYS F2=INB,FIELDS=(1,8,A)                                     
  JOIN UNPAIRED                                                     
  REFORMAT FIELDS=(F1:1,80,F2:10,2)                                 

  INREC IFTHEN=(WHEN=INIT,                                           
         BUILD=(01,80,10,10,UFF,M11,LENGTH=8,X,                     
                21,2,ZD,MUL,+3600,ADD,                               
                24,2,ZD,MUL,+0060,ADD,                               
                27,2,ZD,M11,LENGTH=6,100X,81,2)),                   
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(97:SEQ=2,100:1,95)),       
  IFTHEN=(WHEN=INIT,                                                 
  OVERLAY=(199:180,8,Y4T,DATEDIFF,81,8,Y4T,X,                       
           ((199,8,UFF,MUL,+86400),ADD,189,6,ZD),SUB,               
             90,6,ZD,M11,LENGTH=6))                                 

  OUTFIL IFOUTLEN=80,                                               
  IFTHEN=(WHEN=(196,2,ZD,EQ,2),BUILD=(100,80,/,1,80)),               
  INCLUDE=((97,2,ZD,EQ,2,AND,196,2,ZD,EQ,2,AND,208,6,ZD,LE,28800),OR,
            196,2,ZD,EQ,1)                                           
//*
//JNF2CNTL DD *                                                     
  INREC BUILD=(1,8,X,C'01')                                         
  SUM FIELDS=(10,2,ZD)                                               
//*
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Dec 09, 2011 11:57 am    Post subject:
Reply with quote

Hi Skolusu,

Can you please explain the logic of the above sort card?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Dec 09, 2011 10:26 pm    Post subject:
Reply with quote

Gylbharat,

Hmm I thought my control cards are self explanatory.

The basic idea to check for a time difference is to convert the time format into seconds

HH:MM:SS = HH * 3600 + MM * 60 + SS = total time in seconds

So convert the time for every record into the desired format. However since we are dealing with jobs we need to care if the job ran the next day. ie. a job can start 11:30 PM and finish at 1 am the next day. if you just go with the time difference then it would be more than 8 hour (8* 3600 = 28,800 seconds) window. So we need to check the date difference too. If the date difference is greater than zero we need to add 86,400 seconds (24 hours = 24 * 3600) and then subtract the time in seconds from the next record.

We use keybegin to push the record on to the next record.

Since OP wanted both unique and duplicate job records, I used join keys operation to figure out how many records are there for each job name. So JNF2 will sum the records based on the jobname and build a joined record.

Using OUTFIL we check for 8 hour window records and unique records.


The best method to debug/learn is coding IFTHEN statement at a time and checking the results. If my memory serves right you are using syncsort , so you would need an additional pass to mimic the functionality of JNF2CNTL
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Dec 12, 2011 4:32 pm    Post subject:
Reply with quote

Thanks kolusu.... yes I am using Syncsort...
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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