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

Calculate the count of records between one minute time frame


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

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Thu Jul 01, 2010 2:00 pm
Reply with quote

I have transactions in file as below

intime trans type
22.23.12.9 pay
22.23.20.5 pay
23.24.12.6 pay
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 ATM
23.25.22.9 pay
23.25.22.9 pay
23.26.22.9 ATM
23.26.22.9 CHQ
23.26.22.9 CHQ
23.26.22.9 CHQ
23.26.22.9 CHQ
23.28.22.9 CHQ
The file is comma delimited variable block file .for understanding the requirement i have shown in formatted way as above
Format of in-time: HH:MM:SS:ms



There are millions of records in file .

Requirement:

What I need to do is
Calculate number of transaction logged in one minute time frame for each payment type

for e.g. start time in file is 22.23 and before it switched to 22.24 i.e for first 1 min
it has below result

start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
1 2 0 0

now for next one min it has below transaction
start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
2 1 0 0

again for next one minute it has belwo transactions

start of time no of trans of Pay-type no of trans of ATM type no of trans of CHQ type
3 02 10 0

now see the last transaction its time is 23.28 and befor it there is no entry for 23.27
that means there is no transaction at 23.27 so it should be added as zero as belwo
start of time no of trans of Paytype no of trans of ATM type no of trans of CHQ type
3 0 0 0


finally my output file should look like below


start-of-time no-of-trans-of-Paytype no-of-trans- of-ATM-type no-of trans of CHQtype
1 2 0 0
2 1 0 0
3 2 10 0
4 0 1 4
5 0 0 0 (as 23.27 is missing)
6 0 0 1


Please help me with some solution using DFSORT or ICETOOL . I tried this with REXX but its taking lot of time to process.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Jul 01, 2010 9:52 pm
Reply with quote

pdighade,

Answer the following questions.

1. What is the LRECL and RECFM of the input file?
2. What is the position of the TIME field?
3. What is the position of the type field?
4. Why is the start of time a 1 byte seqnum? How are you going to relate it to the actual times?
5. You want a default value for missing records. How do you map which time records are missing.

Ideally I would have generated the report as follows

Code:

TIME   PAY   ATM   CHQ 
       TRAN  TRAN  TRAN
=====  ====  ====  ====
22:23     2     0     0
23:24     1     0     0
23:25     2    10     0
23:26     0     1     4
23:28     0     0     1
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Fri Jul 02, 2010 8:52 am
Reply with quote

Hi ,

Answers for your questions

1. What is the LRECL and RECFM of the input file?
Ans:file is with LRECL =1028 & RECFM=VB
2. What is the position of the TIME field?
Ans: Time field is the first field of file so it will start form position 5 th
as it is VB file
3. What is the position of the type field?
Ans : Payment type field is start on 170th position and is of length 3
4. Why is the start of time a 1 byte seqnum? How are you going to relate it to the actual times?
Ans:Actually from the report I need to generate graph for time against no of transaction of pay types. Ideally this file is having 24 hrs of data. say from 00.01 to 23.59 . I just take first time in file whatever it may be say 00.01 so my next minute will be 00.02 and so on.. and i want to capture no of transactions in between this time slot .
5. You want a default value for missing records. How do you map which time records are missing.
Yes for missing records i want default value as zero for all payment types
In report shown by you there is no entry for 23.27 . Simple way to map is from starting time period in ur case 22.23 ,keep adding 1 min to it and find for transaction with that period if not found then default zero to that period

I am ok with below report as well

TIME PAY ATM CHQ
TRAN TRAN TRAN
===== ==== ==== ====
22:23 2 0 0
23:24 1 0 0
23:25 2 10 0
23:26 0 1 4
23:28 0 0 1

just if it have one entry for 23.27 .i.e missing slot as below
TIME PAY ATM CHQ
TRAN TRAN TRAN
===== ==== ==== ====
23:27 0 0 0
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Sat Jul 03, 2010 1:14 am
Reply with quote

I do have tendency of posting bad or inefficient solution but here is the solution which I could come up with. When tested, it gave me correct output...

Since, I couldn't come up logic to add records where there is no payment posted, I am creating a master file with just HH:MM value for a day and then matching that with your payment file from first step. For demo, my input master file contains only few records.

However, I tested the job with instream input data, so I may be off by 1 or 2 bytes in some places. Feel free to correct me.


Code:
//SORT00 EXEC  PGM=ICETOOL                                             
//DFSMSG   DD SYSOUT=*                                                 
//TOOLMSG  DD SYSOUT=*                                                 
//IN       DD DISP=SHR,DSN=YOUR.INPUT.VB                                                 
//T1       DD DSN=&&T1,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//T2       DD DSN=&&T2,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//OUT      DD  SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 COPY FROM(IN) TO(T1) USING(CTL1)                                       
 SPLICE FROM(T1) TO(T2) ON(01,5,CH) -                                   
 WITH(61,8) WITH(71,8) KEEPNODUPS USING(CTL2)                           
/*                                                                     
//CTL1CNTL DD *                                                         
 INREC OVERLAY=(1:5,5,174,3,31:5,5,174,3,100:X)                         
 SORT FIELDS=(31,08,CH,A)                                               
 OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(41:SEQNUM,08,ZD,RESTART=(31,8)))     
  OUTFIL REMOVECC,NODETAIL,                                             
  SECTIONS=(1,5,6,3,                                                   
            TRAILER3=(1,50))                                           
/*                                                                     
//CTL2CNTL DD *                                                         
  INREC IFTHEN=(WHEN=(06,03,CH,EQ,C'ATM'),OVERLAY=(51:41,08)),         
        IFTHEN=(WHEN=(06,03,CH,EQ,C'CHQ'),OVERLAY=(61:41,08)),         
        IFTHEN=(WHEN=(06,03,CH,EQ,C'PAY'),OVERLAY=(71:41,08))           
/*                                                                     
//*                                                                     
//SORT01   EXEC  PGM=SORT                                               
//SYSOUT DD SYSOUT=*                                                   
//SORTJNF1 DD DSN=&T2,VOL=REF=*.SORT00.T2,DISP=OLD                     
//SORTJNF2 DD *                                                         
23.21                                                                   
23.22                                                                   
23.23                                                                   
23.24                                                                   
23.25                                                                   
23.26                                                                   
23.27                                                                   
23.28                                                                   
23.29
23.30
/*                                                                     
//SORTOUT DD SYSOUT=*                                                   
//SYSIN   DD *                                                         
  JOINKEYS FILE=F1,FIELDS=(01,05,A)                                     
  JOINKEYS FILE=F2,FIELDS=(01,05,A)                                     
  JOIN UNPAIRED,F2                                                     
  REFORMAT FIELDS=(F1:01,78,                                           
                   F2:01,05),FILL=X'40'                                 
  INREC IFTHEN=(WHEN=(79,05,CH,GT,C'     '),OVERLAY=(1:79,05)),         
        IFTHEN=(WHEN=(51,08,CH,EQ,C'        '),OVERLAY=(51:8C'0')),     
        IFTHEN=(WHEN=(61,08,CH,EQ,C'        '),OVERLAY=(61:8C'0')),     
        IFTHEN=(WHEN=(71,08,CH,EQ,C'        '),OVERLAY=(71:8C'0'))     
  SORT FIELDS=(1,5,CH,A)                                               
   OUTFIL REMOVECC,                                                     
      HEADER2=(001:'TIME      ',                                       
               011:'       PAY',                                       
               021:'       ATM',                                       
               031:'       CHQ',/,                                     
               001:'          ',                                       
               011:'      TRAN',                                       
               021:'      TRAN',                                       
               031:'      TRAN',/,                                     
               001:'=====     ',                                       
               011:'      ====',                                       
               021:'      ====',                                       
               031:'      ===='),                                       
        BUILD=(001:01,05,5X,                                           
               011:51,08,ZD,EDIT=(IIIIIIIT),2X,                         
               021:61,08,ZD,EDIT=(IIIIIIIT),2X,                         
               031:71,08,ZD,EDIT=(IIIIIIIT),2X)                         
/*                                                                     
//*                                                                     



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

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Sat Jul 03, 2010 10:02 am
Reply with quote

Thanks

Let me try this . I will get back to you with result
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Sat Jul 03, 2010 3:00 pm
Reply with quote

one thing i paytype filed's position is getting changed for few records

So instead of below
INREC IFTHEN=(WHEN=(06,03,CH,EQ,C'ATM'),OVERLAY=(51:41,08)),
IFTHEN=(WHEN=(06,03,CH,EQ,C'CHQ'),OVERLAY=(61:41,08)),
IFTHEN=(WHEN=(06,03,CH,EQ,C'PAY'),OVERLAY=(71:41,08))

can I use SS (scan) option as below along with "when"?

INREC IFTHEN=(WHEN=(1,1028,SS,EQ,C'ATM'),OVERLAY=(51:41,08)),
IFTHEN=(WHEN=(1,1028,SS,EQ,C'CHQ'),OVERLAY=(61:41,08)),
IFTHEN=(WHEN=(1,1028,SS,EQ,C'PAY'),OVERLAY=(71:41,08))

1-starting postion
1028-record length of file

Anyways I will try and Let you know
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Sun Jul 04, 2010 7:59 am
Reply with quote

Quote:
can I use SS (scan) option as below along with "when"?
Have you tried it?
What were your results?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun Jul 04, 2010 9:19 am
Reply with quote

Quote:
Anyways I will try and Let you know


Quote:
Have you tried it?
What were your results?
Ahhh. . . Anticipation icon_smile.gif
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Sun Jul 04, 2010 9:48 am
Reply with quote

Gave a little thought to your problem while driving back to home from dinner. The problem lies in my sort card given earlier.

Try this...

Code:
//SORT00 EXEC  PGM=ICETOOL                                             
//DFSMSG   DD SYSOUT=*                                                 
//TOOLMSG  DD SYSOUT=*                                                 
//IN       DD DSN=TSOID.TEST.REPORT.VB1040,DISP=SHR                   
//T1       DD DSN=&&T1,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//T2       DD DSN=&&T2,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//TOOLIN   DD *                                                         
 COPY FROM(IN) TO(T1) USING(CTL1)                                       
 SPLICE FROM(T1) TO(T2) ON(5,5,CH) -                                   
 WITH(31,8) WITH(41,8) WITHEACH KEEPNODUPS USING(CTL2)                 
/*                                                                     
//CTL1CNTL DD *                                                         
 INREC OVERLAY=(1041:5,5,174,03)                                   
 SORT FIELDS=(05,05,CH,A,174,03,CH,A)                                   
 OUTREC IFTHEN=(WHEN=INIT,                                             
                OVERLAY=(1051:SEQNUM,08,ZD,RESTART=(1041,8)))           
  OUTFIL REMOVECC,NODETAIL,                                             
  SECTIONS=(5,5,174,3,                                                 
            TRAILER3=(5,5,174,3,1051,8))                               
/*                                                                     
//CTL2CNTL DD *                                                         
  INREC IFTHEN=(WHEN=(10,03,CH,EQ,C'CHQ'),OVERLAY=(31:13,08)),         
        IFTHEN=(WHEN=(10,03,CH,EQ,C'PAY'),OVERLAY=(41:13,08)),         
        IFTHEN=(WHEN=(10,03,CH,EQ,C'ATM'),OVERLAY=(51:13,08))           
/*                                                                     
//*                                                                     
//SORT01   EXEC  PGM=SORT                                               
//SYSOUT DD SYSOUT=*                                                   
//SORTJNF1 DD DSN=&T2,VOL=REF=*.SORT00.T2,DISP=OLD                     
//SORTJNF2 DD *                                                         
23.20                                                                   
23.21                                                                   
23.22                                                                   
23.23                                                                   
23.24                                                                   
23.25                                                                   
23.26                                                                   
23.27                                                                   
23.28                                                                   
23.29                                                                   
23.30                                                                   
/*                                                                     
//SORTOUT DD SYSOUT=*                                                   
//SYSIN   DD *                                                         
  JOINKEYS FILE=F1,FIELDS=(05,05,A),TYPE=V                             
  JOINKEYS FILE=F2,FIELDS=(01,05,A)                                     
  JOIN UNPAIRED,F2                                                     
  REFORMAT FIELDS=(F1:05,05,10,03,31,08,41,08,51,08,                   
                   F2:01,05),FILL=X'00'                                 
  INREC IFTHEN=(WHEN=(09,08,CH,EQ,C'        '),OVERLAY=(09:8C'0')),     
        IFTHEN=(WHEN=(17,08,CH,EQ,C'        '),OVERLAY=(17:8C'0')),     
        IFTHEN=(WHEN=(25,08,CH,EQ,C'        '),OVERLAY=(25:8C'0')),     
        IFTHEN=(WHEN=(33,05,CH,GT,C'     '),OVERLAY=(1:33,05))         
  SORT FIELDS=(1,5,CH,A)                                               
   OUTFIL REMOVECC,                                                     
      HEADER2=(001:'TIME      ',                                       
               011:'       CHQ',                                       
               021:'       PAY',                                       
               031:'       ATM',/,                                     
               001:'          ',                                       
               011:'      TRAN',                                       
               021:'      TRAN',                                       
               031:'      TRAN',/,                                     
               001:'=====     ',                                       
               011:'      ====',                                       
               021:'      ====',                                       
               031:'      ===='),                                       
        BUILD=(001:01,05,5X,                                           
               011:09,08,ZD,EDIT=(IIIIIIIT),2X,                         
               021:17,08,ZD,EDIT=(IIIIIIIT),2X,                         
               031:25,08,ZD,EDIT=(IIIIIIIT),2X)                         
/*                                                                     
//*                                                                     


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

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Mon Jul 05, 2010 7:24 pm
Reply with quote

Sorry Guys for late reply as I was not able to try this over week end

I tried first step sort00 and there is small issue .Below is the details of the issue along with my code


Code:
//TOOLIN   DD *                                               
 COPY FROM(IN) TO(T1) USING(CTL1)                             
 SPLICE FROM(T1) TO(T2) ON(34,5,CH) -                         
 WITH(31,8) WITH(41,8) WITHEACH KEEPNODUPS USING(CTL2)       
/*                                                           
//CTL1CNTL DD *                                               
  INREC IFTHEN=(WHEN=(5,150,SS,EQ,C'ATM'),                   
               OVERLAY=(1041:34,5,1046:7C'ATM    ')),         
        IFTHEN=(WHEN=(5,150,SS,EQ,C'CHQ'),                   
               OVERLAY=(1041:22,5,1046:7C'CHQ    ')),         
        IFTHEN=(WHEN=(5,150,SS,EQ,C'PAYMENT'),               
               OVERLAY=(1041:22,5,1046:7C'PAYMENT'))         
 SORT FIELDS=(13,8,CH,A,34,05,CH,A,137,15,CH,A)               
 OUTREC IFTHEN=(WHEN=INIT,                                   
                OVERLAY=(1061:SEQNUM,08,ZD,RESTART=(1041,12)))
  OUTFIL REMOVECC,NODETAIL,                                   
  SECTIONS=(1041,5,1046,07,                                   
            TRAILER3=(1041,5,1046,07,1061,8))                 
/*                                                           
//CTL2CNTL DD *                                                   
  INREC IFTHEN=(WHEN=(10,07,CH,EQ,C'ATM    '),OVERLAY=(31:17,08)),
        IFTHEN=(WHEN=(10,07,CH,EQ,C'CHQ    '),OVERLAY=(41:17,08)),
        IFTHEN=(WHEN=(10,07,CH,EQ,C'PAYMENT'),OVERLAY=(51:17,08)) 
/*                                                                 



Just to avoid confusion

Time fileld is starting from position 34 and of lenght 5


Code:
--3----
** Top
3,22:55
4,22:55
4,22:55
0,22:55
5,22:56


TRANS TYPE FIELD IS STARTING FROM POSITION 137 AND MAX CAN OF 15 CHAR
Code:
----+----4----+-
****************
-,XX-IN-ATM-03 
-,YYY-OUT-CHQ   
-,YYY-OUT-CHQ   
-,XX-IN-ATM-79 
-,XX-IN-PAYMENT-43 

As my Transaction type filed is not of fixed length i have inserted above logic . It is giving correct result for T1 file
but for T2 file which splits the transaction of different type with their count is not working

IN PREVIOUS TOPIC I FORGET TO MENTION NAME OF ONE THE TRANSACTION TYPE IS PAYMENT INSTAED OF PAY .i.e all transaction types are not of same length
one is of 7 char and rest all are of 3 charcters

Below is the result of t1 file


Code:
----+----1----+----2-
*********************
22:55ATM    00000002
22:55CHQ    00000002
22:56PAYMENT00000001

below is the result for t2


Code:
***************************** Top of Data ***
22:55CHQ    00000002      00000002
22:55ATM    00000002      00000002           

There is missing "payment" transaction type record in t2 file .This is the issue

If possible please suggest me chages for next step sort01 for formatting the result
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Mon Jul 05, 2010 7:39 pm
Reply with quote

There are 4 sapces after ATM keyword but not getting dispayed in previous reply due to some issue

OVERLAY=(1041:34,5,1046:7C'ATM ')

same issue for t1

result for t1 there are 4 blank spaces
after ATM and CHQ keyword but not getting displayed in previous reply
Below is result of t1

Code:
----+----1----+----2-
*********************
22:55ATM    00000002
22:55CHQ    00000002
22:56PAYMENT00000001

Just to let you know that issue is not because of this i have posted above things again.
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Tue Jul 06, 2010 12:42 pm
Reply with quote

Please help me with the above issue
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Tue Jul 06, 2010 7:58 pm
Reply with quote

See if this works for you...

I would still seek final approval from Frank or Kolusu to make sure this is efficient method.


Code:
//SORT00 EXEC  PGM=ICETOOL                                             
//DFSMSG   DD SYSOUT=*                                                 
//TOOLMSG  DD SYSOUT=*                                                 
//IN       DD DSN=TSOID.TEST.REPORT.VB10401,DISP=SHR                 
//T1       DD DSN=&&T1,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//T2       DD DSN=&&T2,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)               
//TOOLIN   DD *                                                         
 COPY FROM(IN) TO(T1) USING(CTL1)                                       
 SPLICE FROM(T1) TO(T2) ON(5,5,CH) -                                   
 WITH(31,8) WITH(41,8) WITHEACH KEEPNODUPS USING(CTL2)                 
/*                                                                     
//CTL1CNTL DD *                                                         
 INREC IFTHEN=(WHEN=(137,15,SS,EQ,C'ATM'),                             
                OVERLAY=(1041:34,5,1046:C'ATM    ')),                   
       IFTHEN=(WHEN=(137,15,SS,EQ,C'CHQ'),                             
                OVERLAY=(1041:34,5,1046:C'CHQ    ')),                   
       IFTHEN=(WHEN=(137,15,SS,EQ,C'PAYMENT'),                         
                OVERLAY=(1041:34,5,1046:C'PAYMENT'))                   
 SORT FIELDS=(1041,12,CH,A)                                             
 OUTREC IFTHEN=(WHEN=INIT,                                             
                OVERLAY=(1061:SEQNUM,08,ZD,RESTART=(1041,12)))         
  OUTFIL REMOVECC,NODETAIL,                                             
  SECTIONS=(1041,12,                                                   
            TRAILER3=(1041,12,1061,08))                                 
/*                                                                     
//CTL2CNTL DD *                                                         
  INREC IFTHEN=(WHEN=(10,07,CH,EQ,C'CHQ    '),OVERLAY=(31:17,08)),     
        IFTHEN=(WHEN=(10,07,CH,EQ,C'PAYMENT'),OVERLAY=(41:17,08)),     
        IFTHEN=(WHEN=(10,07,CH,EQ,C'ATM    '),OVERLAY=(51:17,08))       
/*                                                                     
//*                                                                     
//SORT01   EXEC  PGM=SORT                                               
//SYSOUT DD SYSOUT=*                                                   
//SORTJNF1 DD DSN=&T2,VOL=REF=*.SORT00.T2,DISP=OLD                     
//SORTJNF2 DD *                                                         
23.20                                                                   
23.21                                                                   
23.22                                                                   
23.23                                                                   
23.24                                                                   
23.25                                                                   
23.26                                                                   
23.27                                                                   
23.28                                                                   
23.29                                                                   
23.30                                                                   
/*                                                                     
//SORTOUT DD SYSOUT=*                                                   
//SYSIN   DD *                                                         
  JOINKEYS FILE=F1,FIELDS=(05,05,A)                                     
  JOINKEYS FILE=F2,FIELDS=(01,05,A)                                     
  JOIN UNPAIRED,F2                                                     
  REFORMAT FIELDS=(F2:01,05,                                           
                   F1:31,08,41,08,51,08)                               
  SORT FIELDS=(1,5,CH,A)                                               
   OUTFIL REMOVECC,                                                     
      HEADER2=(001:'TIME      ',                                       
               011:'       CHQ',                                       
               021:'       PAY',                                       
               031:'       ATM',/,                                     
               001:'          ',                                       
               011:'      TRAN',                                       
               021:'      TRAN',                                       
               031:'      TRAN',/,                                     
               001:'=====     ',                                       
               011:'      ====',                                       
               021:'      ====',                                       
               031:'      ===='),                                       
        BUILD=(001:01,05,5X,                                           
               011:06,08,ZD,EDIT=(IIIIIIIT),2X,                         
               021:14,08,ZD,EDIT=(IIIIIIIT),2X,                         
               031:22,08,ZD,EDIT=(IIIIIIIT),2X)                         
/*                                                                     
//*                                                                     


OUTPUT

Code:

TIME             CHQ       PAY       ATM
                TRAN      TRAN      TRAN
=====           ====      ====      ====
23.20            0         0         0   
23.21            0         0         0   
23.22            0         0         0   
23.23            0         2         0   
23.24            0         1         0   
23.25            1         1        12   
23.26            4         0         1   
23.27            0         0         0   
23.28            1         0         0   
23.29            0         0         0   
23.30            0         0         0   


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

Senior Member


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

PostPosted: Tue Jul 06, 2010 11:29 pm
Reply with quote

pdighade,

Based on the following assumptions (all positions are assumed accounting the RDW)

1. Time fileld is starting from position 34 and of lenght 5
2. TRANS TYPE FIELD IS STARTING FROM POSITION 137 AND MAX CAN OF 15 CHAR

The following DFSORT JCL will give you the desired results for a 24 hour span in minute range from 00.00 to 23:59.

Code:

//****************************************************************
//* GENERATES 1440 RECORDS WITH TIME FROM 00.00 TO 23.59         *
//****************************************************************
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                   
A                                                                 
//SORTOUT  DD DSN=&&DF,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)       
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                               
  OUTFIL REPEAT=1440,FTOV,VLTRIM=C' ',                           
  IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,START=0)),           
  IFTHEN=(WHEN=INIT,OVERLAY=(30:81,8,ZD,DIV,+60,M11,LENGTH=2,     
          C'.',81,8,ZD,MOD,+60,M11,LENGTH=2,1020:X))             
//*                                                               
//****************************************************************     
//* GENERATE SUMMARY REPORT FOR TRANSACTIONS 'PAY' 'ATM' 'CHQ'   *     
//****************************************************************     
//STEP0200 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=your input VB file,DISP=SHR
//         DD DSN=&&DF,DISP=SHR                                       
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,                                             
  PARSE=(%=(ENDBEFR=C'ATM',ENDBEFR=C'CHQ',ENDBEFR=C'PAY',ABSPOS=137), 
        %1=(SUBPOS=3,FIXLEN=3)),                                       
  BUILD=(1,4,34,5,X,%1,X,3C'00000000 ')),                             
  IFTHEN=(WHEN=(11,3,CH,EQ,C'PAY'),OVERLAY=(22:C'1')),                 
  IFTHEN=(WHEN=(11,3,CH,EQ,C'ATM'),OVERLAY=(31:C'1')),                 
  IFTHEN=(WHEN=(11,3,CH,EQ,C'CHQ'),OVERLAY=(40:C'1'))                 
                                                                       
  SORT FIELDS=(5,5,CH,A),EQUALS                                       
  SUM FIELDS=(15,8,ZD,24,8,ZD,33,8,ZD)                                 
                                                                       
  OUTFIL VTOF,                                                         
  BUILD=(03:5,5,                                                       
         10:15,8,ZD,M10,LENGTH=8,                                     
         20:24,8,ZD,M10,LENGTH=8,                                     
         30:33,8,ZD,M10,LENGTH=8,                                     
         80:X),                                                       
  HEADER2=('  TIME ',2X,'PAY TRAN',2X,'ATM TRAN',2X,'CHQ TRAN',/,     
           ' FRAME ',2X,'  COUNT ',2X,'  COUNT ',2X,'  COUNT ',/,     
           '=======',2X,'========',2X,'========',2X,'========')       
//*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Jul 07, 2010 12:43 am
Reply with quote

Kolusu,

I have a question from my attempt of this question for RESTART on multiple keys

1) Is there a way in DFSort, we can perform RESTART for multiple keys?

For example, I have FIELD1 through FIELD5 and I need to add SEQNUM RESTART logic based on FIELD1 and FIELD3. Currently, I am moving these fields to the end of the record and then performing RESTART. Is there a better/efficient way?

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

Senior Member


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

PostPosted: Wed Jul 07, 2010 1:03 am
Reply with quote

sqlcode1 wrote:
Kolusu,

I have a question from my attempt of this question for RESTART on multiple keys

1) Is there a way in DFSort, we can perform RESTART for multiple keys?

For example, I have FIELD1 through FIELD5 and I need to add SEQNUM RESTART logic based on FIELD1 and FIELD3. Currently, I am moving these fields to the end of the record and then performing RESTART. Is there a better/efficient way?

Thanks,


The Restart parm needs the fields to be consecutive. So you need to move the fields F1 and F3 together and then use the RESTART parm. Just so you know you the Restart logic will NOT work in this case as there can be multiple breaks in data. For ex if the times are like this.
Code:

23:01
23:03
23:04
23:06
23:09
23:11

23:22


When coding a solution think of ALL the possible ways that you can break that solution and then you will come up with an ideal solution.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Jul 07, 2010 2:24 am
Reply with quote

Kolusu,

Thanks for the explanation.

Quote:
Just so you know you the Restart logic will NOT work in this case as there can be multiple breaks in data. For ex if the times are like this.


Code:
INREC IFTHEN=(WHEN=(137,15,SS,EQ,C'ATM'),                             
                OVERLAY=(1041:34,5,1046:C'ATM    ')),                   
       IFTHEN=(WHEN=(137,15,SS,EQ,C'CHQ'),                             
                OVERLAY=(1041:34,5,1046:C'CHQ    ')),                   
       IFTHEN=(WHEN=(137,15,SS,EQ,C'PAYMENT'),                         
                OVERLAY=(1041:34,5,1046:C'PAYMENT'))                   
 SORT FIELDS=(1041,12,CH,A)                                             
 OUTREC IFTHEN=(WHEN=INIT,                                             
                OVERLAY=(1061:SEQNUM,08,ZD,RESTART=(1041,12)))


I ran this job with all possible time values and it worked. Since OP wanted report at the minute level. In my input I had records for no transaction,1 transaction,2 transactions and all 3 transactions. Here my intention was to break the Sequencing whenever payment type changes.

From your example if we have all ATM transaction for below time examples, it would have 1 in SEQNUM for all of them.

hh:mm
23:01 ATM
23:03 ATM
23:04 ATM
23:06 ATM
23:09 ATM
23:11 ATM
23:22 ATM


now, if we have multiple transaction for different payment type in a minute, it would still assign seqnum of 1 to all the records,which when used with TRAILER3 would give us COUNT of total occurences.

hh:mm
23:01 ATM
23:01 CHQ
23:04 ATM
23:04 CHQ

I apologize if my understanding is still wrong or I am asking asking too much but I really didn't get the part where this RESTART logic wouldn't work.


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

Senior Member


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

PostPosted: Wed Jul 07, 2010 3:25 am
Reply with quote

sqlcode1 wrote:
I apologize if my understanding is still wrong or I am asking asking too much but I really didn't get the part where this RESTART logic wouldn't work.


sqlcode1,

*sigh* If your intention of using the RESTART parm is to count the occurrences of each type within minute timeframe , then you really don't need it in the first place because trailer3 can automatically get the count of occurrence for each type using COUNT parm

Secondly you are assuming that you can ONLY have 1 ATM or 1 PAY or 1 CHQ transaction within a minute time frame which is wrong. what happens if the data is like this

Code:

hh mm ss type
23:01:03 ATM
23:01:06 PAY
23:01:09 ATM
23:01:12 CHQ
23:01:15 ATM
23:01:25 CHQ
23:01:35 PAY
23:01:45 PAY


Now your restart parm will create

Code:

hh mm ss type seqnum
23:01:03 ATM  00000001
23:01:15 ATM  00000002
23:01:09 ATM  00000003
23:01:12 CHQ  00000001
23:01:25 CHQ  00000002
23:01:35 PAY  00000001
23:01:45 PAY  00000002
23:01:06 PAY  00000003


Now if you use that as a count your counts are clearly off and you would get wrong results.

Please take time to understand the problem and then you can solve the problem.
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Wed Jul 07, 2010 8:24 pm
Reply with quote

Hi Skolusu,

I tried with your code

Input file is having below sample data


22:55 CHQ
22:56 ATM

It is showing the result but entry for time is getting repeated as below

22.55 0 0 0
22.56 0 0 0

22.57 0 0 0
22.58 0 0 0
22.59 0 0 0
22:55 0 1 0
22:56 1 0 0

23.00 0 0 0
23.01 0 0 0
23.02 0 0 0
23.03 0 0 0
23.04 0 0 0

and second thing is ( There is slight change in requirement . The change is file may contain data of previous day's transaction as well. and we need to consider these transactions also in our calculation. The file may contain few transactions of previous day as it depends upon time at with transaction report is generated . We can identify transactions of particular date by date fields in file which is 6 char long(yyyymmdd) and start at position 13th so logic in first step may not work here. I am really apologizes for inconvenience caused due to changed requirement . This will be my final requirement. Can you please help me with this?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Jul 07, 2010 9:27 pm
Reply with quote

pdighade wrote:
Hi Skolusu,

I tried with your code

Input file is having below sample data


22:55 CHQ
22:56 ATM

It is showing the result but entry for time is getting repeated as below


pdighade,

If you have copied the control cards I provided as IS , you will NEVER get the duplicated rows. You need to show me what you changed. Please don't claim that the solution provided gave you wrong results. If you claim you are getting the wrong results, I want to see the control cards you used and your COMPLETE sysout of the job.

Quote:

and second thing is ( There is slight change in requirement . The change is file may contain data of previous day's transaction as well. and we need to consider these transactions also in our calculation. The file may contain few transactions of previous day as it depends upon time at with transaction report is generated . We can identify transactions of particular date by date fields in file which is 6 char long(yyyymmdd) and start at position 13th so logic in first step may not work here. I am really apologizes for inconvenience caused due to changed requirement . This will be my final requirement. Can you please help me with this?


What am I supposed to do with the previous day transactions? Generate another 1440 records for previous day also? what is the expected output? I can't read your mind nor have access to your data. Your date field is 6 character long and how did you fit a 8 byte format in it?
Back to top
View user's profile Send private message
pdighade

New User


Joined: 21 Jul 2008
Posts: 40
Location: pune

PostPosted: Thu Jul 08, 2010 8:47 pm
Reply with quote

Hi Skolusu,

Job is working fine and now giving expected result
Actually I did mistake in below line

C'.',81,8,ZD,MOD,+60,M11,LENGTH=2,1020:X))

As my input file is having date in format with ':' in between HR:MM
I have replaced c'.' with c':' and its working.

Answers for your questions.

What am I supposed to do with the previous day transactions?
Ans:For all the transactions of previous day, all the resultant rows of time and count should come just before start of whatever date next to it.

Generate another 1440 records for previous day also?
Ans: No of transactions of previous day to be captured for perticular time slot depends upon our requirement .Whenever requied we set some parameter and capture transaction of previous day for perticular time slot along with
current days trans. One more thing the time slot of previous day is always in continuation with current day.

e.g previous day's 22:00 PM to 24:00 hrs of current date

So whaever trans of previous day. the count should just come before start of date next to it

Note: The current date is not always system date. we can extract transaction of any date and the date just previous to it. But the limit is
max two days data we can extract and We are generally never extracting previous day's complete data.

Your date field is 6 character
long and how did you fit a 8 byte format in it?

Ans:This is mistake from my side. The data is having 8 characters.
and start position is 13th and format is yyyymmdd

So generating 1440 records for previous day may not work here as file may not have complete day's trans. No of records to be generated depends upon time slot captured of previous day.

Can you please help me on this?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Jul 09, 2010 5:16 am
Reply with quote

pdighade wrote:
So whaever trans of previous day. the count should just come before start of date next to it


pdighade,

The following DFSORT JCL will give you the desired results. The report now shows as date field next to the time. The report will generate the time report from the beginning time frame of any date and generate any default values for the missing time slots till 23:59 of each day.


Code:

//****************************************************************
//* GENERATES 1440 RECORDS WITH TIME FROM 00:00 TO 23:59         *
//****************************************************************
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                   
A                                                                 
//SORTOUT  DD DSN=&&DF,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)       
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                               
  OUTFIL REPEAT=1440,FTOV,VLTRIM=C' ',                           
  IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,START=0)),           
  IFTHEN=(WHEN=INIT,OVERLAY=(30:81,8,ZD,DIV,+60,M11,LENGTH=2,     
          C':',81,8,ZD,MOD,+60,M11,LENGTH=2,1020:X))             
//*                                                   
//****************************************************************     
//* GENERATE SUMMARY REPORT FOR TRANSACTIONS 'PAY' 'ATM' 'CHQ'   *     
//****************************************************************     
//STEP0200 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=your input VB file,DISP=SHR
//         DD DSN=&&DF,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT 
//         DD DSN=&&DF,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT 
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=INIT,                                             
  PARSE=(%=(ENDBEFR=C'ATM',ENDBEFR=C'CHQ',ENDBEFR=C'PAY',ABSPOS=137),   
        %1=(SUBPOS=3,FIXLEN=3)),                                       
  BUILD=(1,4,34,5,X,13,8,X,%1,X,3C'00000000 ',SEQNUM,8,ZD)),           
  IFTHEN=(WHEN=GROUP,BEGIN=(51,8,ZD,EQ,1),PUSH=(60:11,8)),             
  IFTHEN=(WHEN=GROUP,BEGIN=((60,8,CH,NE,11,8,CH),AND,11,8,CH,GT,C' '), 
  PUSH=(69:11,8)),                                                     
  IFTHEN=(WHEN=(20,3,CH,EQ,C'PAY'),OVERLAY=(31:C'1')),                 
  IFTHEN=(WHEN=(20,3,CH,EQ,C'ATM'),OVERLAY=(40:C'1')),                 
  IFTHEN=(WHEN=(20,3,CH,EQ,C'CHQ'),OVERLAY=(49:C'1')),                 
  IFTHEN=(WHEN=(11,8,CH,EQ,C' '),OVERLAY=(78:SEQNUM,4,ZD),HIT=NEXT),   
  IFTHEN=(WHEN=(78,4,ZD,LE,1440),OVERLAY=(11:60,8)),                   
  IFTHEN=(WHEN=(78,4,ZD,GT,1440),OVERLAY=(11:69,8))                     
                                                                       
  SORT FIELDS=(11,8,CH,A,5,5,CH,A),EQUALS                               
  SUM FIELDS=(24,8,ZD,33,8,ZD,42,8,ZD)                                 
                                                                       
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(20,3,CH,GT,C' '),PUSH=(20:20,3))     
                                                                       
  OUTFIL VTOF,OMIT=(20,3,CH,EQ,C' '),                                   
  BUILD=(03:5,5,                                                       
         10:11,8,ZD,EDIT=(TTTT/TT/TT),                                 
         22:24,8,ZD,M10,LENGTH=8,                                       
         32:33,8,ZD,M10,LENGTH=8,                                       
         42:42,8,ZD,M10,LENGTH=8,                                       
         80:X),                                                         
  HEADER2=('  TIME ',2X,'  TRANS ',4X,                                 
           'PAY TRAN',2X,'ATM TRAN',2X,'CHQ TRAN',/,                   
           ' FRAME ',2X,'   DATE ',4X,                                 
           '   COUNT ',2X,'  COUNT ',2X,'  COUNT ',/,                   
           '=======',2X,'==========',2X,                               
           '========',2X,'========',2X,'========')                     
//*


The output of this job would be as follows

Code:

  TIME     TRANS     PAY TRAN  ATM TRAN  CHQ TRAN
 FRAME      DATE        COUNT     COUNT     COUNT
=======  ==========  ========  ========  ========
  20:23  2010/07/04         0         1         0
  20:24  2010/07/04         0         0         0
  20:25  2010/07/04         0         1         0
  20:26  2010/07/04         0         0         0
...
  23:58  2010/07/04         0         0         0 
  23:59  2010/07/04         0         0         2 
  00:00  2010/07/05         0         0         0 
  00:01  2010/07/05         0         4         0 
....
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 To get the count of rows for every 1 ... DB2 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
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts To get the the current time DFSORT/ICETOOL 13
Search our Forums:

Back to Top