Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Compare a field of 2 files and replace it if matches
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Compare a field of 2 files and replace it if matches
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    Post subject:
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    Post subject: Reply
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Mar 05, 2008 10:21 pm    Post subject:
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    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Mar 06, 2008 11:20 pm    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Mar 06, 2008 11:57 pm    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Mar 07, 2008 12:31 am    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Mar 07, 2008 1:45 am    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 20 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us