Multiple spliced records for each match in 2 files

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

Moderators: Frank Yaeger, Moderator Group

Post Reply
nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Multiple spliced records for each match in 2 files

Post by nachi » Fri Jun 12, 2009 9:06 pm

I wish to write multiple records by matching 2 files which has the same key. For example,

IN1

Code: Select all

NACHI MAT
FRANK MAT
IN2

Code: Select all

MAT STG
MAT SAS
I want the output to look like

Code: Select all

NACHI MAT STG
NACHI MAT SAS
FRANK MAT STG
FRANK MAT SAS
Read the first record, and find for the key in pos (7,3) is in (1,3) of file 2. If there are multiple records in FILE2 for the same key, write the base record that many times, and append with chars at (5,3) in overlay record.

I used the following SYSIN card

Code: Select all

//TOOLIN   DD *
  COPY FROM(IN1) TO(T1) USING(CTL1)
  COPY FROM(IN2) TO(T1) USING(CTL2)
  SPLICE FROM(T1) TO(OUT) ON(7,3,CH) -
  KEEPNODUPS WITHALL WITH(11,3) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTREC BUILD=(1,9,10:C'B')
/*
//CTL2CNTL DD *
  OUTREC BUILD=(7:1,3,10:C'V',11:5,3)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OUTREC=(1,14)
/*
IN1, IN2 is FB,LRECL=9
OUT is FB,LRECL=14

Can you please help me in this regard? Please let me know if you have any questions

Thanks,
Nachi

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Fri Jun 12, 2009 9:09 pm

I missed to inform you what the outcome after using the above SYSIN card
Here it is

NACHI MATB....
NACHI MATBSTG.
NACHI MATBSAS.

Thanks,
Nachi

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Mon Jun 15, 2009 11:49 pm

Frank,

Did you had a chance to look at my question?

Thanks,
Nachi

Alissa Margulies
Member
Posts: 25
Joined: Tue Apr 28, 2009 10:53 pm
Location: USA
Contact:

Post by Alissa Margulies » Tue Jun 16, 2009 10:02 pm

Here is a SyncSort for z/OS job that will produce your requested output:

Code: Select all

//STEP1  EXEC PGM=SORT
//SORTJNF1 DD *
NACHI MAT 
FRANK MAT       
//SORTJNF2 DD *
MAT STG 
MAT SAS       
//SORTOUT  DD SYSOUT=*
//SYSOUT   DD SYSOUT=* 
//SYSIN    DD *
   JOINKEYS FILES=F1,FIELDS=(7,3,A) 
   JOINKEYS FILES=F2,FIELDS=(1,3,A) 
   REFORMAT FIELDS=(F1:1,10,F2:5,3) 
   SORT FIELDS=COPY                 
/*
Alissa Margulies
SyncSort Mainframe Product Services
zos_tech@syncsort.com
201-930-8260

skolusu
Member
Posts: 43
Joined: Sat Jul 26, 2008 12:38 am

Post by skolusu » Wed Jun 17, 2009 6:18 am

nachi,

Your both input files have duplicates which when matched will be a Cartesian join. In order to perform such task you need a different approach. The following DFSORT/ICETOOL will give you desired results.

Code: Select all

//STEP0100 EXEC PGM=ICETOOL                                  
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN1      DD *                                              
MAT STG                                                      
MAT SAS                                                      
PAT PUS                                                      
PAT PUS                                                      
//IN2      DD *                                              
NACHI MAT                                                    
FRANK MAT                                                    
XXXXX DAT                                                    
YYYYY DAT                                                    
//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(15,11,CH) -                            
  WITH(1,10) WITHALL USING(CTL3)                                    
//CTL1CNTL DD *                                                     
  OPTION EQUALS                                                     
  SORT FIELDS=(1,3,CH,A)                                            
  OUTREC IFTHEN=(WHEN=INIT,                                         
  BUILD=(1,9,SEQNUM,8,ZD,RESTART=(1,3),SEQNUM,8,ZD,RESTART=(1,3))), 
  IFTHEN=(WHEN=(10,8,ZD,LE,1),OVERLAY=(25:C'2'))                    
                                                                    
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,NODETAIL,BUILD=(80X),             
  TRAILER1=(' OPTION EQUALS',/,                                     
            ' SORT FIELDS=(7,3,CH,A)',/,                            
            ' OUTREC OVERLAY=(10:SEQNUM,8,ZD,RESTART=(7,3))',/,     
            ' OUTFIL FNAMES=T1,',/,                                 
            ' BUILD=(1,9,5X,7,3,SEQNUM,8,ZD,RESTART=(7,11)),',/,    
            ' REPEAT=',MAX=(18,8,ZD,M11,LENGTH=8))                  
                                                                    
  OUTFIL FNAMES=T1,BUILD=(11:5,3,15:1,3,10,8)                       
/*
//CTL2CNTL DD DSN=&&C1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)          
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,OMIT=(10,3,CH,EQ,C' '),BUILD=(1,14)              
/*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Wed Jun 17, 2009 10:18 pm

That is exactly what I wanted. It took a while for me to understand the control cards, but when I got that, it looks great.

I am getting the expected results. I customized the control cards to my need. Thanks for all your help!

Thanks,
Nachi

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Thu Jun 18, 2009 9:04 pm

In this same context, my input is 14 million rows and I have 25 max duplicate keys. This join multiplies 14 million with 25 which will be a huge number of rows in the temp. I tried running it but with no surprise my SORT job went down due to size limitations. Is this is the only solution for this scenario?

As a workaround, I have split the files into 14 parts, 1 million each and started working with 25 million rows. Each job is taking atleast 20 minutes to run.

The simplest way I can think of is writing a COBOL program with Internal table but that is definitely going to consume atleast 8-16 hrs for development.

Please let me know your thoughts

Thanks,
Nachi

skolusu
Member
Posts: 43
Joined: Sat Jul 26, 2008 12:38 am

Post by skolusu » Fri Jun 19, 2009 8:46 pm

Nachi,

I sent you an offline solution for this problem. Let me know how it worked.

Thanks
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Fri Jun 26, 2009 1:29 am

Kolusu,

That is an awesome solution. It is so quick and I got expected results in a short while.

I apologize for the delay in reply. But it saved me a lot of time as I was in the midst of implementation and had to come up with quick solution.

Thanks a lot for all your help!

Thanks,
Nachi

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Fri Jun 26, 2009 10:40 am

Hi Kolusu,

Please share that solution with the board, it will be help ful to the board members.

Thanks.
Natarajan
Chennai

skolusu
Member
Posts: 43
Joined: Sat Jul 26, 2008 12:38 am

Post by skolusu » Fri Jun 26, 2009 8:36 pm

Natarajan,

It is just a File matching solution using cobol, very easy to write.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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