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

I really appreciate your patience.
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

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

 

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

Postby Frank Yaeger » Sat Oct 18, 2008 3:17 am

I've been answering questions all day and I have to get on to some other work. I've asked Kolusu to take a look at this and see if he can help. He has a lot more patience and tenacity than I do for these kinds of complex questions (and he's very clever too).
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: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

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

Postby claywilly » Sat Oct 18, 2008 3:26 am

I wish him luck.

BTW. The JCL code that was posted awhile back does work. We just need to splice things together... unless there is a different approach altogether.

thanks again.
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 skolusu » Sat Oct 18, 2008 6:14 am

The following DFSORT/ICETOOL JCL will give you the desired results. You only showed me the output you wanted for final output but not for the nomatch file. I just assumed that you only wanted the data that is present in OPVL file. Make sure to have disp=MOD for the file T1

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//PSAM     DD DSN=INPUT.PSAM,DISP=SHR                       
//OPVL     DD DSN=INPUT.OPVL,DISP=SHR                       
//OPPH     DD DSN=INPUT.OPPH,DISP=SHR                       
//TEMP     DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(60,20),RLSE)
//OUT      DD SYSOUT=*                                       
//NOMATCH  DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  COPY FROM(PSAM) TO(TEMP) USING(CTL1)                       
  SORT FROM(OPVL) TO(TEMP) USING(CTL2)                       
  SORT FROM(OPPH) TO(TEMP) USING(CTL3)                       
  SORT FROM(TEMP) USING(CTL4)                               
//CTL1CNTL DD *                                             
  OUTREC BUILD=(01:1,11,                                     
                13:82,10,                                   
                24:92,16,SFF,ZD,LENGTH=15,                   
                40:+0,TO=ZD,LENGTH=15,                       
                56:+0,TO=ZD,LENGTH=15,                       
                90:C'100')                                   
/*                                                           
//CTL2CNTL DD *                                             
  INCLUDE COND=(182,2,CH,EQ,C'PN')                           
  SORT FIELDS=(182,16,CH,A)                                 
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),         
  SECTIONS=(182,16,                                         
  TRAILER3=(01:187,11,                                       
            13:146,10,                                       
            40:TOT=(101,16,SFF,ZD,LENGTH=15),               
            56:15C'0',                                       
            90:C'010'))                                     
/*                                                           
//CTL3CNTL DD *                                           
  INCLUDE COND=(131,11,CH,NE,C' ')                         
  SORT FIELDS=(131,11,CH,A)                               
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),       
  SECTIONS=(131,11,                                       
  TRAILER3=(01:131,11,                                     
            40:15C'0',                                     
            56:TOT=(291,16,SFF,ZD,LENGTH=15),             
            90:C'001'))                                   
/*
//CTL4CNTL DD *                                             
  OPTION EQUALS                                             
  INREC OVERLAY=(94:7C'0',C'1')                             
  SORT FIELDS=(1,11,CH,A)                                   
  SUM FIELDS=(40,15,ZD,56,15,ZD,90,3,ZD,94,8,ZD)             
  OUTFIL FNAMES=OUT,INCLUDE=(90,3,ZD,GT,100),REMOVECC,       
  BUILD=(01:01,23,                                           
         24:24,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         40:40,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         56:56,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-),   
         72:24,15,ZD,SUB,40,15,ZD,SUB,56,15,ZD,             
         EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-)),               
  HEADER1=(01:'PA NUMBER',                                   
           15:'END DATE ',                                   
           28:'AUTH. AMOUNT',                               
           41:'OPVL CLOSED AMT',                             
           59:'OPPH EXPD AMT',                               
           77:'REM.BALANCE')                                 
                                                             
  OUTFIL FNAMES=NOMATCH,REMOVECC,                           
  INCLUDE=(90,1,ZD,EQ,0,AND,91,1,ZD,EQ,1),                   
  BUILD=(01:01,23,                                           
         24:40,15,ZD,EDIT=(SIIIIIIIIIIIT.TT),SIGNS=(+,-)),   
  HEADER1=(01:'PA NUMBER',                                   
           15:'END DATE ',                                   
           28:'AUTH. AMOUNT')                               
/*                                                           


The out file will have

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


The nomatch will have

PA NUMBER     END DATE     AUTH. AMOUNT
F2000002042 07/30/2008        +64550.00
N1000003488 02/22/2007         +3629.25
N1000004520 02/15/2008        +46099.66
N1000007362 01/10/2008        +71642.94
N1000009176 11/25/2003          +277.45
N3000001358 02/21/2006          +326.38
X4000000219 05/29/2002          +884.17


if you need a different output for nomatch file let me know and I will create it

Hope this helps...

Cheers
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

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

Postby claywilly » Mon Oct 20, 2008 9:46 pm

Hi Skolusu,

I am getting all zero amounts in the OPVL Closed Amount field and no records in the NoMatch file.

I am looking into it.

If you notice something before I do, please let me know..

Other than that, It looks great!

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 sor

Postby skolusu » Mon Oct 20, 2008 10:38 pm

claywilly,

I used the sample data you supplied in this post

dfsort-icetool-icegener/topic1130-10.html#p4269

You should have atleast found N1000003319 having 128.46 in the OVPL Closed amount column

If you want to check for the nonmatch records change CTL4CNTL to the following and rerun the job and see the contents at pos 90. If you find a record with 010 or 011 , they are your unmatched records


//CTL4CNTL DD *                                            
  OPTION EQUALS                                            
  INREC OVERLAY=(94:7C'0',C'1')                            
  SORT FIELDS=(1,11,CH,A)                                  
  SUM FIELDS=(40,15,ZD,56,15,ZD,90,3,ZD,94,8,ZD)
  OUTFIL FNAMES=OUT
/*


Kolusu
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

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

Postby claywilly » Tue Oct 21, 2008 12:46 am

Ok. I found the problem.

In an earlier post I had the PN number position at 182, this should have been 187. My mistake.

I adjusted the code and the job ran with the expected results.

Here is the code I changed:
//CTL2CNTL DD *                                     
  INCLUDE COND=(187,2,CH,EQ,C'PN')                 
  SORT FIELDS=(187,16,CH,A)                         
  OUTFIL FNAMES=TEMP,NODETAIL,REMOVECC,BUILD=(92X),
  SECTIONS=(187,16,                                 
  TRAILER3=(01:192,11,                             
            13:146,10,                             
            40:TOT=(101,16,SFF,ZD,LENGTH=15),       
            56:15C'0',                             
            90:C'010'))                             

The "182" was changed to "187" and the "187" was changed to "192".

Now I am getting the correct amounts in the OPVL CLosed Amount and
a ton of records in the NoMatch file.

I really appreciate both you and Frank for all your hard efforts.
Once again you have helped me tremendously.

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

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post