Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 8617
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 query to fetch record which has only ... maxsubrat DB2 10 Mon Dec 11, 2017 5:03 pm
No new posts File Tailoring -- Temporary dataset i... samzee71 CLIST & REXX 3 Fri Dec 01, 2017 3:12 am
No new posts Rexx - File tailoring samzee71 CLIST & REXX 10 Thu Nov 30, 2017 10:47 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us