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

Compare 2 files, Using 1 file as look-up file
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Tue Sep 23, 2008 4:23 pm
 Reply with quote
Hi,

I've a requirement. I want to replace 99 in File1 with Actual code from File2 by matching the Number field

File1 - has 2 fields Number(alphanumeric 19) and code(aphanumeric 3)

File 1

Number Code

B0000001ssssssssss 99s
V0000010ssssssssss 99s
L00000023sssssssss 99s

---> s is for spaces

File2

File2 - has same 2 fields but as shown below(difference is shown in red)


Number Code

sB0000001sssssssss 003
sV0000010sssssssss 004
sL00000023ssssssss 005


The output file should look like
Number --> should be picked from File1
Code --> should be picked from File2


Number Code

B0000001ssssssssss 003
V0000010ssssssssss 004
L00000023sssssssss 005


Can this be done using SORT, please advise.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Tue Sep 23, 2008 5:39 pm
 Reply with quote
File2 will not always have policies in the same order. Assume that File2 is not sorted and has to be looked up everytime.

For example:

File2 might be having records as shown below

Number Code

sB0000001sssssssss 003
sV0000010sssssssss 004
sL00000023ssssssss 005
1111111111111111111 100
4444444444444444444 200
01234567sssssssssss 300


The records have to match on the Number field
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: Tue Sep 23, 2008 9:37 pm
 Reply with quote
You can use a DFSORT/ICETOOL job like the following to do what you asked for:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD *
B0000001           99
V0000010           99
L00000023          99
/*
//IN2 DD *
 B0000001           003
 V0000010           004
 L00000023          005
1111111111111111111 100
4444444444444444444 200
01234567            300
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1)
COPY FROM(IN2) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(20,3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1:2,19,20:21,3)
/*
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Wed Sep 24, 2008 2:12 pm
 Reply with quote
Thanks Frank,

I modified the sort card a bit,

Code:

//ICETOOL1 EXEC PGM=ICETOOL
//TOOLMSG   DD SYSOUT=*   
//DFSMSG    DD SYSOUT=*   
//CIS07     DD *           
B0000001           99     
111111111111111111199     
L00000023          99     
V0000010           99     
444444444444444444499     
01234567           99     
/*                         
//RELFILE   DD *           
 B0000001          003                                               
 V0000010          004                                               
 L00000023         005                                               
1111111111111111111100                                               
4444444444444444444200                                               
01234567           300                                               
/*                                                                   
//T1        DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT       DD SYSOUT=*                                             
//TOOLIN DD *                                                       
COPY   FROM(CIS07)   TO(T1)                                         
COPY   FROM(RELFILE) TO(T1)  USING(CTL1)                             
SPLICE FROM(T1)      TO(OUT) ON(1,19,CH) WITH(20,3)                 
/*                               
//CTL1CNTL DD *                   
  INREC OVERLAY=(1:1,19,20:20,3) 
/*                               
//*



But the output looks like this,

Code:

********************************* TOP OF DATA *****
01234567           300                             
1111111111111111111100                             
4444444444444444444200                             
******************************** BOTTOM OF DATA ***


First three records got skipped because of space.
Please advise.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Wed Sep 24, 2008 2:32 pm
 Reply with quote
sorry, i'm posting again the jcl i used

Code:


//ICETOOL1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
B0000001           99 
111111111111111111199
L00000023          99
V0000010           99
444444444444444444499
01234567           99
/*
//IN2 DD *
 B0000001          003
 V0000010          004
 L00000023         005
1111111111111111111100
4444444444444444444200
01234567           300
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1)
COPY FROM(IN2) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(20,3)
/*
//CTL1CNTL DD *
INREC OVERLAY=(1:1,19,20:20,3)
/*
//*



The output looks like

Code:

01234567           300 
1111111111111111111100 
4444444444444444444200 



Cannot use (2,19)

//CTL1CNTL DD *
INREC OVERLAY=(1:2,19,20:21,3)
/*

As it'll truncate the first byte when the Number field does not start with space in the IN2 file. for ex:111111111111111.
Can we add a logic here to remove leading spaces in the Number field before a compare can be done ? Please advise.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Wed Sep 24, 2008 3:46 pm
 Reply with quote
Tried again with the below JCL and got the desired results.

Code:


//ICETOOL1 EXEC PGM=ICETOOL         
//TOOLMSG   DD SYSOUT=*             
//DFSMSG    DD SYSOUT=*             
//IN1     DD *                   
B0000001           99               
111111111111111111199               
L00000023          99               
01234567           99               
V0000010           99               
444444444444444444499               
/*                                 
//IN2   DD *                                                     
 B0000001          003                                               
 V0000010          004                                               
 L00000023         005                                               
1111111111111111111100                                               
4444444444444444444200                                               
01234567           300                                               
/*                                                                   
//T1        DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT       DD SYSOUT=*                                             
//TOOLIN DD *                                                       
COPY   FROM(IN1)   TO(T1)                                         
COPY   FROM(IN2)   TO(T1)  USING(CTL1)                             
SPLICE FROM(T1)    TO(OUT) ON(1,19,CH) WITH(20,3)                 
/*                                                                   
//CTL1CNTL DD *                           
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C' '),   
  BUILD=(2,18,1,1,20,3))                 
/*                                       



Output looks like

Code:


B0000001           003 
L00000023          005 
V0000010           004 
01234567           300 
1111111111111111111100 
4444444444444444444200 



Now i'm not sure that is this the efficient way to do this. Any inputs on this will be appreciated.

Approx records in IN1 file : 5,00,000
Approx records in IN2 file : 7,000,000
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Wed Sep 24, 2008 5:58 pm
 Reply with quote
I'm facing one more issue,

The Code field in the IN2 file is in the Binary Format s9(04) COMP and i'm trying to SPLICE it into Numeric (9(03) field.

It is showing me invalid decimal. Can somebody help in conversion.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Wed Sep 24, 2008 9:12 pm
 Reply with quote
I got around the conversion also,

Code:

//CTL1CNTL DD *                                             
  OUTREC IFTHEN=(WHEN=(1,1,CH,EQ,C' '),                     
   BUILD=(1:2,18,19:1,1,20:13X,33:20,2,BI,EDIT=(TTT),36:X)),
  IFTHEN=(WHEN=NONE,                                         
   BUILD=(1:1,19,20:13X,33:20,2,BI,EDIT=(TTT),36:X))         
/*                                                           


I'm using below code for SPLICE

Code:


//TOOLIN DD *
COPY FROM(IN1) TO(T1)
COPY FROM(IN2) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(20,3)


It is dropping out the duplicate records on 1,19.
I want to keep the duplicates as well.
Please advise.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Wed Sep 24, 2008 9:15 pm
 Reply with quote
Code:


//TOOLIN DD *
COPY FROM(IN1) TO(T1)
COPY FROM(IN2) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(20,3) KEEPNODUPS



Should do the trick
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Thu Sep 25, 2008 1:54 pm
 Reply with quote
thanks,

by using KEEPNODUPS all the records from IN2 file (which i'm using a a look up file) are also coming into the output

My requirement is to output all the records from IN1 file on (1, 19), keeping all the duplicate records only from IN1 file.

Please advise.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Thu Sep 25, 2008 2:42 pm
 Reply with quote
I think I misunderstood your requirement.

OK,

First thing first,

I didn't understand about your "S9(4) COMP" thing so i am leaving it out.

I modified the JCL that to do what you want with normal data (As per my understanding).

You can modify it as per your data.

Code:


//ICETOOL1 EXEC PGM=ICETOOL                             
//TOOLMSG   DD SYSOUT=*                                 
//DFSMSG    DD SYSOUT=*                                 
//IN1     DD *                                           
B0000001           99                                   
111111111111111111199                                   
L00000023          99                                   
01234567           99                                   
V0000010           99                                   
V0000010           99                                   
444444444444444444499                                   
/*                                                       
//IN2   DD *                                             
 B0000001          003                                   
 V0000010          004                                   
 L00000023         005                                   
1111111111111111111100                                   
4444444444444444444200                                   
01234567           300                                   
/*                                                       
//T1        DD DSN=&&temp,                   
//         UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD)       
//OUT       DD SYSOUT=*                                 
//TOOLIN DD *                                                   
 COPY   FROM(IN2)   TO(T1)  USING(CTL1)                         
 COPY   FROM(IN1)   TO(T1)                                     
 SPLICE FROM(T1)    TO(OUT) ON(1,19,CH) WITH(1,19) WITHALL     
/*                                                             
//CTL1CNTL DD *                                                 
    OUTREC IFTHEN=(WHEN=(1,1,CH,EQ,C' '),                       
     BUILD=(1:2,18,19:1,1,20:20,3)),                           
    IFTHEN=(WHEN=NONE,                                         
     BUILD=(1:1,19,20:20,3))                                   
/*                                                             



Try it and let me know if you have any issues.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Thu Sep 25, 2008 3:32 pm
 Reply with quote
hchinnam,

Thanks, But there is some problem with WITHALL.

Here is the JCL is used:

Code:


//ICETOOL1 EXEC PGM=ICETOOL
//TOOLMSG   DD SYSOUT=*     
//DFSMSG    DD SYSOUT=*     
//IN1       DD *           
B0000001           99       
111111111111111111199       
L00000023          99       
01234567           99       
B0000001           99       
V0000010           99       
01234567           99       
L00000023          99       
444444444444444444499       
/*                         
//IN2       DD *                                                     
 B0000001          003                                               
 V0000010          004                                               
 L00000023         005                                               
1111111111111111111100                                               
1114444444555555111101                                               
4444444444444444444200                                               
01234567           300                                               
/*                                                                   
//T1        DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT       DD SYSOUT=*                                             
//TOOLIN DD *                                                       
COPY   FROM(IN1)   TO(T1)                                           
COPY   FROM(IN2) TO(T1)  USING(CTL1)                                 
SPLICE FROM(T1)      TO(OUT) ON(1,19,CH) WITH(20,3) WITHALL         
/*                                                                   
//CTL1CNTL DD *                             
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C' '),       
  BUILD=(2,18,1,1,20,3))                     
/*                                           



Here is the output

Code:


B0000001           99     
B0000001           003     
L00000023          99     
L00000023          005     
V0000010           004     
01234567           99     
01234567           300     
1111111111111111111100     
4444444444444444444200     



The FIRST record for duplicates in the IN1 file are not picking the code field from IN2 file. Please advise.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Thu Sep 25, 2008 3:36 pm
 Reply with quote
You have to copy IN2 first an then IN1.
Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Thu Sep 25, 2008 3:42 pm
 Reply with quote
Did you mean as shown below

Code:


//TOOLIN DD *                                               
COPY   FROM(IN2) TO(T1)  USING(CTL1)                         
COPY   FROM(IN1)   TO(T1)                                   
SPLICE FROM(T1)      TO(OUT) ON(1,19,CH) WITH(20,3) WITHALL 
/*                                                           




The OUTPUT looks like :

Code:


B0000001           99 
B0000001           99 
L00000023          99 
L00000023          99 
V0000010           99 
01234567           99 
01234567           99 
111111111111111111199 
444444444444444444499 



The code field did not get converted.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Thu Sep 25, 2008 3:44 pm
 Reply with quote
leo_sangha wrote:


SPLICE FROM(T1) TO(OUT) ON(1,19,CH) WITH(20,3) WITHALL



It should be like this


Code:


 SPLICE FROM(T1)    TO(OUT) ON(1,19,CH) WITH(1,19) WITHALL   

Back to top
View user's profile Send private message
leo_sangha

New User


Joined: 11 Aug 2005
Posts: 85
Location: England
PostPosted: Thu Sep 25, 2008 3:58 pm
 Reply with quote
perfect, its working.

output looks like:

Code:


B0000001           003
B0000001           003
L00000023          005
L00000023          005
V0000010           004
01234567           300
01234567           300
1111111111111111111100
4444444444444444444200



i guess i really need to dig into this utility.
Thanks for your help.
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 FTP VB File from Mainframe retaining ... JCL & VSAM 1
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
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