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
 

 

Group Data based on a key

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

New User


Joined: 23 Sep 2006
Posts: 24
Location: India

PostPosted: Thu Apr 27, 2017 11:29 pm    Post subject: Group Data based on a key
Reply with quote

Hi,

I am trying to group data based on some key and display as a unique row summarizing the data. Please find the input data as below

Code:

Input:
App-Name  Jobname   Start_Date_Time   End_Date_Time     Status
App1      J6235SR   20170427-060001   20170427-060001   Complete
App1      J4021AB   20170427-060001   20170427-063000   Complete
App1      J0021R4   20170427-063001   20170427-064000   Complete
App1      J2521BG   20170427-064000   20170427-064500   Complete
App1      J3422TY   20170427-064500   20170427-065000   Complete
App2      J6235SR   20170427-060001   20170427-060001   Complete
App2      J4021AB   20170427-060001   20170427-063000   Complete
App2      J0021R4   20170427-063001   20170427-064000   ERROR
App2      J2521BG                                       WAITING
App2      J2521BG                                       WAITING
App3      J6235SR   20170427-060001   20170427-060001   Complete
App3      J4021AB   20170427-060001   20170427-063000   Complete
App3      J0021R4   20170427-063001   20170427-064000   Complete
App3      J2521BG   20170427-064000                     Started
App3      J2521BG                                       WAITING
App4      J6235SR   20170427-060001   20170427-060001   Complete
App4      J4021AB   20170427-060001   20170427-063000   ERROR
App4      J0021R4   20170427-063001   20170427-064000   Complete
App4      J2521BG   20170427-064000                     Started
App4      J2521BG                                       WAITING


Input file Lrecl is 200 and FB

The output that we are trying to extract is as

Code:

Output:
App-Name  Start_Date_Time    End_Date_Time     Status
App1      20170427-060001    20170427-065000   Complete
App2      20170427-060001    20170427-064000   Error
App3      20170427-060001                      In-Progress
App4      20170427-060001    20170427-063000   Error


Can anyone help us with this requirement. I tried using

OUTREC IFTHEN=(WHEN=GROUP,                          
               BEGIN=

But couldnt come to a proper sort card. Please guide us here.

Thanks,
Dinesh
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Thu Apr 27, 2017 11:52 pm    Post subject:
Reply with quote

All you got to do is explain the requirement fully. You did not tell us which row to pick and how. Did you do research? There are plenty of similar stuff available on this forum.
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 106
Location: Maryland

PostPosted: Fri Apr 28, 2017 12:41 am    Post subject:
Reply with quote

The term "summarize" used in your question cannot be applied to the data sample from your column STATUS.

How are you going to "summarize" completely different character strings?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri Apr 28, 2017 10:07 am    Post subject: Reply to: Group Data based on a key
Reply with quote

For each group within "App" you want the earliest start-date, the latest end-date, and staus will be Complete if all are Complete, Errror if at least one is in error, otherwise In-Progress.

Talk to your Scheduler people. They can probably report this.

If you have to continue with this yourself, bear in mind that your task is not as easy as your sample-data makes out. It doesn't have to be the last job in the group which finishes last - or, if it sorted that way, unless the first job in each App is a "trigger" job (it will always start first and end first) you won't be able to pick the earliest easily.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Fri Apr 28, 2017 10:47 am    Post subject:
Reply with quote

mrdinesh,

The requirement is pretty much well explained in your example. Good job.

What if all the entries for an application are 'WAITING'? What status do you want to see for that application in that case?
Will that still be 'In-Progress' with start/end times as blanks?

If yes, you could use something like this :
Code:
//SYSIN DD *                                                 
 INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,21,15,39,15,57,8)),       
       IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,4),PUSH=(43:05,15)),   
       IFTHEN=(WHEN=(35,8,CH,EQ,C'ERROR'),                   
               OVERLAY=(58:C'1Error')),                       
       IFTHEN=(WHEN=(35,8,CH,EQ,C'WAITING'),                 
               OVERLAY=(58:C'2In-Progress')),                 
       IFTHEN=(WHEN=(35,8,CH,EQ,C'Started'),                 
               OVERLAY=(58:C'3In-Progress')),                 
       IFTHEN=(WHEN=(35,8,CH,EQ,C'Complete'),                 
               OVERLAY=(58:C'4Complete'))                     
 SORT FIELDS=(1,4,CH,A,58,1,CH,A,20,15,CH,D)                 
 OUTFIL REMOVECC,NODETAIL,BUILD=(80X),SECTIONS=(1,4,                     
        HEADER3=(01:1,4,11:43,15,30:20,15,48:59,11)),         
        HEADER1=('App-Name  Start_Date_Time    ',             
                 'End_Date_Time     Status')                 

Assumption : Output RECFM=FB/LRECL=80 and the input to be in sorted order of App-Name, Start_Date_Time .

Added the below lines to your test-input,
Code:
App5      J2588BG   20170427-060001                     Started
App5      J2588BG                                       WAITING
App6      J2599BG                                       WAITING
App6      J2599BG                                       WAITING

SORTOUT had:
Code:
App-Name  Start_Date_Time    End_Date_Time     Status     
App1      20170427-060001    20170427-065000   Complete   
App2      20170427-060001    20170427-064000   Error     
App3      20170427-060001                      In-Progress
App4      20170427-060001    20170427-063000   Error     
App5      20170427-060001                      In-Progress
App6                                           In-Progress
Ideally if all the jobs within an application are waiting, I would have expected the Appln status to be 'Waiting' instead of 'In-Progress'.
This can be slightly tweaked if that is what you want to show in the output.


EDIT : My bad, again kept the page open and missed Bill's response.
Back to top
View user's profile Send private message
mrdinesh

New User


Joined: 23 Sep 2006
Posts: 24
Location: India

PostPosted: Wed May 03, 2017 12:48 pm    Post subject:
Reply with quote

Arun Raj wrote:
mrdinesh,

The requirement is pretty much well explained in your example. Good job.

What if all the entries for an application are 'WAITING'? What status do you want to see for that application in that case?
Will that still be 'In-Progress' with start/end times as blanks?

If yes, you could use something like this :
Code:
//SYSIN DD *                                                 
 INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,21,15,39,15,57,8)),       
       IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,4),PUSH=(43:05,15)),   
       IFTHEN=(WHEN=(35,8,CH,EQ,C'ERROR'),                   
               OVERLAY=(58:C'1Error')),                       
       IFTHEN=(WHEN=(35,8,CH,EQ,C'WAITING'),                 
               OVERLAY=(58:C'2In-Progress')),                 
       IFTHEN=(WHEN=(35,8,CH,EQ,C'Started'),                 
               OVERLAY=(58:C'3In-Progress')),                 
       IFTHEN=(WHEN=(35,8,CH,EQ,C'Complete'),                 
               OVERLAY=(58:C'4Complete'))                     
 SORT FIELDS=(1,4,CH,A,58,1,CH,A,20,15,CH,D)                 
 OUTFIL REMOVECC,NODETAIL,BUILD=(80X),SECTIONS=(1,4,                     
        HEADER3=(01:1,4,11:43,15,30:20,15,48:59,11)),         
        HEADER1=('App-Name  Start_Date_Time    ',             
                 'End_Date_Time     Status')                 

Assumption : Output RECFM=FB/LRECL=80 and the input to be in sorted order of App-Name, Start_Date_Time .

Added the below lines to your test-input,
Code:
App5      J2588BG   20170427-060001                     Started
App5      J2588BG                                       WAITING
App6      J2599BG                                       WAITING
App6      J2599BG                                       WAITING

SORTOUT had:
Code:
App-Name  Start_Date_Time    End_Date_Time     Status     
App1      20170427-060001    20170427-065000   Complete   
App2      20170427-060001    20170427-064000   Error     
App3      20170427-060001                      In-Progress
App4      20170427-060001    20170427-063000   Error     
App5      20170427-060001                      In-Progress
App6                                           In-Progress
Ideally if all the jobs within an application are waiting, I would have expected the Appln status to be 'Waiting' instead of 'In-Progress'.
This can be slightly tweaked if that is what you want to show in the output.


EDIT : My bad, again kept the page open and missed Bill's response.


Thanks Arun Raj, the sort card worked well for us.
Back to top
View user's profile Send private message
mrdinesh

New User


Joined: 23 Sep 2006
Posts: 24
Location: India

PostPosted: Wed May 03, 2017 12:51 pm    Post subject: Re: Reply to: Group Data based on a key
Reply with quote

Bill Woodger wrote:
For each group within "App" you want the earliest start-date, the latest end-date, and staus will be Complete if all are Complete, Errror if at least one is in error, otherwise In-Progress.

Talk to your Scheduler people. They can probably report this.

If you have to continue with this yourself, bear in mind that your task is not as easy as your sample-data makes out. It doesn't have to be the last job in the group which finishes last - or, if it sorted that way, unless the first job in each App is a "trigger" job (it will always start first and end first) you won't be able to pick the earliest easily.


Thanks Bill. In the Long term we are going to get a file from our scheduler people. For now we are going with a temporary solution.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Wed May 03, 2017 6:21 pm    Post subject:
Reply with quote

mrdinesh - You're welcome. Good to know you have a long term solution planned as well.
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 copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am


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