Merge 2 files on one key using conditions for select

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

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

murmohk1 · Posted: Tue Dec 18, 2007 9:28 am

Ray,

Fromyour post, its not clear what you are looking for? Could you explain bit clearly.

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

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

murmohk1 · Posted: Tue Dec 18, 2007 10:24 am

Ray,

Try this -

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

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 ...

murmohk1 · Posted: Tue Dec 18, 2007 11:21 am

Ray,

Just check whether the following suits your prupose.

www.ibmmainframes.com/viewtopic.php?t=26648&highlight=join

krisprems · Posted: Tue Dec 18, 2007 10:27 pm

Raymond Sachs

Try running Murli's JOB with SYNCTOOL instead of ICETOOL.

Alissa Margulies · Posted: Wed Dec 19, 2007 3:56 am

Hi Ray.

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

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

Hi Krisprems/Murali,

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.

murmohk1 · Posted: Wed Dec 19, 2007 8:40 am

Ray,

ICETOOL is a DFSORT product (IBM). Whereas SYNCTOOL is SYNCSORT.

murmohk1 · Posted: Wed Dec 19, 2007 8:48 am

Addition to my prev post -

Frank Yaeger · Posted: Thu Dec 20, 2007 2:24 am

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:

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

and

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.0?DT=20060615185603

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

Hello Alissa,

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

Ray[/img][/code]

Aham · New User Joined: 24 Oct 2007 Posts: 42 Location: chennai

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.

Raymond Sachs · New User Joined: 13 Dec 2007 Posts: 45 Location: USA

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?

krisprems · Posted: Wed Dec 26, 2007 4:16 pm

Assuming Files are LRECL=80, RECFM=FB, this is the SYNCTOOL solution. Change as per your requirement.

krisprems · Posted: Wed Dec 26, 2007 4:21 pm

observe my coding

Aham · New User Joined: 24 Oct 2007 Posts: 42 Location: chennai

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

yogeshpawar · New User Joined: 01 Mar 2006 Posts: 37 Location: Sussex-UK

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)

krisprems · Posted: Fri Dec 28, 2007 5:48 pm

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

yogeshpawar · New User Joined: 01 Mar 2006 Posts: 37 Location: Sussex-UK

Thanks for the quick reply Kris..

Frank Yaeger · Posted: Sat Dec 29, 2007 9:51 pm

yogeshpawar,

You seem to be confused whether to use DFSORT/ICETOOL or Syncsort/SYNCTOOL. To tell which one is your primary sort product at your site, look at the messages you get when you use PGM=SORT and PGM=ICETOOL:

PGM=SORT
DFSORT - ICE messages
Syncsort - WER messages

PGM=ICETOOL
DFSORT's ICETOOL - ICE messages
Syncsort's SYNCTOOL - SYT messages

If you have DFSORT, then you have DFSORT's ICETOOL and there would be no reason to use Syncsort's SYNCTOOL. DFSORT's ICETOOL is fully documented and fully supported (has been since 1991) and its function and documentation have been enhanced many times since then. SYNCTOOL is undocumented so if it did have any functions not found in ICETOOL, there would be no documentation of those functions. Likewise, there would be no documentation of functions missing from SYNCTOOL.

Aham · New User Joined: 24 Oct 2007 Posts: 42 Location: chennai

Frank - More than documentation we have the kernel developer himself to help us in the forum

Frank Yaeger · Posted: Sun Dec 30, 2007 6:33 am

Yes, that's true.

I'm quite proud of the fact that I "invented" and developed DFSORT's ICETOOL in 1991, as well as all subsequent enhancements. I even wrote all of the documentation. I'll continue to enhance and document it in the future.

Alissa Margulies · Posted: Mon Dec 31, 2007 10:34 pm