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
 

 

Merge 2 files on one key using conditions for select
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Tue Dec 18, 2007 9:26 am    Post subject: Merge 2 files on one key using conditions for select
Reply with quote

Dear Listers,

Any pointers on how to achieve this in SORT.

System: MVS z/OS

Input File A: LRECL = 900 FB
Input File B: LRECL = 900 FB
Input File C: LRECL = 900 FB

Key 1 is from position 10 and is 4 characters
Key 2 is from position 890 and is 5 characters

File A :
Key1 Key 2

XXXX .... 12345
XXXX .... 12345
XXXX .... 12345
XXXX .... 88888
XXXX .... 88888
XXXX .... 99999
XXXX .... 99999
YYYY .... 12345
YYYY .... 12345
YYYY .... 12345
YYYY .... 55555
YYYY .... 55555
YYYY .... 77777
ZZZZ .... 44444

File B

XXXX .... 12345
YYYY .... 55555
ZZZZ .... 44444


File C

XXXX .... 12345
XXXX .... 12345
XXXX .... 12345
YYYY .... 55555
YYYY .... 55555
ZZZZ .... 44444

Appreciate the help
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Dec 18, 2007 9:28 am    Post subject:
Reply with quote

Ray,

Fromyour post, its not clear what you are looking for? Could you explain bit clearly.
Back to top
View user's profile Send private message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Tue Dec 18, 2007 9:43 am    Post subject:
Reply with quote

Hello Murali,

What I am trying to accomplish is the following:
Using File B as a driver on File A, whenever there is a match on Key1,
get ALL the values of Key2 from File A with the additional match
condition of Key 2 and write it out to File C

e.g. File B has the keys XXXX and 12345. File A is the super
set of the file B that has XXXX paired with values other than
12345. Without writing a program, I would like to write out to
a third file all the values of XXXX that have 12345 and so on

Hope this helps
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Dec 18, 2007 10:24 am    Post subject:
Reply with quote

Ray,

Try this -

Code:
//S@@ EXEC PGM=ICETOOL                                   
//DFSMSG DD SYSOUT=*                                     
//TOOLMSG DD SYSOUT=*                                     
//FILE1 DD FILE1....                                     
//FILE2 DD DRIVER-FILE.....                               
//T1 DD DSN=&&T1,DISP=(MOD,PASS)                         
//OUT DD SYSOUT=*                                         
//TOOLIN DD *                                             
 COPY FROM(FILE2) TO(T1) USING(CTL1)                     
 COPY FROM(FILE1) TO(T1) USING(CTL2)                     
 SPLICE FROM(T1) TO(OUT) ON(10,5,CH) ON(890,4,CH)       -
  WITH(902,1) KEEPNODUPS USING(CTL3) WITHALL             
/*                                                       
//CTL1CNTL DD *                                           
 INREC OVERLAY=(901:C'22')                               
/*                                                       
//CTL2CNTL DD *                                     
 INREC OVERLAY=(901:C'11')                         
/*                                                 
//CTL3CNTL DD *                                     
 OUTFIL FNAMES=OUT,OMIT=(901,2,CH,EQ,C'22',OR,     
                  901,2,CH,EQ,C'11'),               
   BUILD=(1,900)                                   
/*                                                 
Back to top
View user's profile Send private message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Tue Dec 18, 2007 11:13 am    Post subject:
Reply with quote

Hello Murali,

Thanks a bunch for your efforts.

I tried to execute the above and realized that we use SYNCSORT in our shop and do not have ICETOOL.

Is is not possible to use the SYNCSORT to achieve this. I understand that this is not a SYNCSORT list ...
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Dec 18, 2007 11:21 am    Post subject:
Reply with quote

Ray,

Just check whether the following suits your prupose.

http://www.ibmmainframes.com/viewtopic.php?t=26648&highlight=join
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Dec 18, 2007 10:27 pm    Post subject:
Reply with quote

Raymond Sachs

Try running Murli's JOB with SYNCTOOL instead of ICETOOL.
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Wed Dec 19, 2007 3:56 am    Post subject:
Reply with quote

Hi Ray.

Here is a SyncSort for z/OS 1.2 job that should give you the desired results:
Code:

//STEP1  EXEC PGM=SORT
//SORTJNF1 DD DSN=INPUT.FILE1
//SORTJNF2 DD DSN=INPUT.FILE2
//SORTOUT  DD DSN=MATCHED.RECORDS
//SYSOUT   DD SYSOUT=*
   JOINKEYS FILES=F1,FIELDS=(10,4,A,890,5,A)
   JOINKEYS FILES=F2,FIELDS=(10,4,A,890,5,A)
   REFORMAT FIELDS=(F1:1,900)
   SORT FIELDS=COPY
/*
Back to top
View user's profile Send private message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Wed Dec 19, 2007 8:26 am    Post subject:
Reply with quote

Hi Krisprems/Murali,

icon_biggrin.gif The solution worked. I used PGM=SYNCTOOL instead of PGM=SORT.

Well what is SYNCTOOL? I know that we have ICETOOL and SYNCSORT. Is it a new tool or a hybrid? Who supports SYNCTOOL.

I have yet to try out Syncsort support solution using JOINKEYS. Will do that later.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed Dec 19, 2007 8:40 am    Post subject:
Reply with quote

Ray,

ICETOOL is a DFSORT product (IBM). Whereas SYNCTOOL is SYNCSORT.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed Dec 19, 2007 8:48 am    Post subject:
Reply with quote

Addition to my prev post -

Quote:
Is it a new tool or a hybrid? Who supports SYNCTOOL

Both (ICE/SYNC)TOOL are extensions of their base product (DF/SYNC)SORT.

Go thru the following link to get an idea about syncsort and dfsort differences-

http://www.ibmmainframes.com/viewtopic.php?t=25349&highlight=syncsort
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Dec 20, 2007 2:24 am    Post subject:
Reply with quote

ICETOOL has been a fully supported, fully documented part of DFSORT since 1991 when I first developed it, and I've enhanced it many times since then with full documentation. For complete details on DFSORT's ICETOOL, see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CG20/3.0?DT=20060615173822

and

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.0?DT=20060615185603
Back to top
View user's profile Send private message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Tue Dec 25, 2007 6:39 am    Post subject:
Reply with quote

Hello Alissa,

When I executed with your cards (Using JOINKEYS), the output file had Zero records!

Ray[/img][/code]
Back to top
View user's profile Send private message
Aham

New User


Joined: 24 Oct 2007
Posts: 42
Location: chennai

PostPosted: Tue Dec 25, 2007 8:56 am    Post subject:
Reply with quote

Ray,
The job given by murali says (10,5,CH) and (890,4,CH) but your initial post at the top says 10,4 and 890,5. Sort job given alissa also says 10,4 and 890,5. If that is not working for you but job given by murali is working then probably your join keys are 10,5 and 890,4 is it? I had a very similar requirement and alissa's job worked fine for me.
Back to top
View user's profile Send private message
Raymond Sachs

New User


Joined: 13 Dec 2007
Posts: 45
Location: USA

PostPosted: Wed Dec 26, 2007 2:50 am    Post subject:
Reply with quote

Aham/Murali/Alissa

I made sure the Join keys are correct when I coded for either solutions. I re-examined the solution provided by Murali using SYNCTOOL and I found that it did not do what I had expected. I have tried to explain below what is going on:

Requirement:
File A :

Key1 other data Key 2

XXXX ABCDEFGH ... 12345
XXXX LMNOPQRS ... 12345
XXXX PQRTSUVS ... 12345
XXXX ABCDEFGH ... 88888
XXXX LMMMMMMI ... 88888
XXXX PQRTSUVS ... 99999
XXXX LMNOPQRS ... 99999
YYYY LLLLLLLLLLI ... 12345
YYYY MMMMMMM ... 12345
YYYY OOOOOOO ... 12345
YYYY PPPPPPPPP ... 55555
YYYY QQQQQQQ ... 55555
YYYY RRRRRRRR ... 77777
ZZZZ MMMMMMM ... 44444

File B

XXXX ABCDEFGH ... 12345
YYYY OOOOOOO ... 55555
ZZZZ MMMMMMM ... 44444

Desired Output:

File C

XXXX ABCDEFGH ... 12345
XXXX LMNOPQRS ... 12345
XXXX PQRTSUVS ... 12345
YYYY PPPPPPPPP ... 55555
YYYY QQQQQQQ ... 55555
ZZZZ MMMMMMM ... 44444


With Murali Solution: I get the following. (The Key 2 is picked correctlywith Key 1 and the number of rows are correct but I lose
the "other data". i.e. rec#2 should be LMNOPQRS and not
ABCDEFGH

XXXX ABCDEFGH ... 12345
XXXX ABCDEFGH ... 12345
XXXX ABCDEFGH ... 12345
YYYY PPPPPPPPP ... 55555
YYYY PPPPPPPPP ... 55555
ZZZZ MMMMMMM ... 44444


With Allissa's solution: I do not get any records.

I made sure the keys are coded correctly(key 1 = 4 char and Key 2 = 5 char)

Any pointers?
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Wed Dec 26, 2007 4:16 pm    Post subject:
Reply with quote

Assuming Files are LRECL=80, RECFM=FB, this is the SYNCTOOL solution. Change as per your requirement.
Code:
//*******************************************************
//STEP001  EXEC PGM=SYNCTOOL                             
//TOOLMSG  DD SYSOUT=*                                   
//DFSMSG   DD SYSOUT=*                                   
//IN1      DD *                                         
----+----1----+----2----+----3----+----4----+----5----+--
XXXX ABCDEFGH ... 12345                                 
XXXX LMNOPQRS ... 12345                                 
XXXX PQRTSUVS ... 12345                                 
XXXX ABCDEFGH ... 88888                                 
XXXX LMMMMMMI ... 88888                                 
XXXX PQRTSUVS ... 99999                                 
XXXX LMNOPQRS ... 99999                                 
YYYY LLLLLLLL ... 12345                                 
YYYY MMMMMMM  ... 12345                                 
YYYY OOOOOOO  ... 12345                                 
YYYY PPPPPPP  ... 55555                                 
YYYY QQQQQQQ  ... 55555                                 
YYYY RRRRRRR  ... 77777                                 
ZZZZ MMMMMMM  ... 44444                                               
/*                                                                   
//IN2      DD *                                                       
XXXX ABCDEFG  ... 12345                                               
YYYY OOOOOOO  ... 55555                                               
ZZZZ MMMMMMM  ... 44444                                               
/*                                                                   
//TMP1     DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//BOTH     DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
 COPY FROM(IN2)  TO(TMP1) USING(NKK2)                                 
 COPY FROM(IN1)  TO(TMP1) USING(NKK1)                                 
 SPLICE FROM(TMP1) TO(BOTH) ON(1,4,CH) WITH(82,1) WITH(6,12) -       
 ON(19,5,CH)  USING(NKK3) KEEPNODUPS WITHALL                         
/*                                                                   
//NKK2CNTL DD   *                                                     
  OUTREC OVERLAY=(81:C'11')                                           
/*                                                                   
//NKK1CNTL DD   *                                                     
  OUTREC OVERLAY=(81:C'22')                                 
/*                                                         
//NKK3CNTL DD   *                                           
  OUTFIL FNAMES=BOTH,INCLUDE=(81,2,CH,EQ,C'12'),BUILD=(1,80)
/*                                                         
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Wed Dec 26, 2007 4:21 pm    Post subject:
Reply with quote

observe my coding
Code:
WITH(6,12)
in the SPLICE statement.
Back to top
View user's profile Send private message
Aham

New User


Joined: 24 Oct 2007
Posts: 42
Location: chennai

PostPosted: Thu Dec 27, 2007 12:56 pm    Post subject:
Reply with quote

Alissa's code was missing the //SYSIN DD name. I have tested the below code for your input and it does give the desired output. You can use this
Code:

//STEP05      EXEC  PGM=SORT     
//SORTJNF1   DD  DSN=INPUT.FILE1,           
//           DISP=SHR                       
//SORTJNF2   DD  DSN=INPUT.FILE2,           
//           DISP=SHR                       
//SORTOUT    DD  DSN=MATCHED.RECORDS         
//SYSOUT   DD  SYSOUT=*                     
//SYSIN    DD  *                             
----+----1----+----2----+----3----+----4----+
  JOINKEYS FILES=F1,FIELDS=(10,4,A,890,5,A) 
  JOINKEYS FILES=F2,FIELDS=(10,4,A,890,5,A) 
  REFORMAT FIELDS=(F1:1,900)                 
  SORT FIELDS=COPY                           
/*   
Back to top
View user's profile Send private message
yogeshpawar

New User


Joined: 01 Mar 2006
Posts: 37
Location: Sussex-UK

PostPosted: Fri Dec 28, 2007 4:14 pm    Post subject: Reply to: Merge 2 files on one key using conditions for sele
Reply with quote

Kris,

Is SYNCTOOL exactly similar to ICETOOL in functioning or do we need to make any changes in the job to use SYNCTOOL instead of ICETOOl as mentioned above. (apart from mentioning SYNCSORT libraries in steplib as DFSORT is installed on my site)
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Fri Dec 28, 2007 5:48 pm    Post subject:
Reply with quote

yogeshpawar
There are certain differences b/w SYNCTOOL and ICETOOL
http://ibmmainframes.com/viewtopic.php?t=25349&highlight=syncsort+dfsort+difference

If DFSORT is installed in your site then you could not use SYNCTOOL. You should use ICETOOL only.

Check if both(DFSORT AND SYNCSORT) are installed if so , by pointing the STEPLIB/JOBLIB you can point to SYNCSORT library and use SYNCTOOL
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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