Merging & Joining REcords in Five Files

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Merging & Joining REcords in Five Files

Post by raulravi » Thu Jun 23, 2011 1:31 pm

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: Select all

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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Jun 23, 2011 9:28 pm

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: Select all

//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: Select all

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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Jul 27, 2011 5:52 pm

Hello Frank,

When i am running the below JOb. It is working

Code: Select all

//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: Select all

//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: Select all

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

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Jul 27, 2011 9:57 pm

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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Jul 27, 2011 10:32 pm

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

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Feb 01, 2016 1:33 am

<< Content deleted By Admin >>

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others