Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads


     
 
MAINFRAME - TIP OF THE DAY : All available storage is assigned to the job or jobstep, If REGION is coded 0K or 0M

Google
 
Web mainframegurukul.com

Merging & Joining REcords in Five Files

 
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities
  View previous topic :: View next topic  
Author Message
raulravi
Member


Joined: 18 Apr 2011
Posts: 47
Location: India

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Jun 23, 2011 1:31 pm    Post subject: Merging & Joining REcords in Five Files Reply with quote

Hi,

I have five different file, with Cust-Id & Date as Key for all.
When ever the Cust-ID & Date in all the five Files is same i have to join all the five records to one single record in the output file.

Eg: If the Cust -ID & date is available in all the files then i will have to merge all the records from these five files into a single record.

If the Cust-Id & Date are available in only file one & file 3 then merge only those two records as a single record, leaving the rest of the feilds as blanks.



Code:
File 1         
Cust-ID   Date   DOB        Acct Type
111   20110303   19840319   Current
111   20110202   19840319   Saving
111   20110101   19840319   Current
222   20110303   19840319   Current
222   20110202   19840319   Saving
222   20110101   19840319   Current

File 2         
Cust-ID   Date   Place   
111   20110202   London   
111   20110112   Manchestre   
222   20110202   London   




File 3      
Cust-id   Date   Dept
111   20110303   Fraud
111   20110112   security
222   20110303   Fraud

File 4      
Cust-ID   Date   Indicator
111   20110112   None
111   20110101   PEP
222   20110112   None
222   20110101   PEP

File 5      
Cust-ID   Date   Email   
111   20110112   Gmail   
111   20110101   Yahoo   
222   20110112   Gmail   
222   20110101   Yahoo   

Output                     
Cust-ID   Date   DOB        Acct Type   Place   Dept   Indicator   Email
111   20110303   19840319   Current             Fraud      
111   20110202   19840319   Saving      London         
111   20110112                          Manc    secur   None       Gmail
111   20110101   19840319   Current                      PEP       Yahoo
222   20110303   19840319   Current             Fraud      
222   20110202   19840319   Saving      London         
222   20110112                                           None       Gmail
222   20110101   19840319   Current                      PEP       Yahoo


PLease let me know if any thing is not clear.....
Regards,
Raul
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.

Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 812
Location: San Jose, CA

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Jun 23, 2011 9:28 pm    Post subject: Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for. Since you didn't give the RECFM and LRECL of your input files, or the starting position, length and format of each input and output field, I just guessed. Adjust as necessary.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD *
111     20110303        19840319        Current
111     20110202        19840319        Saving
111     20110101        19840319        Current
222     20110303        19840319        Current
222     20110202        19840319        Saving
222     20110101        19840319        Current
//IN2 DD *
111     20110202        London
111     20110112        Manchestre
222     20110202        London
//IN3 DD *
111     20110303        Fraud
111     20110112        security
222     20110303        Fraud
//IN4 DD *
111     20110112        None
111     20110101        PEP
222     20110112        None
222     20110101        PEP
//IN5 DD *
111     20110112        Gmail
111     20110101        Yahoo
222     20110112        Gmail
222     20110101        Yahoo
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
COPY FROM(IN4) TO(T1) USING(CTL4)
COPY FROM(IN5) TO(T1) USING(CTL5)
SPLICE FROM(T1) TO(OUT) ON(1,16,CH) KEEPNODUPS WITHANY -
  WITH(1,24) WITH(25,16) WITH(41,16) -
  WITH(57,16) WITH(73,16) WITH(89,16) WITH(105,16)-
  USING(CTL6)
//CTL1CNTL DD *
  INREC OVERLAY=(125:X)
//CTL2CNTL DD *
  INREC BUILD=(1,24,57:25,16,125:X)
//CTL3CNTL DD *
  INREC BUILD=(1,24,73:25,16,125:X)
//CTL4CNTL DD *
  INREC BUILD=(1,24,89:25,16,125:X)
//CTL5CNTL DD *
  INREC BUILD=(1,24,105:25,16,125:X)
//CTL6CNTL DD *
  SORT FIELDS=(1,8,CH,A,9,8,CH,D)
/*


SORTOUT would have:

Code:

111     20110303        19840319        Current                         Fraud
111     20110202        19840319        Saving          London
111     20110112                                        Manchestre      security        None            Gmail
111     20110101        19840319        Current                                         PEP             Yahoo
222     20110303        19840319        Current                         Fraud
222     20110202        19840319        Saving          London
222     20110112                                                                        None            Gmail
222     20110101        19840319        Current                                         PEP             Yahoo

_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
raulravi
Member


Joined: 18 Apr 2011
Posts: 47
Location: India

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Jul 27, 2011 5:52 pm    Post subject: Reply with quote

Hello Frank,

When i am running the below JOb. It is working

Code:
//S1       EXEC PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//IN1      DD DSN=TTYA.GONUGRK.CUSTPER.UNLOAD,DISP=SHR
//IN2      DD DSN=TTYA.GONUGRK.CUSTPER.UNLAD22,DISP=SHR
//OUT      DD DSN=TTYA.GONUGRK.CUSTPER.FINAL,DISP=MOD 
//TOOLIN   DD *                                       
COPY FROM(IN1) TO(OUT)                                 
COPY FROM(IN2) TO(OUT) USING(CTL1)                     
/*                                                     
//CTL1CNTL DD *                                       
  INREC BUILD=(1,19,26:20,282)                         
/*                                                     


But when i am trying to run the same job like this

Code:

//S1       EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD DSN=TTYA.GONUGRK.CUSTPER.UNLOAD,DISP=SHR                 
//IN2      DD DSN=TTYA.GONUGRK.CUSTPER.UNLAD22,DISP=SHR                 
//OUT      DD DSN=TTYA.GONUGRK.CUSTPER.FINAL,DISP=MOD                   
//TOOLIN   DD DSN=TTYA.GONUGRK.@SPP.SASDATA(RAUL1),DISP=SHR             
//CTL1CNTL DD DSN=TTYA.GONUGRK.@SPP.SASDATA(RAUL2),DISP=SHR             
/*

=============================================
Raul1 with content as below:
COPY FROM(IN1) TO(OUT)                           
COPY FROM(IN2) TO(OUT) USING(CTL1)                         

Raul2 with content as below:
INREC BUILD=(1:1,18,25:19,218,243:X,244:237,65)


I am getting the below error:

Code:
ICE630I 0 MODE IN EFFECT:  STOP                               
                                                             
            COPY FROM(IN1) TO(OUT) USING(CTL1)               
                                                             
ICE604A 0 ERROR IN KEYWORD, PARAMETER, OR DELIMITER           
ICE602I 0 OPERATION RETURN CODE:  12                         
                                                             
ICE630I 2 MODE IN EFFECT:  SCAN                               
                                                             
            COPY FROM(IN2) TO(OUT) USING(CTL2)               
                                                             
ICE604A 0 ERROR IN KEYWORD, PARAMETER, OR DELIMITER           
                                                             
                                                             
ICE601I 0 DFSORT ICETOOL UTILITY RUN ENDED - RETURN CODE:  12


Can you please let me know what went wrong.

Raul
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
DikDude
Moderator


Joined: 22 Jul 2011
Posts: 1001
Location: usa

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Jul 27, 2011 9:57 pm    Post subject: Reply with quote

Your error message info does not match the job you say you submitted. . .

Where is CTL2?

Suggest you use more consistent member names (ctl vrs raul). . .
_________________
Have a good one
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 812
Location: San Jose, CA

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Jul 27, 2011 10:32 pm    Post subject: Reply with quote

Raul,

If the ICE604A message marker is really pointing between TO(OUT) and USING(CTL1), then the character between them must not be a space (X'40'). Try typing a space over that character or look at it in hex.

Same for the second ICE604A message.

If that doesn't help, then please post the complete JES log for your run.
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
academyindia4
Guest





Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Feb 01, 2016 1:33 am    Post subject: Topic deleted by Admin Reply with quote

<< Content deleted By Admin >>
Back to top
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Display posts from previous:   
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities All times are GMT + 5 Hours
Page 1 of 1



 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Related topics
 Topics   Replies   Author   Views   Last Post 
No new posts What are all the COBOL compilers available? 6 Sweetu 14669 Mon Feb 01, 2016 10:48 pm
academyindia4 View latest post
No new posts Reg: Different operating systems in mainframes 2 156138 6808 Mon Feb 01, 2016 1:35 am
academyindia4 View latest post
No new posts compare 2 files with different LRECL 13 nina 17200 Mon Feb 01, 2016 1:34 am
academyindia4 View latest post
No new posts Different forms of Case -when (loop handling) in Eazytrieve 3 Avani 10392 Mon Feb 01, 2016 1:34 am
academyindia4 View latest post
No new posts Copy records for matching key appearing at different places. 4 rangab 7444 Tue Feb 03, 2009 11:36 pm
rangab View latest post
 


Cobol Tutorial
This cobol tutorial covers most of the important topics like STRING, UNSTRING, COMP, COMP-3.....
DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. Explains in simple language. Some Chapters are locked, Forum members have free access to these chapters
CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming...
JCL Tutorial
This is most popular JCL tutorial from mainframegurukul. It does contain important jcl ....
SORT Tutorial
This Tutorial covers all important aspects of DFSORT. Has more SORT examples
INTERVIEW Questions
This page covers important interview questions
Mainframe Jobs
Mainframe Jobs posted by members of mainframegurukul forum
FaceBook Page
MainframeGurukul FaceBook Page
LinkedIn Page
MainframeGurkul Linkedin Page

This widget requires Flash Player 9 or better







Go to top of the page
 

Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials || Mainframe CICS tutorial|| Mainframe COBOL Tutorial

Drona Educational Forums - Mainframe Cobol DB2 CICS Board
Powered by phpBB

mainframetopic-5856.html