Help with Icetool, Splice, Sum and Calculate complex sort



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Tue Oct 14, 2008 10:35 pm

Hello,
I have a somewhat complex problem that envolves 3 input files.
It might take several steps to get to the end result.
I would appreciate any suggestions.

The PASM input file has a record length of 380.
The OPPH input file has a record length of 398.
The OPVL input file has a record length of 380.

I need to extract all records from PASM and create a file containing the PA Number (1:11), Authorized Limit (92:16) and End Date (82:10).

I need to extract all records from OPVL where Ref.Trans.Code (182:2) = "PN" and create a file containing the whole PN Number (182:16), Closed Amount (101:16) and Last Check Date (146:10), then summarize Closed Amount by PN Number.

I need to extract all records from OPPH where the Blanket Number (131:11) is not spaces and create a file containing the Blanket Number (131:11) and Expended Amount (291:16), then summarize Expended Amount by Blanket Number.

I need to join the PASM file (PA Number) with the OPVL file (PN Number). Return all PASM records and only those OPVL records where PN Number = PA Number.

I need to join the PASM file (PA Number) with the OPPH file (Blanket Number). Return all PASM records and only those OPPH records where Blanket Number = PA Number.

The end result (file) should include all PA Numbers, End Date, Authorized Limit (from PASM), OPVL Closed Amount, OPPH Expended Amount, then calculate the Remaining Balance (Authorized Limit - OPVL Closed Amount - OPPH Expended amount)

If this is too complex, just let me know. :?
Thank you.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Wed Oct 15, 2008 12:30 am

I forgot to mention that the amount fields are already in the format of Siiiiiiiiiiii.ii ( s is sign).
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby Frank Yaeger » Wed Oct 15, 2008 2:36 am

I need more information. Show an example of the records in each input file (relevant fields only), and the corresponding expected intermediate records, and final output records. If there can be duplicates at any point, show that in your example. Explain the "rules" in terms of the example.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Wed Oct 15, 2008 5:18 am

Sample PASM records (with pos:length)
PA Number 1:11      End Date 82:10   Auth. Amount 92:16
D0000000022   â€¦â€¦   4/30/1998   â€¦â€¦   100000.00
D0000000023   â€¦â€¦   4/30/1995   â€¦â€¦   18000.00
D0000000025   â€¦â€¦   11/15/1997   â€¦â€¦   520000.00
D0000000030   â€¦â€¦   5/24/1997   â€¦â€¦   50000.00
D0000000032   â€¦â€¦   9/30/1996   â€¦â€¦   1200000.00


Sample OPVL records (with pos:length)
1:100   Closed Amt. 101:16   Last Check Date 146:10       PN Number 187:16      
….           +2100.00   â€¦.         08/13/2002   â€¦.        PN012RB000054.02      â€¦.
….          +39829.67   â€¦.         04/29/2002   â€¦.        PO012RB000054.02      â€¦.
….           +8866.50   â€¦.         04/29/2002   â€¦.        PN012RB000054.02      â€¦.
….          -13726.56   â€¦.         05/30/2002   â€¦.        PO012RB000054.02      â€¦.
….          +14685.00   â€¦.         05/30/2002   â€¦.        PN012RB000054.02      â€¦.
….                     
Sorted / Summarized result                     
….           25651.5   â€¦.          05/30/2002   â€¦.        PN012RB000054.02      



Sample OPPH records (with pos:length)
1:130   Blanket Num. 131:11      Expended Amt. 291:16            
….   N2000002903   â€¦.          +24948.00            
….   N3000002903   â€¦.          +10695.00            
….   N1000005282   â€¦.          +10070.00            
….   N1000005282   â€¦.              +0.00            
….   N1000005282   â€¦.          +13911.50            

Sorted / Summarized result                     
….   N2000002903   â€¦.           35643.00            
….   N1000005282   â€¦.           23981.50            



Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
Join if the OPPH Blanket Number equals the PA Number from PASM

Sample end result file:
PA Number      End Date    Auth. Amount    OPVL Closed Amt.     OPPH Expended Amt.   Rem.Balance
N2000002903   08/13/2002     +494500.00           +2100.00         +35643.00          +456757.00
N1000005282   04/29/2002     +500000.00          +39829.67         +23981.50          +436188.83
…..               



There would be duplicate PA and PN Numbers, but then they would be summarized (on amounts) before the final output.

THanks.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby Frank Yaeger » Thu Oct 16, 2008 2:29 am

Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
Join if the OPPH Blanket Number equals the PA Number from PASM


Huh? I don't see any matches between these numbers in your examples.

For the output,you show PA numbers of N2000002903 and N1000005282. I see blanket numbers in OPPH that match those numbers. But I don't see any values in PASM or OPVL that match those numbers. So your "rules" don't seem to match your examples.

You need to explain HOW you got those output records from the input records you show.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Thu Oct 16, 2008 3:58 am

Here is a better example:

PASM file input records:
PA Number 1:11      End Date 82:10   Auth. Amount 92:16
D0000000022   â€¦â€¦   4/30/1998   â€¦â€¦   +100000.00
D0000000025   â€¦â€¦   11/15/1997  ……   +520000.00
D0000000030   â€¦â€¦   5/24/1997   â€¦â€¦   +50000.00
N1000005282   â€¦â€¦   9/30/1996   â€¦â€¦   +1200000.00
N2000002903   â€¦â€¦   7/12/1999   â€¦â€¦   +53070.00


OPVL file input records:
1:100   Closed Amt. 101:16   Last Check Date 146:10       PN Number 187:16     
….           +2100.00   â€¦.         08/13/2002   â€¦.        PN012D0000000025      ….
….          +39829.67   â€¦.         04/29/2002   â€¦.        PO012RB000054.02      ….
….           +8866.50   â€¦.         04/29/2002   â€¦.        PN012D0000000025      ….
….          -13726.56   â€¦.         05/30/2002   â€¦.        PO012RB000054.02      ….
….          +14685.00   â€¦.         05/30/2002   â€¦.        PN012D0000000025     â€¦.
….                     
Sorted / Summarized result  (Just "PN" numbers are sorted and summed)                   
….           +25651.50   â€¦.          05/30/2002   â€¦.      PN012D0000000025     


OPPH file input records:
1:130   Blanket Num. 131:11      Expended Amt. 291:16           
….   N2000002903   â€¦.          +24948.00           
….   N3000002903   â€¦.          +10695.00           
….   N1000005282   â€¦.          +10070.00           
….   N1000005282   â€¦.              +0.00           
….   N1000005282   â€¦.          +13911.50           

Sorted / Summarized result     
….   N1000005282   â€¦.           +23981.50                   
….   N2000002903   â€¦.           +24948.00 
….   N3000002903   â€¦.           +10695.00       


Join if the OPVL PN Number (from pos 192:11) equals the PA Number from PASM
--- D0000000025 is in the PASM file.

Join if the OPPH Blanket Number equals the PA Number from PASM
---N1000005282 and N2000002903 are in the PASM file

Sample end result file.
PA Number      End Date    Auth. Amount    OPVL Closed Amt.     OPPH Expended Amt.   Rem.Balance
D0000000025   11/15/1997      +520000.00          +25651.50         +0.00              +494348.50
N1000005282    9/30/1996      +1200000.00         +0.00             +23981.50          +1176018.50
N2000002903    7/12/1999      +53070.00           +0.00             +35643.00          +17427.00
…..               


Does this help?
Thanks.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby Frank Yaeger » Thu Oct 16, 2008 5:18 am

N2000002903    7/12/1999      +53070.00           +0.00             +35643.00          +17427.00


Where did the +35643.00 value come from? Shouldn't the value be +24948.00 from the sorted/summarized result for the N2000002903 record in OPPH?

Let's take this a step at a time to make sure I understand what you want. Let's start with the three extracted output files (before the join).

I need to extract all records from PASM and create a file containing the PA Number (1:11), Authorized Limit (92:16) and End Date (82:10).

I need to extract all records from OPVL where Ref.Trans.Code (182:2) = "PN" and create a file containing the whole PN Number (182:16), Closed Amount (101:16) and Last Check Date (146:10), then summarize Closed Amount by PN Number.

I need to extract all records from OPPH where the Blanket Number (131:11) is not spaces and create a file containing the Blanket Number (131:11) and Expended Amount (291:16), then summarize Expended Amount by Blanket Number.


Does this DFSORT/ICETOOL job give you the three extracted output data sets the way you want them?

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//PASM DD DSN=... PASM input file
//OPPL DD DSN=... OPPL input file
//OPPH DD DSN=... OPPH input file
//PASMOUT DD DSN=...  PASM output file
//OPPLOUT DD DSN=...  OPPL output file
//OPPHOUT DD DSN=...  OPPH output file
//TOOLIN DD *
COPY FROM(PASM) TO(PASMOUT) USING(CTL1)
SORT FROM(OPPL) USING(CTL2)
SORT FROM(OPPH) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:1,11,82:82,10,92:92,16)
/*
//CTL2CNTL DD *
  INCLUDE COND=(182,2,CH,EQ,C'PN')
  INREC BUILD=(101:101,16,146:146,10,182:182,16)
  SORT FIELDS=(182,16,CH,A)
  OUTFIL FNAMES=OPPLOUT,NODETAIL,REMOVECC,
    SECTIONS=(182,16,
      TRAILER3=(101:TOT=(101,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
        SIGNS=(+,-)),146:146,10,182:182,16))
/*
//CTL3CNTL DD *
  INCLUDE COND=(131,11,CH,NE,C' ')
  INREC BUILD=(131:131,11,291:291,16)
  SORT FIELDS=(131,11,CH,A)
  OUTFIL FNAMES=OPPHOUT,NODETAIL,REMOVECC,
    SECTIONS=(131,11,
      TRAILER3=(131:131,11,
       291:TOT=(291,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),
         SIGNS=(+,-))))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Thu Oct 16, 2008 5:35 am

Yes, you are correct on the amount should be +24948.00.

However, it should be OPVL not OPPL, in your code.

I am running the test now and will let you know the results.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby claywilly » Thu Oct 16, 2008 6:40 am

OK. There were some typos (my fault) but I corrected them. > The PN number position starts at 187, not 182. I changed the code to reflect that. The OPPL was renamed to OPVL in all occurances.

The job ran ok after some minor output record length adjustments.

PASMOUT file :

0-15                   82-107      
----+----1----+   ----+----9----+----0----+--      
***************   ***************************      
AC97446120         06/30/2002     +1658400.00      
DGS-OFA-97F        06/30/2003           +0.00      
D0000000007        08/31/1996       +60000.00      
D0000000012        11/30/1996       +55000.00      
D0000000013        11/30/1996     +2506454.00      
D0000000020        03/31/1997       +47000.00      
D0000000022        04/30/1998      +100000.00      
D0000000023        04/30/1995       +18000.00      
D0000000025        11/15/1997      +520000.00      



OPVLOUT file:
100-120                     145-156            185-202   
0----+----1----+----2      +----5----+-      +----9----+----0--   
*********************       ***********      ******************   
        +64550.00           07/30/2008         PN001F2000002042   
          +128.46           06/14/2002         PN001N1000003319   
         +3629.25           02/22/2007         PN001N1000003488   
        +46099.66           02/15/2008         PN001N1000004520   
        +71642.94           01/10/2008         PN001N1000007362   
          +277.45           11/25/2003         PN001N1000009176   
          +326.38           02/21/2006         PN001N3000001358   
          +884.17           05/29/2002         PN001X4000000219   



OPPHOUT file:

130-142         293-306            
3----+----4--   --+----0----+-            
*************   **************            
 DA00000015              +0.00            
 D0000000007         +18570.50            
 D0000000020         +17319.30            
 D0000000022          +3276.50            
 D0000000023          +1838.20            
 D0000000032          +8584.62            
 D0000000039         +27306.70            
 D0000000040        +107026.30            



So far so good.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Help with Icetool, Splice, Sum and Calculate complex sort

Postby Frank Yaeger » Thu Oct 16, 2008 9:56 pm

Well, since you changed the job, please post the job you used.

Do you actually need to keep the PASMOUT, OPVLOUT and OPPHOUT data sets as separate data sets? Or are they just throwaway data sets to get us to the final output data set?

In your example, you only show matches for a particular key between two of the data sets at a time. Is it possible to have a match on a key in all three data sets?
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post