Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SYNCTOOL usage-Any replacement with DB2 LEFTJOIN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Girija

New User


Joined: 29 Jul 2005
Posts: 14
Location: Mysore

PostPosted: Mon Apr 16, 2007 5:34 pm    Post subject: SYNCTOOL usage-Any replacement with DB2 LEFTJOIN
Reply with quote

Hi all,

I have got the requirement to join two files.

File1 Data
----------
Id Name
----------------
100 Anthony
101 John
102 Test
103 Girija
104 Java
105 Cobol


File 2 data
----
Id Name
---------------
100 Anthony
101 John
104 Java
106 Test2
107 Govind
108 Tom

Output file should have data as follows

Key for search is ID.

Id Name
----------------
100 Anthony Anthony
101 John John
102 Test
103 Girija
104 Java Java
105 Cobol

If we have the matching data, then the data from file2 should concatenate with file1, otherwise only file1 should be written

I have use SYNCTOOL with SPLICE option, but only matching records are writing. Is there any wany so that we can write file 1 data alone even if don't get matching

I have use the logic as follows:

Code:

//KILLRR   EXEC PGM=DSKILLER,PARM='&TRS.SEQ.TEST1.AU2100.OUT12'   
//KILLSS   EXEC PGM=DSKILLER,PARM='&TRS.SEQ.TEST1.AU2100.OUT11'   
//KILLTT   EXEC PGM=DSKILLER,PARM='&TRS.SEQ.TEST1.AU2100.OUT22'   
//KILLYY   EXEC PGM=DSKILLER,PARM='&TRS.SEQ.TEST1.AU2100.TEMP01'   
//*                                                               
//ICETOOL1 EXEC PGM=SYNCTOOL
//SYSPRINT DD  SYSOUT=*                                           
//SYSOUT   DD  SYSOUT=*                                           
//*                                                               
//TOOLMSG  DD  SYSOUT=*                                           
//DFSMSG   DD  SYSOUT=*                                           
//IN1      DD  DSN=&TRS.SEQ.AU.MASTER.VEND,DISP=SHR               
//IN2      DD  DSN=&TRS.SEQ.AU.AU2100.OUTREC.SORT,DISP=SHR         
//OUT12    DD  DSN=&TRS.SEQ.TEST1.AU2100.OUT12,                   
//             DISP=(,CATLG,DELETE),                               
//             SPACE=(CYL,(50,50),RLSE),                           
//             DCB=(RECFM=FB,LRECL=335)                           
//OUT22    DD  DSN=&TRS.SEQ.TEST1.AU2100.OUT22,           
//             DISP=(,CATLG,DELETE),                       
//             SPACE=(CYL,(50,50),RLSE),                   
//             DCB=(RECFM=FB,LRECL=335)                   
//OUT11    DD  DSN=&TRS.SEQ.TEST1.AU2100.OUT11,           
//             DISP=(,CATLG,DELETE),                       
//             SPACE=(CYL,(50,50),RLSE),                   
//             DCB=(RECFM=FB,LRECL=335)                   
//T1       DD  DSN=&TRS.SEQ.TEST1.AU2100.TEMP01,           
//             DISP=(MOD,CATLG,DELETE),                   
//             SPACE=(CYL,(50,50),RLSE),                   
//             DCB=(RECFM=FB,LRECL=335)                   
//TOOLIN   DD  DSN=&TSP.PDS.SORTPARM(TPSORT24),DISP=SHR   
//CTL1CNTL DD  DSN=&TSP.PDS.SORTPARM(TPSORT21),DISP=SHR   
//CTL2CNTL DD  DSN=&TSP.PDS.SORTPARM(TPSORT22),DISP=SHR   
//CTL3CNTL DD  DSN=&TSP.PDS.SORTPARM(TPSORT23),DISP=SHR   
//*                                                       

TPSORT21-INREC OVERLAY=(333:C'11')   
TPSORT22-INREC OVERLAY=(333:C'22')   
TPSORT23-
---------------
OUTFIL FNAMES=OUT12,INCLUDE=(333,2,CH,EQ,C'12'),BUILD=(1,335)     
OUTFIL FNAMES=OUT11,INCLUDE=(333,2,CH,EQ,C'11'),BUILD=(1,335)     
OUTFIL FNAMES=OUT22,INCLUDE=(333,2,CH,EQ,C'22'),BUILD=(1,335)     
TPSORT24-
---------------
 COPY FROM(IN1) TO(T1) USING(CTL1)                               
 COPY FROM(IN2) TO(T1) USING(CTL2)                               
 SPLICE FROM(T1) TO(OUT12) ON(1,332,CH) WITH(333,2) USING(CTL3)-
 KEEPNODUPS                                                     


From this logic, I am getting the records in OUT11 file, which does not have matching records with In2, then finally

using sort and sort fields = copy, concatenating the OUT11 file with OUT12 but this flow seems to be lengthy.

Please let me know is there any way so that by using only SYNCTOOL/ICETOOL, can we get our requirement?
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Apr 16, 2007 5:41 pm    Post subject:
Reply with quote

What happens if there is a file 2 record with no file 1 record ?
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: Tue Apr 17, 2007 11:44 pm    Post subject:
Reply with quote

Hello,

SYNCTOOL and ICETOOL are entirely different products.

Does your site have both of those sort products?
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts Replacement FTP to SFTP Vasili Koipish All Other Mainframe Topics 1 Wed May 11, 2016 3:10 pm
No new posts How can amount of the Storage Usage b... esduman61 CICS 6 Wed Mar 09, 2016 1:19 pm
No new posts Controlling usage of resources by tasks raja1128 CICS 3 Fri Nov 06, 2015 9:53 am
No new posts TDQ & its usage as ATI akodakka CICS 3 Sat Oct 17, 2015 6:04 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us