IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare a field of 2 files and replace it if matches
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 11:46 am
 Reply with quote
Hi,

I need a help on the below.

I have 2 input files. I have to match a field from first file with a field from second file. If matches then I have to replace the field of the first file with another field of second file. If not matches then I have to keep the record in the first file only.

Example:

File1:
Code:
5----------16
00026
00028
00038
00040
00041
00048


File2:
Code:
1---------11
00026B00026
00028B00028
00038B00038
00040B00040
00041B00041
00042B00042


Required output:
Compare (5,5)of File1 with (1,5) of File2. If matches replace (5,5) of File1 with (6,6) of File2. If not matches keep the record as it is.


Code:
5----------16
B00026
B00028
B00038
B00040
B00041
00048
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 12:11 pm
 Reply with quote
Fyi..

File2 has no duplicates but File1 has duplicates.

Regards,
Back to top
View user's profile Send private message
thisak

New User


Joined: 09 Jun 2006
Posts: 14
PostPosted: Wed Mar 05, 2008 12:42 pm
 Reply with quote
Using SPLICE of ICETOOL you can perform this. Please refer to any of SPLICE notes... you will come to know.

I you not getting any solution, then revert, I will give you the code for performing.
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 1:16 pm
 Reply with quote
Thanks Thisak,

Yes I am going through Splice.
As it is a bit urgent for me Could you please give me the code?

Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 2:08 pm
 Reply with quote
Sorry the requirement is.

Input file1:

Code:
5----------17---20
00026       ABCD
00028       XYZP
00038       ABCP
00040       LMNQ
00041       ABCM
00048       LPOK


Input file2:

Code:
1---------11---15
00026B00026ABCD
00028B00028XYZP
00038B00038ABCP
00040B00040LMNQ
00041B00041ABCM
00042B00042LPOK



Required output:

match (5,5) and (17,4) of Input file1 with (1,5) and (12,4) of Input file2 respectivly. If matches replace (5,5) of Input file1 with (6,6) of Input file2 as below.
Code:
5----------16
B00026
B00028
B00038
B00040
B00041
00048


It doesn't match keep the record in the Input file1 as it is.

Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 2:12 pm
 Reply with quote
Fyi..

File2 has no duplicates but File1 has duplicates.

Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Wed Mar 05, 2008 3:42 pm
 Reply with quote
Looking at some previous topics I tried the following.

Code:
                   
//S1           EXEC  PGM=ICETOOL                                   
//TOOLMSG      DD SYSOUT=*                                         
//DFSMSG       DD SYSOUT=*                                         
//IN2          DD DSN=TBIS.IM.STKCD.MAPPING.OUT1,DISP=SHR           
//T1           DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON          DD DSN=TBIS.IM.STKCD.BIVXE.INPUT1,DISP=SHR           
//             DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)             
//OUT          DD DSN=TBIS.IM.STKCD.MAPPING.BIVXE.FRK,             
//             DISP=(OLD,CATLG,CATLG),UNIT=(DISK,5),               
//             SPACE=(CYL,(10,10),RLSE),                           
//             DCB=(RECFM=FB,LRECL=332,BLKSIZE=10624)               
//TOOLIN DD *                                                       
  COPY FROM(IN2) USING(CTL1)                                       
  SPLICE FROM(CON) TO(OUT) ON(93,5,CH) ON(105,4,CH) VLENMAX -       
  KEEPNODUPS WITH(93,6)                                             
/*                                                                 
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,FTOV,BUILD=(93:1,5,105:13,4,93:6,6)             
/*                                                                 
//*                                                                                                   
 



How ever I am getting RC= 0016,


Code:
ICE126A 9 INCONSISTENT T1       IFTHEN 0 REFORMATTING FIELD FOUND     
ICE751I 0 C5-K90007 C6-K90007 C7-K90000 C8-K90007 E9-K90007 E7-K11698
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed Mar 05, 2008 10:21 pm
 Reply with quote
Your descripton of your requirement is confusing. You don't say what the RECFM and LRECL of the input file is or what you want for the output file.
That SPLICE job makes no sense.

Why are you using FTOV - do you have an FB input file and want a VB output file?

You say file2 has duplicates but you don't show any duplicates. You show every record in file2 with a match in file1. Is that really the case or can a record in file2 not have a match in file1?

Do you want 17-20 from input file1 in the output file or not?

Please show a better example of the input records in each file with all possible variations, and the expected output records. Give the RECFM and LRECL of each input file and the output file.
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Thu Mar 06, 2008 10:27 am
 Reply with quote
Hi Frank,

File1:FB
Record length . . . : 332
Block size . . . . : 10624

File2:FB
Record length . . . : 80
Block size . . . . : 10720

File1:
Code:
9----+----0----+---
*******************
INV00001       LEQUN
INV00026       LEQUN
INV00026       LEQUN
INV00038       LEQUN
INV00040       LEQUN
INV00041       LEQUN


File2:
Code:
----+----1----+----
*******************
00026B00026BLEQU
00028B00028BLEQU
00038B00038BLEQP
00040B00040BLEQU
00041B00041BLEQU


Required output:

Match (93,5)&(105,4) of File1 with (1,5)&(13,4) of File2. If Matches replace (93,6) in File1 by (6,6) of File2.

Code:
9----+----0----+---
*******************
INV00001       LEQUN
INVB00026      LEQUN
INVB00026      LEQUN
INV00038       LEQUN
INVB00040      LEQUN
INVB00041      LEQUN


Please let me know how to code this.
Also Please let me know what has to be done if File1 is a VB file.

Thanks,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Thu Mar 06, 2008 12:33 pm
 Reply with quote
Frank,

The LRECL and BLKSIZE of the output should be same as File1.

Regards,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Mar 06, 2008 11:20 pm
 Reply with quote
Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/332)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/332)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(333,5,CH) ON(338,4,CH) -
 KEEPNODUPS KEEPBASE -
 WITHALL WITH(1,92) WITH(99,240) WITH(342,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(93:6,6,333:1,5,338:13,4,342:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(333:93,5,338:105,4,342:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(342,2,CH,EQ,C'BB'),
    BUILD=(1,332)
/*
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Thu Mar 06, 2008 11:42 pm
 Reply with quote
Hi Frank,

I tried but getting RC=16

JCL
Code:
//S1    EXEC  PGM=ICETOOL                                     
//TOOLMSG DD SYSOUT=*                                         
//DFSMSG  DD SYSOUT=*                                         
//IN1 DD DSN=TBIS.IM.STKCD.BIVXE.INPUT1,DISP=SHR             
//IN2 DD DSN=TBIS.IM.STKCD.MAPPING.OUT1,DISP=SHR             
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=TBIS.IM.STKCD.MAPPING.BIVXE.FRK,                 
//       DISP=(OLD,CATLG,CATLG),UNIT=(DISK,5),               
//       SPACE=(CYL,(10,10),RLSE),                           
//       DCB=(RECFM=FB,LRECL=332,BLKSIZE=10624)               
//TOOLIN DD *                                                 
COPY FROM(IN2) TO(T1) USING(CTL1)                             
COPY FROM(IN1) TO(T1) USING(CTL2)                             
SPLICE FROM(T1) TO(OUT) ON(333,5,CH) ON(338,4,CH) -           
 KEEPNODUPS KEEPBASE -                                       
 WITHALL WITH(1,92) WITH(99,240) WITH(342,1) USING(CTL3)     
/*                                                           
//CTL1CNTL DD *                                               
  INREC BUILD=(93:6,6,333:1,5,338:13,4,342:C'BB')             
/*                                                           
//CTL2CNTL DD *                                               
  INREC OVERLAY=(333:93,5,338:105,4,342:C'VV')               
/*                                                           
//CTL3CNTL DD *                                               
  OUTFIL FNAMES=OUT,OMIT=(342,2,CH,EQ,C'BB'),                 
    BUILD=(1,332)                                             
/*                                                           
//*                                                           


Spool:

Code:
ICE751I 1 EF-K10929 F0-Q84357 E8-K11698                                       
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 10720, TYPE = FB                       
ICE171I 0 SORTOUT LRECL OF 80 IS DIFFERENT FROM SORTIN(NN) LRECL OF 332 - RC=0
ICE055I 0 INSERT 0, DELETE 0                                                 

ICE162I 0 ICEIEXIT CHANGED ONE OR MORE OPTIONS IN EFFECT             
ICE027A 1 END OF          FIELD BEYOND MAXIMUM RECORD LENGTH         
ICE751I 0 C5-K90007 C6-K90007 C7-K90000 C8-K90007 E4-K90007 E7-K11698


Regards,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Mar 06, 2008 11:57 pm
 Reply with quote
It appears that your System Programmers have changed DFSORT's shipped default of SOLRF=YES to SOLRF=NO. This is NOT recommended.

You can do an override for your job using:

Code:

//DFSPARM DD *
   OPTION SOLRF
/*


But unless you can get your System Programmers to change back to the shipped default of SOLRF=YES, you and other people at your shop may run into this problem for other jobs and have to use the override for each of them.
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 12:08 am
 Reply with quote
Frank,

Truly you are the best.

Now its working. But it is getting sorted on the matching key field.
Can I have the output as it was in the input file?

Regards,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Fri Mar 07, 2008 12:31 am
 Reply with quote
Quote:
But it is getting sorted on the matching key field.


Well, that's what all of your examples showed. If you wanted records in their original order, why didn't you show that in your example?

Here's a revised DFSORT/ICETOOL job for that:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/332)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/332)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(333,5,CH) ON(338,4,CH) -
 KEEPNODUPS KEEPBASE -
 WITHALL WITH(1,92) WITH(99,240) WITH(342,1) WITH(344,8) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(93:6,6,333:1,5,338:13,4,342:C'BB',344:8X)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(333:93,5,338:105,4,342:C'VV',344:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,OMIT=(342,2,CH,EQ,C'BB')
/*
//CTL4CNTL DD *
  SORT FIELDS=(344,8,ZD,A)
  OUTREC BUILD=(1,332)
/*
//DFSPARM DD *
  OPTION SOLRF
/*
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 12:46 am
 Reply with quote
Frank,

I tried as follows

Code:
//S1    EXEC  PGM=ICETOOL                                           
//TOOLMSG DD SYSOUT=*                                               
//DFSMSG  DD SYSOUT=*                                               
//IN1 DD DSN=TBIS.IM.STKCD.BIVXE.INPUT1,DISP=SHR                     
//IN2 DD DSN=TBIS.IM.STKCD.MAPPING.OUT1,DISP=SHR                     
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)       
//OUT DD DSN=TBIS.IM.STKCD.MAPPING.BIVXE.FRK,                       
//       DISP=(OLD,CATLG,CATLG),UNIT=(DISK,5),                       
//       SPACE=(CYL,(10,10),RLSE),                                   
//       DCB=(RECFM=FB,LRECL=332,BLKSIZE=10624)                     
//TOOLIN DD *                                                       
COPY FROM(IN2) TO(T1) USING(CTL1)                                   
COPY FROM(IN1) TO(T1) USING(CTL2)                                   
SPLICE FROM(T1) TO(T2) ON(333,5,CH) ON(338,4,CH) -                   
 KEEPNODUPS KEEPBASE -                                               
 WITHALL WITH(1,92) WITH(99,240) WITH(342,1) WITH(344,8) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)                                   
/*                                                                   
//CTL1CNTL DD *                                                     
  INREC BUILD=(93:6,6,333:1,5,338:13,4,342:C'BB',344:8X)             
/*                                                                   
//CTL2CNTL DD *                                                     
  INREC OVERLAY=(333:93,5,338:105,4,342:C'VV',344:SEQNUM,8,ZD)       
/*                                                                   
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=T2,OMIT=(342,2,CH,EQ,C'BB')                         
/*                                                                   
//CTL4CNTL DD *                                                     
  SORT FIELDS=(344,8,ZD,A)                                           
  OUTREC BUILD=(1,332)                                               
/*                                                                   
//DFSPARM DD *                                                       
  OPTION SOLRF                                                       
/*                                                                   
//*                                                                                                             


But Getting RC=12

Code:
1 EF-K10929 F0-Q84357 E8-K11698                                       
0 OUTPUT LRECL = 351, BLKSIZE = 351, TYPE = FB                         
0 SORTOUT LRECL OF 351 IS DIFFERENT FROM SORTIN(NN) LRECL OF 332 - RC=0
0 INSERT 0, DELETE 0                                                   
0 RECORDS - IN: 10, OUT: 10                                           


Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 12:50 am
 Reply with quote
Sorry,

I missed T2..

It is now working fine.

Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 1:02 am
 Reply with quote
Frank,

It is really a great help for me and my team.

Just one last question
Where should we take care if File1 is of VB(332) and File2 is of FB(80).
The output should be VB i.e same as File1.

Regards,
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 1:25 am
 Reply with quote
Input1:
VB
LRECL:216
BLKSIZE:10796

Code:
--+----9----+---
****************
00001       LEQU
00038       LEQU
00028       LEQU
00040       LEQU
00041       LEQU


Input2:
FB
LRECL:80
BLKSIZE:10720

Code:
----+----1----+-
****************
00001B00026BUQSE
00028B00028BLEQU
00038B00038BLEQU
00040B00040BLEQS
00041B00041BLEQU
00042B00042BUQSE


Match (83,5) + (95,4) of input1 with (1,5)+(13,4) of input2.
If Matches replace (83,6) of Input1 with (6,6) of input2 and write in output.

Output
VB
LRECL:216
BLKSIZE:10796
The Out put should be in same order as it was in input1.

Code:
--+----9----+---
****************
00001       LEQU
B00038      LEQU
B00028      LEQU
00040       LEQU
B00041      LEQU


Regards,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Fri Mar 07, 2008 1:45 am
 Reply with quote
Quote:
Match (83,5) + (95,4)


Does the 83 and 95 count the RDW in positions 1-4, or is it really 87 and 99 when the RDW is counted?

Are there "short" VB records that don't have all of the key bytes? If so, what do you want to do about those?
Back to top
View user's profile Send private message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad
PostPosted: Fri Mar 07, 2008 1:37 pm
 Reply with quote
Franks,

Your soultion had really helped us to save our time and cost.

Quote:
Does the 83 and 95 count the RDW in positions 1-4, or is it really 87 and 99 when the RDW is counted?


No it it doesn't count the RDW position. it is really 87 and 99 when the RDW is counted.


Quote:
Are there "short" VB records that don't have all of the key bytes? If so, what do you want to do about those?


No All records have key bytes.

Regards,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Sat Mar 08, 2008 12:12 am
 Reply with quote
Here's a DFSORT/ICETOOL job for your VB example:

Code:

//VB    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/216)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=235
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (VB/216)
//TOOLIN DD *
COPY FROM(IN2) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(5,5,CH) ON(10,4,CH) -
 KEEPNODUPS KEEPBASE VLENOVLY -
 WITHALL WITH(14,1) WITH(16,8) WITH(1,105) WITH(112,124) -
 USING(CTL3)
 SORT FROM(T2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,FTOV,
    BUILD=(1:1,5,6:13,4,10:C'BB',12:8X,102:6,6)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,5:87,5,10:99,4,14:C'VV',16:SEQNUM,8,ZD,24:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,OMIT=(14,2,CH,EQ,C'BB')
/*
//CTL4CNTL DD *
  SORT FIELDS=(16,8,ZD,A)
  OUTREC BUILD=(1,4,5:24)
/*
//DFSPARM DD *
  OPTION SOLRF
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 3
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us