Icetool - Reduce 3 steps to 1



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

Icetool - Reduce 3 steps to 1

Postby Ankit Mittal » Wed Sep 01, 2010 10:18 am

Hi,

I have 2 files

File A (Main File):

----+----1----+----2---
15540614010057866010900
15540614010285646011000
15540614011429696011000
15540614013667746010900
15540614022133976010900
15540614031529416010900

File B (Reference File):

----+----1----+--
1554061401005786
1554061401028564C
1554061401142969
1554061401366774
1554061402125526L
1554061402213397
1554061403152941

Key in both files is from 1-16.

I want to match above 2 files on Key and remove records from file A for which value
in column 20-22 is 090 (lets call it Type) and File B contains a non blank
in column 17 (lets call it status) for the same record.

Based on above 2 files Output should look like:

----+----1----+----2---
15540614010057866010900
15540614010285646011000
15540614011429696011000
15540614013667746010900
15540614022133976010900
15540614031529416010900

Here 5th record from File A (key - 1554061402125526) is removed because this
record has type - 090 and is having status - L in file B.

I can do it in 3 steps.
In 1st step I will extract records from File A which has type as 090
In 2nd step I will compare it with file B using Icetool & remove the records with status as non blanks
In 3rd step I can merge the output file of step 2 with my input file A and remove duplicates.

What i want to know is can we do it in single step of using ICETOOL ?. If yes, can somebody give me a sample JCL.
Ankit Mittal
 
Posts: 4
Joined: Wed Sep 01, 2010 10:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: Icetool - Reduce 3 steps to 1

 

Re: Icetool - Reduce 3 steps to 1

Postby skolusu » Wed Sep 01, 2010 9:08 pm

Ankit Mittal wrote:Hi,
Here 5th record from File A (key - 1554061402125526) is removed because this
record has type - 090 and is having status - L in file B.
.

Ankit Mittal,

Your input file 1 does NOT have the record with 1554061402125526. Assuming that it is a typo , with z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), DFSORT now supports the JOINKEYS function which can do this kind of thing much more easily in just 1 step.

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//INA      DD *                                               
15540614010057866010900                                       
15540614010285646011000                                       
15540614011429696011000                                       
15540614013667746010900                                       
15540614022133976010900                                       
15540614031529416010900                                       
//INB      DD *                                               
1554061401005786                                               
1554061401028564C                                             
1554061401142969                                               
1554061401366774                                               
1554061402125526L                                             
1554061402213397                                               
1554061403152941                                               
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(1,16,A)                             
  JOINKEYS F2=INB,FIELDS=(1,16,A),OMIT=(17,1,CH,EQ,C' ')       
  JOIN UNPAIRED                                               
  REFORMAT FIELDS=(?,F1:1,80)                                 
  OUTFIL BUILD=(2,80),                                         
  INCLUDE=(1,1,ZD,EQ,1,OR,(1,1,CH,EQ,C'B',AND,21,3,ZD,NE,90)) 
//*


For complete details on JOINKEYS and the other new functions available with the Nov, 2009 DFSORT PTF, see:

http://www.ibm.com/support/docview.wss? ... g3T7000174
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: Icetool - Reduce 3 steps to 1

Postby Ankit Mittal » Thu Sep 02, 2010 6:44 pm

Hi Skolusu,

Thanks for the response. Yes, there was a typo in my last mail, sorry about that. I got the logic how it is gonna perform the file matching using join in just 1 step. But the probem is this version of DFSORT PTF is still not available in our system. I spoke to system programmers here and they say it will take some time for them to make it available.
Can I have some alternate method to accomplish this thing or do I need to go through my 3 step method as described in my first post ?

Thanks,
Ankit
Ankit Mittal
 
Posts: 4
Joined: Wed Sep 01, 2010 10:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: Icetool - Reduce 3 steps to 1

Postby skolusu » Thu Sep 02, 2010 9:44 pm

Ankit Mittal wrote:Can I have some alternate method to accomplish this thing or do I need to go through my 3 step method as described in my first post ?


Ankit,

Well it depends. I can't help you without complete details.

1. What is the LRECL and RECFM of file1 ?
2. What is the LRECL and RECFM of file2 ?
3. Are there any duplicates on key in file 1 ?
4. Are there any duplicates on key in file 2 ?

Show me your 3 step job and may be we can fine tune or club the steps.
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: Icetool - Reduce 3 steps to 1

Postby Ankit Mittal » Fri Sep 03, 2010 10:29 am

Hi Skolusu,

The RECFM and LRECL of file 1 is FB,80 with no duplicates on key.
The RECFM and LRECL of file 2 is FB,20 with no duplicates on key.

The 3 step JCL is:

//STEP010  EXEC PGM=SORT                                   
//SORTIN   DD  DSN=File1,DISP=SHR
//SORTOUT  DD  DSN=File1.cut,     
//           DISP=(NEW,CATLG,DELETE),                     
//           DCB=(,RECFM=FB,LRECL=80),                     
//           SPACE=(CYL,(5,1),RLSE)                       
//SYSOUT   DD   SYSOUT=*                                   
//SYSPRINT DD   SYSOUT=*                                   
//SYSDBOUT DD   SYSOUT=*                                   
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  INCLUDE COND=(20,3,ZD,EQ,100)                           
/*                                                         
//*     
//STEP020  EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD DSN=File1,DISP=SHR       
//IN2      DD DSN=File2,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,             
//            SPACE=(CYL,(5,5),RLSE)                           
//MATCH    DD DSN=MATCH,               
//            DISP=(,CATLG,DELETE),                             
//            SPACE=(CYL,(1,5),RLSE),                           
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=800)               
//NONMATCH DD DSN=NONMATCH,           
//            DISP=(,CATLG,DELETE),                             
//            SPACE=(CYL,(1,5),RLSE),                           
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=800)               
//TOOLIN   DD *                                                 
  COPY FROM(IN1) TO(T1) USING(CTL1)                             
  COPY FROM(IN2) TO(T1) USING(CTL2)                             
  SPLICE FROM(T1) TO(MATCH) ON(1,16,CH) -     
  WITH(82,1) KEEPNODUPS USING(CTL3)                             
/*                                                               
//CTL1CNTL DD *                                                 
  INREC OVERLAY=(81:C'AA')                                       
/*                                                               
//CTL2CNTL DD *                                                 
  OUTREC FIELDS=(1:1,16,81:C'BB')                               
/*                                                               
//CTL3CNTL DD *                                                 
  OUTFIL FNAMES=MATCH,INCLUDE=(81,2,CH,EQ,C'AB'),OUTREC=(1,80)   
  OUTFIL FNAMES=NONMATCH,INCLUDE=(81,2,CH,EQ,C'AA'),OUTREC=(1,80)
/*                                                               
//*                                                             
//STEP030  EXEC PGM=SORT                                         
//SORTIN   DD DSN=File1.cut,DISP=SHR   
//         DD DSN=NONMATCH,DISP=SHR     
//SORTOUT  DD  DSN=MATCH.FINAL,         
//           DISP=(NEW,CATLG,DELETE),   
//           DCB=(,RECFM=FB,LRECL=80),
//           SPACE=(CYL,(5,1),RLSE)   
//SYSOUT   DD   SYSOUT=*               
//SYSPRINT DD   SYSOUT=*               
//SYSDBOUT DD   SYSOUT=*               
//SYSIN    DD *                       
  SORT FIELDS=(1,16,ZD,A)             
  SUM FIELDS=NONE                     
/*                                     
//*                                                                                                                                 


Content of my file is:
File 1
----+----1----+----2----+
15520614022133976010900
15520614021255266010900
15520614010057866010900
15520614010285646011000
15520614036260766011000
15520614037956086010900
15520614037856176010900

File 2
----+----1----+----2-
1552061401028564C ----- 4h record in File 1
1552061402125526L ----- 2nd record in File 1

File1.cut contents after STEP010 runs successfully is:
15520614010285646011000
15520614036260766011000

Match File contents after STEP020 runs:
15520614021255266010900
15520614010285646011000

Non Match File contents after STEP020 runs:
15520614022133976010900
15520614010057866010900
15520614036260766011000
15520614037956086010900
15520614037856176010900

Input to STEP30:
1st File:
15520614010285646011000
15520614036260766011000
2nd file:
15520614022133976010900
15520614010057866010900
15520614036260766011000
15520614037956086010900
15520614037856176010900

Output of Step30:
15520614022133976010900
15520614010057866010900
15520614010285646011000
15520614036260766011000
15520614037956086010900
15520614037856176010900

I am getting the desired o/p in 3 step JCL. Can we reduce it to fine tune it further?
Ankit Mittal
 
Posts: 4
Joined: Wed Sep 01, 2010 10:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: Icetool - Reduce 3 steps to 1

Postby skolusu » Fri Sep 03, 2010 11:14 pm

Ankit,

You don't need that many passes of data. Here is a simple 2 step DFSORT JCL to get the desired results

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=Your input FB 20 byte no duplicate file,DISP=SHR
//SORTOUT  DD DSN=&&TM80,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)   
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                             
  OMIT COND=(17,1,CH,EQ,C' ')                                 
  OUTFIL REMOVECC,BUILD=(1,16,80:X),                           
  HEADER1=('$$$')                                             
//*                                                           
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=Your input FB 80 byte no duplicate file,DISP=SHR
//         DD DSN=&&TM80,DISP=SHR                             
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:C'0')),                 
  IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(81:ID=1)) 
  SORT FIELDS=(1,16,CH,A),EQUALS                                     
  SUM FIELDS=(81,1,ZD)         
                               
  OUTFIL BUILD=(1,80),                                         
  OMIT=(1,3,CH,EQ,C'$$$',OR,                                   
       (81,1,ZD,EQ,1,AND,20,3,ZD,EQ,90),OR,                   
       (81,1,ZD,EQ,1,AND,17,64,CH,EQ,C' '))
//*
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: Icetool - Reduce 3 steps to 1

Postby Ankit Mittal » Tue Sep 07, 2010 9:25 am

Thanks Skolusu.
Ankit Mittal
 
Posts: 4
Joined: Wed Sep 01, 2010 10:09 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post