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

Selecting records comparing with previous record


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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: 1742
Location: Tirupur, India

PostPosted: Thu Dec 08, 2011 12:11 pm
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top