Files with match and no match key



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

Files with match and no match key

Postby vespa » Wed Aug 25, 2010 11:19 am

Hello,

I have 2 input files and want to create output files for the following:
* matched key that appear on both file1 and 2
* key that appears only on file1
* key that appears only on file2

These 2 input files are Fixed Block, recl=80, key is in same place, position 1-10;
Output file is also FB, recl=80; I used the ICETOOL SPLICE and it seemed to worked,
however, I have a couple questions:

1/ When I build output the file, how can I remove the identifier 'VV' BB' 'VB'?
2/ Currently the key is character (1,10,CH), will this solution works if the key is unsigned packed (1,10,PD)?
3/ If I need to have another output file that has matched key (pos. 1 to 10), but the detail does not match
(pos 11 to pos 80), how can I do it? Do I need to add another identifier?
4/ To sort the output files by the key, do I need an extra step to sort it or can I sort it at the same time that I build the output files?

Thanks.

input file 1
RECORD1 1                                                20100706

RECORD3 3                                                20100706

RECORD2 2                                                20100706

RECORD4 4                                                20100706


Input file 2
RECORD1 1                                                20100706

RECORD3 3                                                20100706

RECORD6 6                                                20100706

RECORD2 2                                                20100706

RECORD5 5                                                20100706


Output - key matched on both file1 and 2
RECORD1 1                                                20100706   VB

RECORD2 2                                                20100706   VB

RECORD3 3                                                20100706   VB

Output - key only in file 1
RECORD4 4                                                20100706   BB


Output file - key only in file 2
RECORD5 5                                                20100706   VV

RECORD6 6                                                20100706   VV


JCL:
//PS030    EXEC PGM=ICETOOL                                   

//TOOLMSG  DD SYSOUT=*                                         

//DFSMSG   DD SYSOUT=*                                         

//IN1      DD DSN=SAT.SP.TEST.FILE1,DISP=SHR                   

//IN2      DD DSN=SAT.SP.TEST.FILE2,DISP=SHR                   

//T1       DD DSN=&&T1,                                       

//            UNIT=SYSDA,                                     

//            SPACE=(CYL,(10,10),RLSE),                       

//            DISP=(MOD,PASS)                                 

//OUT12    DD DSN=SAT.SP.TEST.FILE.FIL1FIL2.MATCH,             

//            DISP=OLD                                         

//*           DISP=(,CATLG,DELETE),                           

//*           UNIT=SYSDA,                                     

//*           SPACE=(80,(250,100),RLSE),AVGREC=K,             

//*           DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)   

//OUT1     DD DSN=SAT.SP.TEST.FILE.NOMATCH.FILE1,       

//            DISP=OLD                                 

//*           DISP=(,CATLG,DELETE),                     

//*           UNIT=SYSDA,                               

//*           SPACE=(80,(250,100),RLSE),AVGREC=K,       

//*           DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)   

//OUT2     DD DSN=SAT.SP.TEST.FILE.NOMATCH.FILE2,       

//            DISP=OLD                                 

//*           DISP=(,CATLG,DELETE),                     

//*           UNIT=SYSDA,                               

//*           SPACE=(80,(250,100),RLSE),AVGREC=K,       

//*           DCB=(MODEL,RECFM=FB,LRECL=80,BLKSIZE=0)   

//TOOLIN   DD *                                   

* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS           

COPY FROM(IN1) TO(T1) USING(CTL1)                 

* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS           

COPY FROM(IN2) TO(T1) USING(CTL2)                 

* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR

* APPROPRIATE OUTPUT FILES.                       

SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(70,1) -

  USING(CTL3) KEEPNODUPS                           

/*                                                 

//CTL1CNTL DD *                                   

* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.     

  INREC OVERLAY=(70:C'BB')                         

/*                                                 

//CTL2CNTL DD *                                   

* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.     

  INREC OVERLAY=(70:C'VV')                         

/*                                                           

//CTL3CNTL DD *                                             

* WRITE MATCHING RECORDS TO OUT12 FILE     

  OUTFIL FNAMES=OUT12,INCLUDE=(70,2,CH,EQ,C'VB'),BUILD=(1,80)

* WRITE FILE1 ONLY RECORDS TO OUT1 FILE       

  OUTFIL FNAMES=OUT1,INCLUDE=(70,2,CH,EQ,C'BB'),BUILD=(1,80)

* WRITE FILE2 ONLY RECORDS TO OUT2 FILE     

  OUTFIL FNAMES=OUT2,INCLUDE=(70,2,CH,EQ,C'VV'),BUILD=(1,80)

/*                                                           
vespa
 
Posts: 19
Joined: Thu Jul 23, 2009 5:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: Files with match and no match key

 

Re: Files with match and no match key

Postby Frank Yaeger » Thu Aug 26, 2010 1:32 am

Why did you put the id in the middle of the records instead of at the end of the records? If you put the id at the end of the records (e.g. 81-82), then BUILD=(1,80) at the end will remove the id.

* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS
COPY FROM(IN1) TO(T1) USING(CTL1)
* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(81,1) -
  USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.
  INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE
  OUTFIL FNAMES=OUT12,INCLUDE=(81,2,CH,EQ,C'VB'),BUILD=(1,80)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE
  OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'BB'),BUILD=(1,80)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE
  OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'VV'),BUILD=(1,80)


Currently the key is character (1,10,CH), will this solution works if the key is unsigned packed (1,10,PD)?


You can use ON(1,10,PD) ... that will work for a valid PD value. I don't know what you mean by unsigned packed - do you mean it has a sign but the sign is always the same (e.g. C) or something else?

3/ If I need to have another output file that has matched key (pos. 1 to 10), but the detail does not match (pos 11 to pos 80), how can I do it? Do I need to add another identifier?


I'm not sure what you mean by this. Show an example of input and expected output and explain the rules.

4/ To sort the output files by the key, do I need an extra step to sort it or can I sort it at the same time that I build the output files?


When you use ON(1,10,CH) for SPLICE, the records are automatically sorted by 1,10,CH. You don't need an extra step.
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: Files with match and no match key

Postby vespa » Thu Aug 26, 2010 9:35 am

Frank,

Thanks for the info, I made the change and the id was removed.

Regard the unsigned pack field, below is an example of the 1st record in the file, the key is the 1st 6 bytes, unsigned and pack
in the copy book, I sorted the file using BI option instead of PD and it's sorted o.k.

.............h.'..........r..................àáë..&!!<.ä<ëä....

000000000000080B02100000009000000000000000000445425554244542222

000010000000080E2AC00000029000000000000000000453B00AAC03C330000


One more question, if I need to write out 2 additional output files from this process,
outfile4 has records from file 1 with matched key and outfile5 has record from file 2 with
matched key, how can I do this? In a different process, we might need to compare these 2 files for differences
starting from column 11 to 80.

Output file 4: Records from file 1 that has matched key
RECORD1 1                                                20100706   

RECORD2 2                                                20100706   

RECORD3 3                                                20100706   


Output file 5: Records from file 2 that has matched key
RECORD1 1                                                20100707   

RECORD2 2                                                20100707   

RECORD3 3                                                20100707   
vespa
 
Posts: 19
Joined: Thu Jul 23, 2009 5:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: Files with match and no match key

Postby Frank Yaeger » Thu Aug 26, 2010 10:07 pm

Yes, BI would be the correct format to use for those values.

To answer your other question, I'd like some more information about your DFSORT level. Please run the following job and post the //SYSOUT messages you receive:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*
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: Files with match and no match key

Postby vespa » Fri Aug 27, 2010 8:48 pm

Sysout messages
ICE805I 1 JOBNAME: #DPTEST , STEPNAME: STEP1                                                                                   
ICE802I 0 BLOCKSET     TECHNIQUE IN CONTROL                                                                                     
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                                           
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 C9-BASE   E5-K35433 E7-K24705                                       
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                                                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE                                           
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 09:43 ON FRI AUG 27, 2010 -                                     
              OPTION COPY                                                         00120000                                       
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1                                                                           
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 C9-BASE   E5-K35433 E7-K24705                                       
ICE193I 0 ICEAM1 ENVIRONMENT IN EFFECT - ICEAM1 INSTALLATION MODULE SELECTED                                                     
ICE088I 0 #DPTEST.STEP1   .        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB                                                 
ICE093I 0 MAIN STORAGE = (MAX,2097152,2083934)                                                                                   
ICE156I 0 MAIN STORAGE ABOVE 16MB = (2006494,2006494)                                                                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256                     
ICE128I 0 OPTIONS: SIZE=2097152,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT                           
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG             
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,STIMER=Y,COBEXIT=COB2         
ICE131I 0 OPTIONS: TMAXLIM=2097152,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0                         
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N   
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=300 ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX                   
ICE235I 0 OPTIONS: NULLOUT=RC0                                                                                   
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                                                   
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN                                                                     
ICE889I 0 CT=MAX     , SB=4, L=0, D=0000                                                                         
ICE902I 0 O AZ00  I AZ00                                                                                         
ICE751I 1 EF-K10929 F0-K30362 E8-K90013                                                                         
ICE900I 0 CON=1,MUV=0,VOL=1,ENU=0,SBK=0,SRC=0,VEM=0                                                             
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB                                                             
ICE055I 0 INSERT 0, DELETE 0                                                                                     
ICE054I 0 RECORDS - IN: 1, OUT: 1                                                                               
ICE891I 1 2075888 WMAIN, 8040 CMAIN, MAX CALLOC, N SCN, B BA, 0 AZ, 0 BZ, YY QC                                 
ICE892I 1 80 RIN  80 BLI  80 BLO  78 RUN  0 BUN  2086 CPU  00 CVC                                               
ICE893I 1 0 XIN 0 WIN 0 GIN NDEN PFP   B6D CM000 CIX UPTH LMD VS   RUX                                           
ICE026I 1 SMF RECORD NOT WRITTEN TO THE SMF DATA SET(RC=20)                                                     
ICE052I 0 END OF DFSORT
vespa
 
Posts: 19
Joined: Thu Jul 23, 2009 5:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: Files with match and no match key

Postby Frank Yaeger » Fri Aug 27, 2010 11:48 pm

This would be easy to do in one pass if you could use JOINKEYS, but you don't have the DFSORT PTF that supports it. How about just adding another SELECT operator to create OUT4. Here's the revised DFSORT/ICETOOL job:

//PS030    EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN1      DD DSN=...  input file1
//IN2      DD DSN=...  input file2
//T1       DD DSN=&&T1,
//            UNIT=SYSDA,
//            SPACE=(CYL,(10,10),RLSE),
//            DISP=(MOD,PASS)
//OUT4    DD DSN=...  match file1
//OUT5    DD DSN=...  match file2
//OUT1    DD DSN=...  file1 only
//OUT2    DD DSN=...  file2 only
//TOOLIN   DD *
* ADD 'BB' IDENTIFIER FOR FILE1 RECORDS
COPY FROM(IN1) TO(T1) USING(CTL1)
* ADD 'VV' IDENTIFIER FOR FILE2 RECORDS
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT5) ON(1,10,CH) WITH(81,1) -
  USING(CTL3) KEEPNODUPS
SELECT FROM(T1) TO(OUT4) ON(1,10,CH) FIRSTDUP USING(CTL4)
/*
//CTL1CNTL DD *
* ADD 'BB' (BASE) IDENTIFIER TO FILE1 RECORDS.
  INREC OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
* ADD 'VV' (BASE) IDENTIFIER TO FILE2 RECORDS.
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE
  OUTFIL FNAMES=OUT5,INCLUDE=(81,2,CH,EQ,C'VB'),BUILD=(1,80)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE
  OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'BB'),BUILD=(1,80)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE
  OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'VV'),BUILD=(1,80)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT4,BUILD=(1,80)
/*
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: Files with match and no match key

Postby vespa » Thu Oct 07, 2010 12:16 am

Thanks Frank, it worked !!
vespa
 
Posts: 19
Joined: Thu Jul 23, 2009 5:30 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post