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
 
How to replicate rows n times based on the key

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

New User


Joined: 28 May 2007
Posts: 6
Location: Chennai

PostPosted: Sat May 02, 2009 8:23 am    Post subject: How to replicate rows n times based on the key
Reply with quote

Hi,
How to replicate rows n times based on the key fields, consider two files one having duplicate keys and another one having unique key. I wanted to replicate records in unique file as many records present for that particular key from the duplicate file.
For eg:
FileA
Key|Fields

AAA|01
AAA|04
AAA|06
BBB|03
CCC|01
CCC|03

FileB
Key|Fields

AAA|10
BBB|12
CCC|09

Output should like:
Key|Field2
AAA|10 (AAA has 3 recs in the FileA, hence need to replicate 3 times)
AAA|10
AAA|10
BBB|12 (BBB has only 1 record in FileA)
CCC|09 (CCC has 2 records in FileA)
CCC|09
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: Sat May 02, 2009 8:37 am    Post subject:
Reply with quote

Hello,

You have posted your question in the area for Job Vacancies. . . icon_confused.gif

In which part of the forum do you really want your topic located?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat May 02, 2009 11:23 am    Post subject:
Reply with quote

Hi Dick,

I would like to "move" this topic to JCL part of the Froum until we "discover" what sort of solution (COBOL, SyncSort or DFSORT) he is looking for; as on homepage having this post titled as "Latest News: How to replicate rows n times based on the key" seems funny . . .

Hopefully it's not a bother . . . icon_smile.gif

Ad
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: Sat May 02, 2009 12:53 pm    Post subject:
Reply with quote

Hi Anuj,

Not a bother icon_smile.gif

I'd hoped Imran would have replied much earlier and i'd have relocated the topic and removed my "guidance" post.
Back to top
View user's profile Send private message
Imrankhan

New User


Joined: 28 May 2007
Posts: 6
Location: Chennai

PostPosted: Sun May 03, 2009 8:29 am    Post subject: Reply to: How to replicate rows n times based on the key
Reply with quote

Hi All,
I wanted to place my query in JCL part using DFSORT. Actually i forgot to mention it in my question.. sorry for the inconvenience caused and as Dick mentioned it is for my Job requirement only. icon_razz.gif
Please kindly help me
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: Sun May 03, 2009 8:36 am    Post subject:
Reply with quote

Hi Imran,

Quote:
I wanted to place my query in JCL part using DFSORT
Please note that there is a JCL part of the forum (which is where Syncsort questions are placed) and there is a separate part of the forum for DFSORT & ICETOOL.

Your topic is now in the DFSORT part of the forum.

Quote:
sorry for the inconvenience caused and as Dick mentioned it is for my Job requirement only.
Not to worry, though careful reading of which topics go in which part of the forum will get you answers quicker. . .
Back to top
View user's profile Send private message
Imrankhan

New User


Joined: 28 May 2007
Posts: 6
Location: Chennai

PostPosted: Sun May 03, 2009 8:45 am    Post subject: Reply to: How to replicate rows n times based on the key
Reply with quote

Thank you so much Dick. From now on i'll post my query in correct Forum
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Mon May 04, 2009 9:33 pm    Post subject: Reply to: How to replicate rows n times based on the key
Reply with quote

isn't it a simple case of 2 file matching and overlaying the contents from fileb onto filea?

I assumed that your input is FB and lrecl of 80. The following DFSORT/ICETOOL jcl will give you the desired results. Make sure that you have disp=MOD for t1 dataset.

Also if there is a way to identify the records as to which file they belong and if both files are of the same LRECL, the same can be done with a single pass of data.

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//FILEA    DD *                                                     
AAA|01                                                               
AAA|04                                                               
AAA|06                                                               
BBB|03                                                               
CCC|01                                                               
CCC|03                                                               
//FILEB    DD *                                                     
AAA|10                                                               
BBB|12                                                               
CCC|09                                                               
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                     
  COPY FROM(FILEB) USING(CTL1)                                       
  COPY FROM(FILEA) USING(CTL2)                                       
  SORT FROM(T1) USING(CTL3)                                         
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(81:C'1')                                 
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(81:C'2')                                 
//CTL3CNTL DD *                                                     
  SORT FIELDS=(1,3,CH,A),EQUALS                                     
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,ZD,EQ,1),PUSH=(5:5,2,82:1,3))
  OUTFIL FNAMES=OUT,BUILD=(1,80),                                   
  INCLUDE=(81,1,ZD,EQ,2,AND,82,3,CH,EQ,1,3,CH)                       
/*
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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am

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