| View previous topic :: View next topic |
| Author |
Message |
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
 Posted: Tue Nov 08, 2005 4:01 am Post subject: Get records from 1st file if it's there in 2nd, SYNCSORT. |
 |
|
Hi,
I have file-1 :
| Code: | 111 AA
111 BB
111 CC
222 AA
222 BB
333 AA
444 BB |
file-2 :
| Code: | 111 ST
222 EA
444 LD |
Now my output should be :
| Code: |
111 AA
222 AA
444 BB |
Can this be done by SYNCSORT ?? 1-3 bytes of the files is KEY..
_________________
-kr |
|
| Back to top |
|
 |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 4:25 am Post subject: |
|
|
Thanks, but as I specified, I need a SYNCSORT solution...not sure whether it can be done by SYNCSORT !!!
_________________
-kr |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Tue Nov 08, 2005 4:42 am Post subject: |
|
|
krkcs,
| Quote: |
not sure whether it can be done by SYNCSORT !!!
|
Well, It all depends on the version of Syncsort you are running on. If you have syncsort v 1.1 or 1.2 (these 2 are the latest) then the DFSORT solution provided by Frank will work. To find out the version of syncsort just code a small dummy sort step (use PGM=SORT. DON'T USE PGM=SYNCTOOL). Go to sysout and find out the version displayed in the first line.
Ok...Back to your original post. I agree that a picture/example explains 1000 times better than a page of write-up but pls take some time to explain in couple of lines as to what you are trying to do. Ppl here, spend so much time trying to solve others problems. It would be nice, if guys seeking help spend sometime and post complete information so that you get the quick and better response.
In your case, I believe that you are trying to extract a subset of records from your first file. The key for this subset is provided in file 2. Am I right ???
Question:
How many records do you expect to have in your file 2 (in production). In case the above solution provided by Frank does not work for you, then you need to go for the traditional way of creating a dynamic sort control card based on file 2 contents. In this case, the number of records written to sort control card Matters!.
Thanks,
Phantom |
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 4:50 am Post subject: |
|
|
Hi Phantom,
Yes, I'm trying to extract a subset of records from first file. Key for both of the files is starting at pos - 1 and ending at position - 3. So, whenever a key from the first file is having a instance in the second one, I'll write it to file skipping the rest of occureneces of the key in the rest of the first file. If you see in my output, I have skipped 111 BB,
111 CC..etc.,
Version of SYNCSORT we have : SYNCSORT FOR Z/OS 1.1DR TPF3..
whether SPLICE will work in this version ??
_________________
-kr |
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 5:14 am Post subject: |
|
|
Ooops...!!! forgot to answer your question Phantom, sorry for that...
File - 2 may contain up 50K of records....in production.....
_________________
-kr |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Tue Nov 08, 2005 5:20 am Post subject: |
|
|
krkcs,
Here are the answers for all your questions,
| Quote: |
File - 2 may contain up 50K of records....in production.....
|
You cannot acheive this using the traditional way of creating Dynamic sort control cards. You can do that for hundreds of records and probably for a few thousands but definitely not this much.
| Quote: |
Version of SYNCSORT we have : SYNCSORT FOR Z/OS 1.1DR TPF3..
whether SPLICE will work in this version ??
|
As I said in my earlier post, SPLICE will work for Syncsort v 1.1 and 1.2. All you need to do is to change the PGM=ICETOOL by PGM=SYNCTOOL. It will work fine.
Thanks,
Phantom |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 6:26 am Post subject: |
|
|
Yes Phantom..SPLICE is working for me...till now I thought this will be working only for IBM tools....Thanks for the Info.
I worked out this problem as follows :
| Code: | //SYNCSTP EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//CT DD DSN=FILE1,DISP=SHR
// DSN=FILE2,DISP=SHR
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(CONCAT) TO(OUTPUT) ON(1,7,CH) FIRSTDUP USING(CTL3)
/*
//CTL3CNTL DD *
OUTREC FIELDS=(1,80)
/*
//* |
Fortunately/ Unfortunately I'm seeing the desired output....Is it OK ?? Let me know if this approach has got any flaws.....
Thanking one and all....for the responses....
_________________
-kr |
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 6:28 am Post subject: |
|
|
Please read this SELECT FROM(CONCAT) TO(OUTPUT) ON(1,7,CH) FIRSTDUP USING(CTL3) as SELECT FROM(CT) TO(OUT) ON(1,7,CH) FIRSTDUP USING(CTL3)
_________________
-kr |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Nov 08, 2005 6:31 am Post subject: |
|
|
| Quote: |
Fortunately/ Unfortunately I'm seeing the desired output....Is it OK ?? Let me know if this approach has got any flaws.....
|
krkcs,
Selecting Firstdup will NOT give you the desired results. First dup select any key which has the key field more than once.
Just change your input file1 to the following
| Code: |
111 AA
111 BB
111 CC
222 AA
222 BB
333 AA <=== non matching row in file2
333 AA <=== non matching row in file2
444 BB
|
Using Firstdup you will pick up the 333 key also which is not required as it is not found in file2.
You need to use SPLICE as shown in my previous post.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 6:44 am Post subject: |
|
|
Kolusu, If both of my files (file-1 and file-2) have unique keys...then will it work fine ?? or while executing the select will it shuffle the records and get me some of the file-2 records also ??
_________________
-kr |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Nov 08, 2005 6:59 am Post subject: |
|
|
| Quote: |
Kolusu, If both of my files (file-1 and file-2) have unique keys...then will it work fine ?? or while executing the select will it shuffle the records and get me some of the file-2 records also ??
|
krkcs,
If your Both files have unique keys then you can use FIRSTDUP to get the matching records. SELECT Operator has OPTION EQUALS as default, so it will get the first occurance of each key. So make sure that you have file-1 first in your concatenation list.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
krkcs
Beginner
Joined: 30 May 2004
Posts: 27
Topics: 5
|
| Posted: Tue Nov 08, 2005 7:06 am Post subject: |
|
|
Yes Kolusu....It has definitely helped me....thanks....
_________________
-kr |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Wed Nov 09, 2005 1:41 am Post subject: |
|
|
This gives the desired output.
| Code: | //SYNCSTP EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//CT DD DSN=FILE1,DISP=SHR
// DSN=FILE2,DISP=SHR
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(CONCAT) TO(OUTPUT) ON(1,3,CH) FIRSTDUP USING(CTL3)
/*
//CTL3CNTL DD *
OUTREC FIELDS=(1,80)
/*
//* |
The below sort card was not returning anything.
| Code: | | SELECT FROM(CONCAT) TO(OUTPUT) ON(1,7,CH) FIRSTDUP USING(CTL3) |
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
