Join through splice

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

Moderators: Frank Yaeger, Moderator Group

Post Reply
pardeep mittal
Member
Posts: 1
Joined: Fri Mar 05, 2010 6:29 pm

Join through splice

Post by pardeep mittal » Fri Mar 05, 2010 6:50 pm

Hi,

I want to Join two files with some records from 1st and all from 2nd.i am using following JCL code

//SPLICE EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=A70528.SAMPLE,DISP=SHR
//IN2 DD DSN=A70528.SAMPLE1,DISP=SHR
//*IN1 DD DSN=A70528.SAMPLE3,DISP=SHR
//*IN2 DD DSN=A70528.SAMPLE4,DISP=SHR
//T1 DD DSN=A70528.SAMPLE5,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=26,BLKSIZE=0,DSORG=PS)
//T2 DD DSN=A70528.SAMPLE6,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=26,BLKSIZE=0,DSORG=PS)
//TEMP2 DD SYSOUT=*
//CONCAT DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
// DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//T3 DD DSN=A70528.JOIN.SPLICE.TEST,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=26,BLKSIZE=0,DSORG=PS)
//*DSN=E001.COLONIAL.CIF.NOREL.RPT,DISP=SHR
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(CONCAT) TO(T3) ON(1,4,CH) -
WITHALL WITH(16,10) USING(CTL3)
/*
//CTL1CNTL DD *
SORT FIELDS=COPY
OUTREC FIELDS=(1,15,16:10C' ',26:C'A')
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,4,5:11C' ',5,10,26:C'B')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=T3,
OUTREC=(1,26)
/*
//


input file a70528.sample is

1111 pardeep
2222 mittal
3333 naveen
4444 sachin
6666 rahul

input file a70528.sample is

1111 abcd
2222 xyz
2222 ksdf
3333 ksdfjh
5555 testone

Output by using above jcl is:

1111 pardeep abcd A
2222 mittal xyz A
2222 mittal ksdf A
3333 naveen ksdfjh A

means it is giving only matched records only but i want unmatched records from sample1 also.Please tell me a way to do it....

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

Post by Frank Yaeger » Fri Mar 05, 2010 10:08 pm

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
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

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

Post by Alissa Margulies » Fri Apr 16, 2010 1:40 am

Here is a sample SORT job that may give you the desired results:

Code: Select all

//SORT1  EXEC PGM=SORT                    
//SORTJNF1 DD *                          
1111 PARDEEP                             
2222 MITTAL                              
3333 NAVEEN                              
4444 SACHIN                              
6666 RAHUL                               
//SORTJNF2 DD *                          
1111 ABCD                                
2222 XYZ                                 
2222 KSDF                                
3333 KSDFJH                              
5555 TESTONE                             
//SORTOUT  DD SYSOUT=*                    
//SYSOUT   DD SYSOUT=*                     
//SYSIN    DD *                               
   JOINKEYS FILES=F1,FIELDS=(1,4,A)        
   JOINKEYS FILES=F2,FIELDS=(1,4,A)        
   JOIN UNPAIRED,F2                        
   REFORMAT FIELDS=(F2:1,4,F1:5,8,F2:5,8)  
   SORT FIELDS=COPY                        
/*                                         
Here is the output produced:

Code: Select all

1111 PARDEEP ABCD    
2222 MITTAL  XYZ     
2222 MITTAL  KSDF    
3333 NAVEEN  KSDFJH  
5555         TESTONE 
If this is not what you want, please provide the information previously requested.
Alissa Margulies
SyncSort Mainframe Product Services
zos_tech@syncsort.com
201-930-8260

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