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

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

Postby claywilly » Thu Oct 16, 2008 10:19 pm

Hi Frank,

I don't really need the 3 output files.
However, I do need a file that contains the PN numbers from OPVL that do not exist in the PASM file.

Yes, It is possible to have a match in all three datasets.

Here is the JCL I used.
//S1   EXEC  PGM=ICETOOL                                         
//PASM DD  DSN=PFS.FS99U.DBSEQ.AFN5.PASM.EXTRACT(+0),DISP=SHR   
//OPPH DD  DSN=PFS.FS99U.DBSEQ.AFN5.OPPH.EXTRACT(+0),DISP=SHR   
//OPVL DD  DSN=PFS.FS99U.DBSEQ.AFN5.OPVL.EXTRACT,DISP=SHR       
//TOOLMSG   DD  SYSOUT=*                                         
//DFSMSG    DD  SYSOUT=*                                         
//PASMOUT  DD DSN=ACVE023.SEQ.AFN5.PASMOUT,                     
//            DISP=(NEW,CATLG,DELETE),                           
//            UNIT=DISK,                                         
//            SPACE=(CYL,(300,20),RLSE),                         
//            DCB=(RECFM=FB,LRECL=300,BLKSIZE=0)                 
//OPVLOUT  DD DSN=ACVE023.SEQ.AFN5.OPVLOUT,                     
//            DISP=(NEW,CATLG,DELETE),                           
//            UNIT=DISK,                                         
//            SPACE=(CYL,(300,20),RLSE),                         
//            DCB=(RECFM=FB,LRECL=202,BLKSIZE=0)                 
//OPPHOUT  DD DSN=ACVE023.SEQ.AFN5.OPPHOUT,                     
//            DISP=(NEW,CATLG,DELETE),                           
//            UNIT=DISK,                                         
//            SPACE=(CYL,(300,20),RLSE),                         
//            DCB=(RECFM=FB,LRECL=306,BLKSIZE=0)                 
//TOOLIN DD *                                                   
COPY FROM(PASM) TO(PASMOUT) USING(CTL1)                         
SORT FROM(OPVL) USING(CTL2)                                     
SORT FROM(OPPH) USING(CTL3)                                     
/*                                                               
//CTL1CNTL DD *                                                 
  INREC BUILD=(1:1,11,82:82,10,92:92,16)                         
/*                                                               
//CTL2CNTL DD *                                                 
  INCLUDE COND=(187,2,CH,EQ,C'PN')                               
  INREC BUILD=(101:101,16,146:146,10,187:187,16)                 
  SORT FIELDS=(187,16,CH,A)                                     
  OUTFIL FNAMES=OPVLOUT,NODETAIL,REMOVECC,                       
      SECTIONS=(187,16,                                         
        TRAILER3=(101:TOT=(101,16,SFF,EDIT=(SIIIIIIIIIIIT.TT),   
          SIGNS=(+,-)),146:146,10,187:187,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=(+,-))))                               
/*                                                       
//



What does "SFF" do when you total the amounts?

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 » Fri Oct 17, 2008 1:41 am

SFF (signed free form format) handles numbers with decimal points, + and - signs, etc.

I don't really need the 3 output files.


Sigh. I interpreted your first post to mean you did.

However, I do need a file that contains the PN numbers from OPVL that do not exist in the PASM file.

Yes, It is possible to have a match in all three datasets.


I'm not sure what you mean by the first sentence. Is this another output data set besides the one with the joined fields? I don't think you showed either of these cases in your example.

I think we've gotten offtrack here. Let's start over. PLEASE show me an example of the records in your input files and what you want for output with all cases covered. Be specific about the output data set(s) you need, and the "rules" for getting from input to that output (if you want to talk about intermediate data sets, label them as such).
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 » Fri Oct 17, 2008 1:58 am

Hi Frank,

I believe you are on the right track. I supposed that the 3 output datasets created so far would just be 'temporary' in that they would be spliced together to create the final output file. In that sense, if they are temporary, then I don't need to keep them.

I did suggest that another file be created for those PN numbers that do not match the PA numbers in the PASM file. - It would be nice to have.

I doubt we need to start over. The current output from the 3 datasets contain over 50k records each. I also know that there are PA and PN numbers that match in all 3 datasets. Sorry that the sample records I provided don't really indicate a match - I just got lazy than trying to match a few records out of 150k of them.

So currently the output the way it is now is correct, we just need to combine them all into one output file.
The intermediate data sets, I would assume would be the 3 output datasets we have now.

If then it is possible to splice or merge all three of these datasets into one, then perform the Remaining Balance calculation - we would be done.

What do you think?
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 » Fri Oct 17, 2008 2:19 am

I think I'd like you to go back and create an example that shows what you want to do. If you don't want the intermediate data sets, then I don't need to create them the way I did when I thought you needed them. If you want two output data sets, it still isn't clear to me what they are. I'd prefer NOT to have to go back through all of the notes to figure out what you want (I do have a day job developing DFSORT). So if you want to pursue this, I'm going to put the burden on you to give me a complete example and rules I can work with.
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 » Fri Oct 17, 2008 5:04 am

Maybe this is more clear ..
I currently have 3 input datasets:
1) PASM – which contain fields (p:l):
PA Number (1:11)
End Date (82:10)
Authorized Amount (92:16) Already formatted (siiiiiiiiiiii.ii)
2) OPVL – which contain fields (p:l):
OPVL Closed Amount (101:16) Already formatted (siiiiiiiiiiii.ii)
Last Check Date (146:10)
Ref.Trans.Code (187:2)
PN Number (192:11)
3) OPPH – which contain fields (p:l)
Blanket Number (131:11)
Expended Amount (291:16) Already formatted (siiiiiiiiiiii.ii)

Intermediate files needed:

PASMout: (temp)
Sorted by PA Number (1:11)

OPVLout: (temp)
Only extract records containing “PN” in Ref.Trans.Code (187:2)
Record layout:
PN Number (192:11), OPVL Closed Amount (101:16), Last Check Date (146:10)
Summarize OPVL Closed Amount by PN Numbers

OPPHout: (temp)
Only extract records where Blanket Number is not blank.
Record layout:
Blanket Number (131:11), Expended Amount (291:16)
Summarize Expended Amount by Blanket Numbers

MatchPN file: (temp)
Match OPVL (PN Number) with the PASM (PA Number)

MatchBLNKT file: (temp)
Match OPPH (Blanket Number) with the PASM (PA Number)

NonMatchPN file: (keep)
Records of PN Numbers that do not match with PASM (PA Number)

----
Final Output file: (keep)
Record layout: (combo of match files)
PA Number , End Date (PASM), Authorized Amount (PASM), OPVL Closed Amount (OPVLout), Expended Amount (OPPHout), Remaining Balance
Remaining Balance = (Authorized Amount – OPVL Closed Amount – OPPH Expended Amount)

(These input records are just examples, not the real data)
Sample PASM 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


Sample OPVL 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     


Sample OPPH 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                     
….   N2000002903   â€¦.           35643.00           
….   N1000005282   â€¦.           23981.50           


JCL that creates the 3 output datasets (PASMout, OPVLout and OPPHout)
//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=(+,-))))
/*


The following Data is real output.

Sample PASMout records:
0-15                   82-107     
----+----1----+   ----+----9----+----0----+--     
***************   ***************************     
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
…     
N1000003319        11/15/1997      +750000.00 <



Sample OPVLout records:
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   


Sample OPPHout records:
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           


Sample MatchPN records:
…
N1000003319        11/15/1997      +750000.00
…


Sample MatchBLNKT records:
…
D0000000020         +17319.30 <           
D0000000022          +3276.50 <           
D0000000023          +1838.20 <         
…


Sample NonMatchPN records:
…
PN001F2000002042         +64550.00           07/30/2008
PN001X4000000219           +884.17           05/29/2002 
…         


Sample expected Final Output file:
PA Number      End Date    Auth. Amount    OPVL Closed Amt.     OPPH Expended Amt.   Rem.Balance
D0000000020    03/31/1997      +47000.00           +0.00             +17319.30      +29680.70
D0000000022    04/30/1995     +100000.00           +0.00             +3276.50       +96723.50
D0000000023    04/30/1998      +18000.00           +0.00             +1838.20       +16161.80
N1000003319    11/15/1997     +750000.00         +128.46             +0.00          +749871.54

…..               
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 » Fri Oct 17, 2008 5:52 am

I'm still a bit confused.

You labelled 6 files as "Intermediate files needed". Do you actually need to keep any or all of these files? If so, which ones? If I can create the final output file without creating one or more of these intermediate files, do you care?

You don't show any final output records with values from OPVL and OPPH, although I thought you said that could happen. Can it happen? Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."? I was hoping you would show all possible cases in your new 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 » Fri Oct 17, 2008 6:05 am

I get more confused everytime I think about it.

The intermediate files that I don't need to keep are marked "temp". If you can work the code another way where these files are not needed, be my guest. The files marked "keep" are the ones I need - NonMatchPN's and the Final Output file.

The final output file consists of the matchPN's and matchBLNKT files. They both would have OPVL and OPPH records that match PA Numbers in the PASM file. (marked with '<' )

There could be non-zero and negative amounts. If it is a non-zero then it should be converted to "0.00".

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 » Fri Oct 17, 2008 10:48 pm

I'm not clear on the answer to this question from my previous post.

Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."?


You said:

There could be non-zero and negative amounts. If it is a non-zero then it should be converted to "0.00".


Where can there be negative amounts? Where can there be non-zero amounts that you want to change to"0.00"?

Can you show an example?

I'm trying to help, but without a clear explanation of what you want to do, it's rather difficult.
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 » Fri Oct 17, 2008 11:29 pm

Could you have an output record with non-zero values for both "OPVL Closed Amt." and "OPPH Expended Amt."?


--yes there could be non-zero values for both fields.

The negative amounts could occur in the OPVL Closed Amount field.

Here is an example of an final output file

PA_Number     Auth_amount     Expended   PN Closed Amount   Remaining Balance
N1000004232   217,692.00   217,691.30     -7,256.40         7,257.10
N1000007503   9,948.00     8,348.00       -709.73           2,309.73
F2000002590   9,000.00     2,584.88       -171.32           6,586.44
F1000001135   1,000.00                    -105.00           1,105.00
F2000000489   40,000.00                   -102.83           40,102.83




The Remaining Balance is Auth.amount - expended- closed amount.

Hope it is getting clearer.

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 » Sat Oct 18, 2008 1:00 am

Yes, it is getting clearer. Now I just have to find time to work on it.
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

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post