DFSORT - Need 1 step solution?



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

DFSORT - Need 1 step solution?

Postby bond_ajay » Wed Apr 02, 2008 10:27 am

Below is the requirement for which we want to know if we can achieve it in 1 step using a DFSORT - splice/icetool? The headings used, namely, Item, Location, RTL and CST are not part of the file but only indicative.

File -1: (key is Item(6bytes) + Location(4bytes). CST field is a dump from a table and has a assumed 4 decimal spaces)

Item    Location   RTL        CST
55037   5001       3198.4     2286.752
283846  5001       166301.52  129381.0672
330143  5001       30054.99   23547
330178  5001       30474.92   23876


File -2: (key is Item (6bytes)+ Location(4bytes). CST field is a dump from a table and has a assumed 4 decimal spaces)

Item    Location   RTL        CST
55037   5001       3198.4     1286.752
283846  5001       166301.52  119381.06
330143  5001       30054.99   23547
330000  5001       30474.92   23876


Requirement:
1. We need to compare the key fields (item + location) of the two files, file-1 and file-2. for the matching records we need to write an output record which will have the "ITEM", "LOCATION" and a difference of CST field of file-1 and CST field of File-2. (CST-File-1 - CST-File-2).

2. When the record is found on file-1 and not on file-2, we need to write an output record which will have "ITEM", "LOCATION" and a negated value of the CST field of file-1.

3. When the record is found on file-2 and not on file-1, we need to write an output record into a different output file which will have "ITEM", "LOCATION" and "CST" fields as in the file.

Expected OUTPUT:
Output-File-1:

Item    Location   CST
55037   5001       1000.0000
283846  5001       10000.0072
330143  5001       0.0000
330178  5001       -23876


Output-File-2:

Item    Location   CST
330000  5001       23876
bond_ajay
 
Posts: 11
Joined: Wed Apr 02, 2008 9:53 am
Has thanked: 0 time
Been thanked: 0 time

Re: DFSORT - Need 1 step solution?

Postby skolusu » Thu Apr 03, 2008 2:32 am

The following DFSORT/ICETOOL JCL will give you the desired results

 //STEP0100 EXEC PGM=ICETOOL                                 
 //TOOLMSG  DD SYSOUT=*                                       
 //DFSMSG   DD SYSOUT=*                                       
 //IN1      DD *   
 ----+----1----+----2----+----3----+----4----+                                           
 55037   5001       3198.4     2286.752                       
 283846  5001       166301.52  129381.0672                   
 330143  5001       30054.99   23547                         
 330178  5001       30474.92   23876                         
 //IN2      DD *                                             
 55037   5001       3198.4     1286.752                       
 283846  5001       166301.52  119381.06                     
 330143  5001       30054.99   23547                         
 330000  5001       30474.92   23876                         
 //T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
 //OUT1     DD SYSOUT=*                                         
//OUT2     DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
  COPY FROM(IN1) USING(CTL1)                                   
  COPY FROM(IN2) USING(CTL2)                                   
  SORT FROM(T1) USING(CTL3)                                     
//CTL1CNTL DD *                                                 
  INREC PARSE=(%00=(ABSPOS=31,ENDAT=C'.',FIXLEN=6),             
               %01=(FIXLEN=4)),                                 
  BUILD=(1,15,C'+',%00,UFF,EDIT=(TTTTTT),C'.',%01)             
  OUTREC OVERLAY=(24:24,4,TRAN=ALTSEQ)                         
  ALTSEQ CODE=(40F0)                                           
  OUTFIL FNAMES=T1,BUILD=(01,15,16,12,SFF,PD,LENGTH=8,24:C'1') 
//CTL2CNTL DD *                                                 
  INREC PARSE=(%00=(ABSPOS=31,ENDAT=C'.',FIXLEN=6),             
               %01=(FIXLEN=4)),                                 
  BUILD=(1,15,C'-',%00,UFF,EDIT=(TTTTTT),C'.',%01)             
  OUTREC OVERLAY=(24:24,4,TRAN=ALTSEQ)                         
  ALTSEQ CODE=(40F0)                                           
  OUTFIL FNAMES=T1,BUILD=(01,15,16,12,SFF,PD,LENGTH=8,24:C'2') 
//CTL3CNTL DD *                                                 
  OPTION EQUALS                                                 
  SORT FIELDS=(01,12,CH,A)                                     
  SUM FIELDS=(16,8,PD,24,1,ZD)   
                               
  OUTFIL FNAMES=OUT1,INCLUDE=(24,1,SS,EQ,C'1,3'),               
  IFTHEN=(WHEN=(24,1,CH,EQ,C'1'),                               
  BUILD=(1,15,16,8,PD,EDIT=(SIIIIIT.TTTT),SIGNS=('-',' ',,))), 
  IFTHEN=(WHEN=(24,1,CH,EQ,C'3'),                               
  BUILD=(1,15,16,8,PD,EDIT=(SIIIIIT.TTTT),SIGNS=(' ','-',,)))   

  OUTFIL FNAMES=OUT2,SAVE, 
  BUILD=(1,15,16,8,PD,EDIT=(IIIIIIT.TTTT))                                   
 


The output from the above job is

Out1 output

283846  5001     10000.0072
330143  5001         0.0000
330178  5001    -23876.0000
55037   5001      1000.0000


out2 output
330000  5001     23876.0000
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: DFSORT - Need 1 step solution?

Postby bond_ajay » Thu Apr 03, 2008 11:30 am

Thanks for the help skolusu.

I have just one doubt. If i remove the column RTL (and its values are removed from the file). So we will effectively have only Location, Item and the CST fields. If so, can we reduce the complexity of the icetool? what will the Sort card look like. Please reply with your comments. Thanks.
bond_ajay
 
Posts: 11
Joined: Wed Apr 02, 2008 9:53 am
Has thanked: 0 time
Been thanked: 0 time

Re: DFSORT - Need 1 step solution?

Postby skolusu » Thu Apr 03, 2008 11:34 pm

bond_ajay wrote:Thanks for the help skolusu.

I have just one doubt. If i remove the column RTL (and its values are removed from the file). So we will effectively have only Location, Item and the CST fields. If so, can we reduce the complexity of the icetool? what will the Sort card look like. Please reply with your comments. Thanks.


Bond_ajay,

Removing the RTL column isn't going to help. The complexity of the job depends on the way u have the input data and the way you want the output. And btw dont just get confused with the control cards. It is only using 1 sort pass and 2 copy operations which are quite efficient.
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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post