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

Query on using SPLICE when there are duplicates on main file
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Parthasarathy

New User


Joined: 24 Feb 2006
Posts: 18
Location: Chennai
PostPosted: Fri May 08, 2009 1:52 pm
 Reply with quote
Hi

I have a main file with duplicates on Key field and a lookup file with unique values on key
When I use SPLICE to compare these two files based on key and tried to assign a field from lookup file to main file in the output, I'm facing the following problem.

The unique keys in main file are spliced as exected.
The duplicates in main file which has a match in lookup file are spliced as expected.
But the duplicates in main file which do not have a match in lookup file are spliced together and the results are not valid. I need these record as-is in the output file.

Can anyone please help me with a logic and please let me know if there are any similar posts( I'm unable to find a similar one).

Thanks in advance
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Fri May 08, 2009 2:43 pm
 Reply with quote
Please post the control statements being used.
Back to top
View user's profile Send private message
Parthasarathy

New User


Joined: 24 Feb 2006
Posts: 18
Location: Chennai
PostPosted: Fri May 08, 2009 3:01 pm
 Reply with quote
Please find the control statements
IN1 is my lookup file and IN2 is my main file. All files are 276 LRECL FB.

Please note that I'm facing problem only with the records in IN2 having duplicate key and not finding a matching key in IN1. I want those record in output as-is.

Code:
//TOOLIN  DD *                                     
 COPY FROM(IN1) TO(T1) USING(CTL1)                 
 COPY FROM(IN2) TO(T1) USING(CTL2)                 
 SPLICE FROM(T1) TO(T2) ON(201,5,CH) ON(23,9,CH) -
     WITHALL WITH(1,246) WITH(252,26) KEEPNODUPS   
 COPY FROM(T2) TO(OUT) USING(CTL3)                 
/*                                                 
//CTL1CNTL DD *                                   
  INREC FIELDS=(1,246,50,5,252,25,C'BB')           
  SORT FIELDS=(201,5,CH,A,23,9,CH,A)               
  SUM FIELDS=NONE                                 
/*                                                 
//CTL2CNTL DD *                                   
  INREC FIELDS=(1,276,C'VV')                       
/*                                                 
//CTL3CNTL DD *                                   
* INCLUDE COND=(277,1,CH,EQ,C'V')                 
  OUTREC FIELDS=(1,276)                           
/* 


Thanks
Partha
Parthasarathy Govindaraj
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 May 08, 2009 9:40 pm
 Reply with quote
Please show an example of the records in each input file for all cases (relevant fields only) and the expected output. Give the starting position, length and format for all relevant fields.
Back to top
View user's profile Send private message
Parthasarathy

New User


Joined: 24 Feb 2006
Posts: 18
Location: Chennai
PostPosted: Mon May 11, 2009 9:30 am
 Reply with quote
For a better readability I have modified the input files for the same requirement with few required fields only.

Files OUT, IN1 and IN2 have recl 50 Bytes, FB
IN1 lookup file layout
City - 10 Bytes
State - 11Bytes
Filler - 29 Bytes

IN2 Main file layout
SNO - 3 Bytes
Name - 10 Bytes
City - 10 Bytes
Filler - 27 Bytes

OUT file layout
SNO - 3 Bytes
Name - 10 Bytes
City - 10 Bytes
State - 11 Bytes
Filler - 16 Bytes

Please find the code I'm using

Code:

//S1      EXEC PGM=ICETOOL                                       
//TOOLMSG DD SYSOUT=*                                             
//DFSMSG  DD SYSOUT=*                                             
//IN1     DD *                                                   
----+----1----+----2----+----3----+----4----+----5----+----6----+-
KOLKATTA  WESTBENGAL                                             
BANGALORE KARNATAKA                                               
MUMBAI    MAHARASHTRA                                             
CHENNAI   TAMILNADU                                               
//IN2     DD *                                                   
----+----1----+----2----+----3----+----4----+----5----+----6----+-
101VEERA     CHENNAI                                             
102PARTHA    MUMBAI                                               
103BALA      MUMBAI                                               
104SATISH    KOLKATTA                                             
105NIRMAL    KOLKATTA                                             
106NITA      TRIVANDRUM                                           
107STEVE     TRIVANDRUM                                           
//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 SYSOUT=*                                             
//TOOLIN  DD *                                                   
 COPY FROM(IN1) TO(T1) USING(CTL1)           
 COPY FROM(IN2) TO(T1) USING(CTL2)           
 SPLICE FROM(T1) TO(T2) ON(11,10,CH) -       
   WITHALL WITH(1,10) WITH(51,1) KEEPNODUPS 
 COPY FROM(T2) TO(OUT) USING(CTL3)           
/*                                           
//CTL1CNTL DD *                             
  INREC FIELDS=(13X,1,37,C'BB')             
/*                                           
//CTL2CNTL DD *                             
  INREC FIELDS=(1,50,C'VV')                 
/*                                           
//CTL3CNTL DD *                             
  INCLUDE COND=(51,1,CH,EQ,C'V')             
  OUTREC FIELDS=(1,50)                       
/*                                           


After executing this code I'm getting the following output
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+-
101VEERA     CHENNAI   TAMILNADU 
104SATISH    KOLKATTA  WESTBENGAL
105NIRMAL    KOLKATTA  WESTBENGAL
102PARTHA    MUMBAI    MAHARASHTRA
103BALA      MUMBAI    MAHARASHTRA
107STEVE     TRIVANDRUM


But the expected output is as follows

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+-
101VEERA     CHENNAI   TAMILNADU 
104SATISH    KOLKATTA  WESTBENGAL
105NIRMAL    KOLKATTA  WESTBENGAL
102PARTHA    MUMBAI    MAHARASHTRA
103BALA      MUMBAI    MAHARASHTRA
106NITA      TRIVANDRUM
107STEVE     TRIVANDRUM


I found that the duplicate records in IN2 file which is "TRIVANDRUM " which do not find a match with IN1 is spliced each other I'm getting only one record in the output.

But my requirement is to keep the Non matching IN2 records as-is in the output file.

Could you please help me with a solution?
Back to top
View user's profile Send private message
Parthasarathy

New User


Joined: 24 Feb 2006
Posts: 18
Location: Chennai
PostPosted: Mon May 11, 2009 12:58 pm
 Reply with quote
Hi Yaeger

After adding the KEEPBASE keyword to the SPLICE statement, I got the expected results.

Code:
SPLICE FROM(T1) TO(T2) ON(11,10,CH) -       
   WITHALL WITH(1,10) WITH(51,1) KEEPNODUPS KEEPBASE
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 Compare 2 files and retrive records f... DFSORT/ICETOOL 2
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
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
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