MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Cumulative sum and Select Records

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
dhansr
Beginner


Joined: 26 May 2016
Posts: 14
Topics: 6
PostPosted: Thu May 26, 2016 2:38 pm    Post subject: Cumulative sum and Select Records Reply with quote
Hi,

My file has number count at position 66 with length 8 sorted in ascending order. File has more than 1000 records.

My requirement is to count this field and when the count reaches 50,000 i should stop. While counting 50,000 i should write the input file records to output file.

Please help me how to do this using sort in JCL.

Thanks.
shivaredk@gmail.com
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu May 26, 2016 3:38 pm    Post subject: Reply with quote
dhansr,

JCL by itself cannot do anything. It is the utilities or programs that can perform the tasks for you.

What is the LRECL and RECFM of the input file?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
dhansr
Beginner


Joined: 26 May 2016
Posts: 14
Topics: 6
PostPosted: Thu May 26, 2016 6:10 pm    Post subject: Reply with quote
Hi Kolusu,

Thanks for your response I am using the method which was suggested by you to another person. Below are the details.

Input file name is 120 LRECL and RECFM is FB, sorted in ascending order. (Original production file is of 101 LRECL to accommodate the cumulate sum of each record I am adding 19 bytes towards the end)

I have a 9 digit numeric number starting at position 46 as mentioned below. I need to add the cumulative of these numbers and when total reaches > 5000 (example) I want to write all the input files to output file and stop processing.

To achieve this I am doing is using the method suggested by you to add cumulative towards the end of the file and then in another sort step I am putting the condition for the newly added cumulative column to select records which totals upto less than 5000.

INCLUDE COND=(22,4,LT,5000),FORMAT=ZD

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
FILENAME11111111111111111111111111111111111 ;000000131;00000003333;AAAAA
FILENAME22222222222222222222222222222222222 ;000000242;00000004444;BBBBB
FILENAME33333333333333333333333333333333333 ;000002333;00000005555;CCCCC
FILENAME44444444444444444444444444444444444 ;000003454;00000006667;DDDDD
FILENAME55555555555555555555555555555555555 ;000004565;00000007774;EEEEE
**************************** Bottom of Data ****************************


What I am doing now:
Code:

//STEP020 EXEC PGM=SORT
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SORTIN   DD  DSN=TEST.IP.TESTFL01,DISP=SHR
//SORTOUT  DD  DSN=TEST.IP.TESTFL02,DISP=SHR
//SYSIN DD *
  SORT FIELDS=COPY
  OUTFIL REMOVECC,NODETAIL,
  SECTIONS=(1,1,
  TRAILER3=(1,101,SUBTOTAL=(46,9,ZD,EDIT=(IIIIIIT))))
/*

//

Problem I am facing is it is writing only one record with the total count in the output file.

but I am confused is if I change the location of numeric digit which I am adding like below
TRAILER3=(1,10,SUBTOTAL=(1,9,ZD,EDIT=(IIIIIIT))))

input file
Code:

VIEW      ,TEST.IP.FL01
Command ===>,
******,********************
000100,111111111
000200,222222222
000300,333333333
000400,444444444
******,********************

output file:
Code:

******,******************
000001,111111111 1111111
000002,222222222 3333333
000003,333333333 6666666
000004,444444444 1111110
******,******************

it is adding the cumulative at the end which I was expecting.

Please advise if I use the original file with position 46 and different numeric values why it is not writing all the cumulative records, in test files when I use 111111111, 222222222,333333333,444444444 values at position 1 it is working fine.

Even at position if I change the value from 111111111,222222222,333333333,444444444 to original values like below Command ===>,
Code:

******,************
000100,000004781
000200,000005442
000300,000006333
000400,000007444
******,************

it is not working, giving only one trial record with total.
Code:

Command ===>,
******,******************
000001,000007444   24000
******,******************

Thank You for your time.
Dhananjay.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu May 26, 2016 7:05 pm    Post subject: Reply with quote
dhansr,

I appreciate you spending time in searching and trying to tailor it to your needs. Good Job. You just missed one slight clue from my old trick. You got the right idea however you have a control break on the position 1 for a length of 1 and in your case position 1 for every record is value F. So the Sections and trailer3 is generating a single record with total count.

You need to have a control break on every record as you need a running total. So in order to do that you simply add a sequence number at the end and have a control break on it.


Here is the complete Job.
Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DISP=SHR,DSN=Your Input FB 101 Lrecl File                     
//SORTOUT  DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)     
//SYSIN    DD *                                               
  OPTION COPY                                                 
  INREC OVERLAY=(121:SEQNUM,8,ZD)                             
  OUTFIL REMOVECC,NODETAIL,BUILD=(120X),                       
  SECTIONS=(121,8,                                             
  TRAILER3=(1,101,SUBTOT=(46,9,UFF,M11,LENGTH=15)))           
//*
//STEP0200 EXEC PGM=SORT                 
//SYSOUT   DD SYSOUT=*                   
//SORTIN   DD DISP=SHR,DSN=&&T1         
//SORTOUT  DD SYSOUT=*                   
//SYSIN    DD *                         
  OPTION COPY                           
  INCLUDE COND=(102,15,ZD,LE,5000)       
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
dhansr
Beginner


Joined: 26 May 2016
Posts: 14
Topics: 6
PostPosted: Thu May 26, 2016 9:23 pm    Post subject: Reply with quote
Hi Kolusu,

Happy to hear good job from Master !

Code given by you is working like a magic, thank you so much.

When you have some time please advise what is the function of SEQNUM & SECTIONS.

Thanks again for your quick response, outstanding !!!

Thank You Thank You
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0
PostPosted: Fri May 27, 2016 4:17 am    Post subject: Reply with quote
Maybe some more research is the best way to go? Consult the manuals, starting with the Getting Started and then moving on to the Application Programming Guide.

Whilst going through, follow the examples and try some of your own.

If, after that, you haven't grasped it fully, explain the issue that you have.

You would find it would "stick" better that way.
Back to top
View user's profile Send private message
dhansr
Beginner


Joined: 26 May 2016
Posts: 14
Topics: 6
PostPosted: Fri May 27, 2016 10:32 am    Post subject: Reply with quote
Sure, Thanks.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri May 27, 2016 10:58 am    Post subject: Reply with quote
dhansr wrote:
When you have some time please advise what is the function of SEQNUM & SECTIONS.

Thanks again for your quick response, outstanding !!!


Dhansr,

As william pointed out, you will remember the code and make any future changes quite easily if you understood what it is doing rather than me explaining.

Steps to understand the job

Run the job multiple times to understand what it is doing. run it against your sample data of 5-10 records

first run
Code:

//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                               
  OPTION COPY   
  INREC OVERLAY=(121:SEQNUM,8,ZD)
//*


Now go and check the contents at position 121 and see how each record is numbered at the end.

A control break occurs when there is a change in the value of one of the keys.

Now we are having a control break on the sequence number that we just added. Since it is changing for every record we write out a summary record.

Step 2 : Add the outfil statement with sections. But see how I took off NODETAIL and BUILD=(120X).

Code:

//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                               
  OPTION COPY   
  INREC OVERLAY=(121:SEQNUM,8,ZD)   
  OUTFIL REMOVECC,                       
  SECTIONS=(121,8,                                             
  TRAILER3=(1,101,SUBTOT=(46,9,UFF,M11,LENGTH=15)))           
//*


Now you will see the original record (lack of NODETAIL) and another record with summary total at position 102. Since I removed the BUILD=(120X) you will also see the Sequence number that we added at the end.

By running control cards one at a time you can actually see how DFSORT process the control cards.

Just once you understand the job then it is quite easy to change or apply similar logic to other requirements that might come up.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group