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
 

 

How to split the records using the amount field
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Fri Oct 28, 2016 7:33 pm    Post subject: How to split the records using the amount field
Reply with quote

Hi,

I have an amount field in a file and I want to split the records into multiple records 250000 each into one file until the total number of records reaches 10. Even after 10 splits if the amount is greater than zero then write the record into a separate file with the remaining amount.

For ex input file has below data.
Code:
12345 2550000

And I want the output file-1 like this
Code:
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000


Output file-2 like this
Code:
50000


Output file-2 should only be written when the amount field has value greater than zero even after 10 splits.

Please advise.

Thanks,
Ramana.

Code'd correctly
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Fri Oct 28, 2016 8:09 pm    Post subject:
Reply with quote

Is this a one-off for this particular record? If so you would be better off writing a simple program or even doing it manually. If not, will the value to be output on each split always be 250,000? If not, how is that calculated? Suppose you cannot get 10 records from splitting e.g. if your input record was:
Code:
12345   1250000

you would only get 5 splits and nothing in your second dataset (not file).
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Fri Oct 28, 2016 8:28 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Nic,

Yes the split limit is always 250,000. number of splits is based on the total amount. We will have to write to second file only if the amount is greater than 0 after 10 splits. If the amount exhausts after 5 splits, no need to write to second file. If the remaining amount after 10 splits is greater than 25000, we will only write one record for the total amount in the second file. I mean no splits required in the second file.

Thanks,
Ramana.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Oct 28, 2016 10:15 pm    Post subject:
Reply with quote

Here is one way of achieving this.
Code:
//STEP01   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
12345 2550000                                                       
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1))                 
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                             
  OUTFIL REPEAT=10,                                           
  IFTHEN=(WHEN=INIT,                                           
  OVERLAY=(15:C'250000',X,SEQNUM,7,ZD,START=250000,INCR=250000,
           22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,SEQNUM,2,ZD))                           
//STEP02   EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DSN=&T1,DISP=(SHR,PASS)                               
//OUT1     DD SYSOUT=*                                               
//OUT2     DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                               
  OUTFIL FNAMES=OUT1,INCLUDE=(22,7,ZD,GE,0),                     
         IFTHEN=(WHEN=(22,7,ZD,LT,15,6,ZD,AND,22,7,ZD,GT,0,AND, 
                       30,2,ZD,LT,10),                           
                 BUILD=(1,6,15,6,/,1,6,23,6)),                   
         IFTHEN=(WHEN=NONE,BUILD=(1,6,15,6))                     
  OUTFIL FNAMES=OUT2,INCLUDE=(22,7,ZD,GT,0,AND,30,2,ZD,EQ,10),   
                 BUILD=(22,7)                                     


EDIT : Apparently my tests were not exhaustive enough. Thanks Bill for notifying me, I have edited my cards above. This assumes original amount to be split >= the individual split amount (250,000 in this case)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Fri Oct 28, 2016 11:10 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

If you don't have a one-record file, please post representative sample input and output.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Mon Oct 31, 2016 10:32 am    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Bill,

For example I have give that record. My file contains many of such records with key and amount fields. And amount is not a fixed one. If the amount is greater than 250000 then we should go for split. Else we will write one record and proceed to next. Also, if the amount split goes beyond 10 records then we will write 10 split records in the first file and another record with rest of the amount is written to second file.


Input file
Code:

12345   3000000
12346   2000000
12347   2500000
12348   250000


Output file-1
Code:

12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12345   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12346   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12347   250000
12348   250000


Output file-2
Code:

12345   500000
[/code]
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Oct 31, 2016 11:22 am    Post subject:
Reply with quote

vnktrrd,

My previous post was assuming your input had only one-record. Now that you have multiple records like that the solution above does not hold good.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Oct 31, 2016 12:34 pm    Post subject:
Reply with quote

This might be of some interest.
Code:
//STEP01   EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                       
12345 3000000                                         
12346 2000000                                         
12347 2500000                                         
12348 0250000                                         
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1)) 
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  OUTFIL REPEAT=10,                                               
  IFTHEN=(WHEN=INIT,                                               
  OVERLAY=(15:C'250000',X,                                         
           SEQNUM,7,ZD,START=250000,INCR=250000,RESTART=(1,5),X,   
           22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,                   
           SEQNUM,2,ZD,RESTART=(1,5)))                             
//STEP02   EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTOUT  DD SYSOUT=*                                         
//SORTIN   DD DSN=&T1,DISP=(SHR,PASS)                           
//OUT1     DD SYSOUT=*                                         
//OUT2     DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                             
  OUTFIL FNAMES=OUT1,INCLUDE=(22,7,ZD,GE,0),                   
         IFTHEN=(WHEN=(22,7,ZD,LT,15,6,ZD,AND,22,7,ZD,GT,0,AND,
                       30,2,ZD,LT,10),                         
                 BUILD=(1,6,15,6,/,1,6,23,6)),                 
         IFTHEN=(WHEN=NONE,BUILD=(1,6,15,6))                   
  OUTFIL FNAMES=OUT2,INCLUDE=(22,7,ZD,GT,0,AND,30,2,ZD,EQ,10), 
                 BUILD=(1,6,22,7)       

OUT1
Code:
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12345 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12346 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12347 250000
12348 250000

OUT2
Code:
12345 0500000
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Mon Oct 31, 2016 1:04 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Arun,

Looks like this is working. I am still testing it tweaking the inputs.

Can you please explain how its achieved ? That might help me map to my data exactly.

Thanks,
Ramana.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Mon Oct 31, 2016 3:09 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Run the first step to SYSOUT rather than a named dataset, then you can see what is happening and work out how it is useful.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Mon Oct 31, 2016 3:22 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Arun,

I have run it and its working fine. Thanks a lot.

All I am confused is the below snippet. Can you please explain how this loop works ?

Code:

OVERLAY=(15:C'249000',X,                                     
         SEQNUM,7,ZD,START=249000,INCR=249000,RESTART=(1,5),X,
         22:7,7,ZD,SUB,22,7,ZD,ZD,LENGTH=7,X,                 
         SEQNUM,2,ZD,RESTART=(1,5)))                         


Thanks,
Ramana.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Oct 31, 2016 8:01 pm    Post subject:
Reply with quote

vnktrrd,

As Bill has pointed out above, you can look at the SORTOUT in the SYSOUT. If you take a look at that, you will see something like below.
Code:
12346 2000000 250000 1750000 01
12346 2000000 250000 1500000 02
12346 2000000 250000 1250000 03
12346 2000000 250000 1000000 04
12346 2000000 250000 0750000 05
12346 2000000 250000 0500000 06
12346 2000000 250000 0250000 07
12346 2000000 250000 0000000 08
12346 2000000 250000 025000} 09
12346 2000000 250000 050000} 10
Each record is repeated 10 times. And an offset to be subtracted from the original amount is created in incremental amounts (column-4 above). I have used the same column to store the subtraction results. So before subtraction, column 4 will look like this
Code:
0250000
0500000
0750000
1000000
1250000
1500000
1750000
2000000
2250000
2500000

So in the example above, we need all records to output-1 as long as the subtraction result is non-negative. Plus if your subtraction result is less than the incremental amount (250000), we are writing the leftover as an additional record. Because it cannot be split further.

We need to write output-2 only when the last record computation result is non-negative. ie, when we have a valid left over amount even after the 10th iteration.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 159
Location: Chennai

PostPosted: Tue Nov 01, 2016 2:57 am    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Arun,

We can do this in one step and save resources icon_smile.gif

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN       DD *                                             
12345 3000000                                               
12346 2000000                                               
12347 2500000                                               
12348 0250000                                               
//FILE1    DD SYSOUT=*                                       
//FILE2    DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  RESIZE FROM(IN) TO(FILE1) TOLEN(013) USING(CTL1)           
//CTL1CNTL DD *                                             
  INREC OVERLAY=(1,5,X,7,7,                                 
                 1,5,X,7,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,20,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,33,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,46,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,59,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,72,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,85,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,   
                 1,5,X,98,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,     
                 1,5,X,111,7,ZD,SUB,+250000,LENGTH=7,TO=ZD,     
                 1,5,C'~',124,7,ZD,SUB,+250000,LENGTH=7,TO=ZD) 
  OUTFIL FNAMES=FILE1,INCLUDE(7,7,ZD,GT,0,AND,6,1,CH,NE,C'~'), 
         BUILD=(1,5,X,C'250000')                               
  OUTFIL FNAMES=FILE2,INCLUDE(7,7,ZD,GT,0,AND,6,1,CH,EQ,C'~'), 
         OVERLAY=(6:C' ')
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Nov 01, 2016 6:50 am    Post subject:
Reply with quote

magesh23586,

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 159
Location: Chennai

PostPosted: Tue Nov 01, 2016 10:46 am    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Arun Raj wrote:

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.


I agree "not always," but in this case, it is better than your two steps solution.

Your code is little over engineered. You have ten overlay statements with SEQNUM, Restart and three outfil statements with ifthen build.

You are reading the file twice. One pass is always better than two pass.

Bill Woodger, Please advise, if I am wrong.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Nov 01, 2016 4:52 pm    Post subject: Re: Reply to: How to split the records using the amount field
Reply with quote

magesh23586 wrote:
Arun Raj wrote:

This is a good alternative. But again the number of steps may not always decide if there is any savings or not.
You are reading the file twice. One pass is always better than two pass.
You have extended each record 10 times towards the end or record. Depends on what exactly you try to "save", and if we have run sufficient tests to substantiate that.

And what if for some reason in the future, someone decides to split into 50 amounts, you need to extend your record further more towards the right and code additional 40 lines in the OVERLAY
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Tue Nov 01, 2016 5:32 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

My main concern with this topic is the data. Asked for a representative sample, data was only shown which is exactly divisible by 250000 (despite the example in the first post).

If that second set of sample data is representative, then the task is easy, one step, no ICETOOL.

You note that all the values on the first output file are 250000, and on the first OUTFIL output 1-10 records (using the slash-operator) depending on the amount (if greater than 2.5 million, output 10, 2.25 million, output nine, etc).

On the second OUTFIL, INCLUDE= for GT 2.5 million, and subtract 2.5 million to give the residual value.

If, as I suspect, the numbers are not nice and "round", it doesn't take much to make the final one a calculation on the first OUTFIL (subtract the value "below" the one you are testing for).

With all the references to the same fields and constants, I'd definitely show the solution with symbols.

But then, do I want to prepare and test all that, only to have TS/OP explain further that the description of the data is not quite right?

Lots of IFTHENs, lots of code to create, only two calculations at maximum per record.

For a different requirement, I'd go for RESIZE over two passes of the data, but Arun is correct, for any given solution it is only known to perform better or worse than another with the actual data. Generally I'd expect the RESIZE to work better, but no guarantees, as it does depend on what goes along with it, and can, not so much in this example, depend on the data.

As to "over engineering" there's a certain amount of that in both proposed solutions :-)

All suggested solutions would suffer from an increase to 50 splits, but all the code for all the solutions could be "generated" should such a thing arise.

My specific advice is to wait for/obtain clarity from TS/OP before getting to code...
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Wed Nov 02, 2016 11:46 am    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Arun,

A lot of input for this post and a lot of learning for me.

A small help further for your above code I am using for this purpose.

I have mapped the code to my requirement like below.

Code:

SORT FIELDS=COPY                               
OUTFIL REPEAT=10,                               
IFTHEN=(WHEN=INIT,                             
OVERLAY=(105:C'249000',                         
         SEQNUM,7,ZD,START=249000,             
                      INCR=249000,RESTART=(1,6),
         111:28,7,PD,SUB,111,7,ZD,ZD,LENGTH=15,
         SEQNUM,2,ZD,RESTART=(1,6)))           



Here I am overlaying the input record starting from 105 position and my amount field in the input file resides in 28th position in PD format with a length of 7. And, I want the subtracted value in ZD format.

After execution I am not getting the subtracted value correctly. Please suggest if I am doing something wrong.

Thanks,
Ramana.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Wed Nov 02, 2016 11:55 am    Post subject: Reply to: How to split the records using the amount field
Reply with quote

When something goes wrong, it is really, really, helpful to show what goes wrong. "It doesn't work" is useless.

Anyway, you sequence number starts at 112, not 111.
Back to top
View user's profile Send private message
vnktrrd

New User


Joined: 12 Jan 2010
Posts: 22
Location: india

PostPosted: Wed Nov 02, 2016 12:13 pm    Post subject: Reply to: How to split the records using the amount field
Reply with quote

Hi Bill,

The constant 249000 starts at 105 and ends at 110. So the seqnum starts from 111. Please correct if I am wrong.

Output is like this :

Code:

249000000000000052730M01


starts from 105.

Values in amount field : 278304

values after subtraction should be 278304-249000 which is 29304. But I am getting above values with a junk alphabet at the last position.

Thanks,
Ramana.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 1 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 22 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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