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
 

 

merge files with duplicate in both the files...

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Feb 05, 2009 3:12 pm    Post subject: merge files with duplicate in both the files...
Reply with quote

Input1:
Code:

KEY1TEXTA
KEY1TEXTX
KEY2TEXTB
KEY2TEXTY
KEY3TEXTC

Input2:
Code:

KEY1INFOX
KEY1INFOY
KEY1INFOZ
KEY1INFOP
KEY2INFOR
KEY2INFOS
KEY3INFOA


Output required:
Code:

KEY1TEXTAINFOX
KEY1TEXTAINFOY
KEY1TEXTAINFOZ
KEY1TEXTAINFOP
KEY1TEXTXINFOX
KEY1TEXTXINFOY
KEY1TEXTXINFOZ
KEY1TEXTXINFOP
KEY2TEXTBINFOR
KEY2TEXTBINFOS
KEY2TEXTYINFOR
KEY2TEXTYINFOS
KEY3TEXTCINFOA

product I am using is DFSORT.
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Feb 05, 2009 7:14 pm    Post subject:
Reply with quote

I searched in dfsort forum for it and it looks like this is the case of cartesian join.
And replies on such topics tells
cant be done using splice (dfsort) icon_sad.gif icon_sad.gif icon_sad.gif

what will be best suited to do so?
any thoughts/suggestions?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Feb 06, 2009 12:21 am    Post subject:
Reply with quote

Sambhaji,

There is a DFSORT solution for Cartesian join. I need the following details.

1. What is the LRECL and RECFM of input files
2. What is the position and format of the key to be matched
3. Which file has the max dups on the key?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Feb 06, 2009 11:52 am    Post subject:
Reply with quote

Thanks Kolusu for update.

Details you required are as follows

file1:
LRECL=45
RECFM=FB
max no of dups=55


file2:
LRECL=32
RECFM=FB
max no of dups=10
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Feb 06, 2009 10:33 pm    Post subject: Reply to: merge files with duplicate in both the files...
Reply with quote

Sambhaji,

Since you did not mention the key position and format of the key, I assumed that it is in the first 4 bytes in character form in both files.

The format of output file is

Code:

45 bytes of file 1 + 28 bytes of file2 from pos 5



Code:

//STEP0100 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD DSN=Your 45 byte lrecl file,DISP=SHR
//IN2      DD DSN=Your 32 byte lrecl file,DISP=SHR           
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)     
//OUT      DD SYSOUT=*
//TOOLIN   DD *                                                   
  SORT FROM(IN1) USING(CTL1)                                     
  SORT FROM(IN2) USING(CTL2)                                     
  SPLICE FROM(T1) TO(OUT) ON(1,4,CH) ON(74,8,CH) -               
  WITH(1,32) WITHALL USING(CTL3)                                 
//CTL1CNTL DD *                                                   
  OPTION EQUALS                                                   
  SORT FIELDS=(1,4,CH,A)                                         
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(46:SEQNUM,8,ZD,RESTART=(1,4),
                 SEQNUM,8,ZD,RESTART=(1,4))),                     
  IFTHEN=(WHEN=(46,8,ZD,LE,1),OVERLAY=(61:C'2'))                 
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,NODETAIL,BUILD=(80X),           
  TRAILER1=(' SORT FIELDS=(1,4,CH,A)',/,                         
            ' OUTREC OVERLAY=(33:SEQNUM,8,ZD,RESTART=(1,4))',/,   
            ' OUTFIL FNAMES=T1,',/,                               
            ' BUILD=(1,32,41X,SEQNUM,8,ZD,RESTART=(1,40)),',/,   
            ' REPEAT=',MAX=(54,8,ZD,M11,LENGTH=8))               
  OUTFIL FNAMES=T1,BUILD=(1,4,33:5,41,46,8)                       
//*                                                               
//CTL2CNTL DD DSN=&&C1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)       
//CTL3CNTL DD *                                                   
  OUTFIL FNAMES=OUT,BUILD=(1,4,33,41,5,28),                       
  OMIT=(5,28,CH,EQ,C' ',OR,33,41,CH,EQ,C' ')                     
//*
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Tue Feb 10, 2009 3:11 pm    Post subject:
Reply with quote

Hi Kolusu...
Thanks a lot...
I tried this solution for 10% of records then works fine but when i tried with actual file it is failing with EB37 on tmp file
I tried increasing space but still result was same.. icon_sad.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 11, 2009 2:17 am    Post subject:
Reply with quote

Hello,

Suggest you talk with your storage management people about your "special" requirement. . .

You mention the abend being related to the "tmp" file, but there is no tmp dd. . .
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Feb 11, 2009 4:13 am    Post subject:
Reply with quote

EB37 icon_question.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2284
Location: @my desk

PostPosted: Wed Feb 11, 2009 7:36 am    Post subject:
Reply with quote

Sambhaji,

The logic used here involves repeating 'n' times the records in file-2, where 'n' is the maximum number of duplicates in file-1 and storing it in T1. It would be better if you post the actual number of input records and the value of 'n' from your job run(Search for REPEAT in your DFSMSG output).
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Feb 11, 2009 11:59 am    Post subject:
Reply with quote

Dick wrote:

You mention the abend being related to the "tmp" file, but there is no tmp dd. . .

Its t1 here.

And am sorry its not eb37 it is SB37


Arun wrote:

if you post the actual number of input records and the value of 'n' from your job run(Search for REPEAT in your DFSMSG output).

repeat count is

REPEAT=00000069

Records in second file is 11979745
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Feb 11, 2009 12:08 pm    Post subject:
Reply with quote

Did you look up what an SB37 is? Did you look up the accompanying IEC030I message and the reason code?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Feb 11, 2009 10:14 pm    Post subject: Reply to: merge files with duplicate in both the files...
Reply with quote

Sambhaji,

Your input files are huge. I do not think the REPEAT solution is viable for this job as we are repeating every record 69 times. Your input count shows more than 11 million and the repeat solution would create roughly about 826 million records which is the reason for your SB37 errors.

We have an COBOL program for such requests. Please contact me via e-mail offline (skolusu@us.ibm.com). Make sure that you send me the following details

1. LRECL and RECFM of both files
2. Position of the key and format in both files
3. OUTPUT file layout ( what fileds do you need to pick from file 1 and file 2)
4. Do you also need the unmatched records from both files?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Feb 16, 2009 11:00 am    Post subject:
Reply with quote

Thanks Kolusu. I got it.
Working much faster than expected timings.. icon_smile.gif icon_smile.gif icon_smile.gif
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 getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm


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