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
 

 

Query on using SPLICE when there are duplicates on main file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Query on using SPLICE when there are duplicates on main file
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: 8593
Location: Back in jolly old England

PostPosted: Fri May 08, 2009 2:43 pm    Post subject:
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    Post subject:
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 Moderator


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

PostPosted: Fri May 08, 2009 9:40 pm    Post subject:
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    Post subject: Reply to: Query on using SPLICE when there are duplicates on
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Converting Variable block file to Fix... bhavana yalavarthi DFSORT/ICETOOL 10 Tue May 16, 2017 2:20 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts File processing vishwakotin Mainframe Interview Questions 9 Fri Apr 28, 2017 11:38 am
No new posts Occurrence of various strings within ... TS70363 JCL & VSAM 1 Thu Apr 27, 2017 8:18 pm


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