|
|
| Author |
Message |
MSTP
New User
Joined: 21 Jun 2007
Posts: 17
Location: Baltimore
|
|
|
|
Hi
FILEA has 500k records and FILEB has 100 records . For each record in FILEB there can a matching record in FILEA .FILEA is sorted as per FILEB records. I want to match FILEA and FILEB and want to extract only the first 50 of each matching into one FILEC from FILEA and all the other records into FILED from FILEA(FILEA – FILEC).
MSTP. |
|
| Back to top |
|
 |
References
|
|
 |
 Moved: Tue Sep 23, 2008 12:58 am by superk From DFSORT/ICETOOL to JCL |
MSTP
New User
Joined: 21 Jun 2007
Posts: 17
Location: Baltimore
|
|
|
|
Hi
FILEA has 500k records and FILEB has 100 records . For each record in FILEB there can a matching record in FILEA .FILEA is sorted as per FILEB records. I want to match FILEA and FILEB and want to extract only the first 50 of each matching into one FILEC from FILEA and all the other records into FILED from FILEA(FILEA – FILEC).
MSTP |
|
| Back to top |
|
|
| Moved: Tue Sep 23, 2008 8:08 pm by superk From DFSORT/ICETOOL to JCL |
arcvns
Senior Member
Joined: 17 Oct 2006
Posts: 710
Location: Chennai, India
|
|
|
|
MSTP,
I have assumed FB files of LRECL=80 and key fields of both the files starting at pos 1-3 and for extracting 2 matching records from file1 instead of 50. You can modify it as per your file attributes.
| Code: |
//STEP00 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
AAA 123
AAA 414
AAA 343
AAA 233
BBB 234
BBB 223
BBB 355
CCC 532
CCC 222
//SORTJNF2 DD *
AAA
BBB
CCC
DDD
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
REFORMAT FIELDS=(F1:1,80)
SORT FIELDS=COPY
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
OUTFIL FNAMES=OUT1,INCLUDE=(81,8,ZD,LE,2),BUILD=(1,80)
OUTFIL FNAMES=OUT2,SAVE,BUILD=(1,80)
//* |
OUT1
| Code: |
AAA 123
AAA 414
BBB 234
BBB 223
CCC 532
CCC 222 |
OUT2
| Code: |
AAA 343
AAA 233
BBB 355 |
The above SYNCSORT works only if you have Syncsort for z/OS release 1.2 or later. Let me know if you come across any issues. |
|
| Back to top |
|
|
MSTP
New User
Joined: 21 Jun 2007
Posts: 17
Location: Baltimore
|
|
|
|
Arun
I tried with the above SORT parameters. It works to some extent but I am losing records from fileA like rcds ABC 111 which don't have a match to fileB records. Please let me know what modification is needed.
thanks
mstp |
|
| Back to top |
|
|
| Moved: Tue Sep 23, 2008 11:52 pm by superk From JCL to DFSORT/ICETOOL |
Frank Yaeger
DFSORT Moderator
Joined: 15 Feb 2005
Posts: 4579
Location: San Jose, CA
|
|
|
|
There seems to be some confusion about which SORT product you're using. Please run the following job and post the //SYSOUT messages:
| Code: |
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD *
RECORD 1
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
/*
|
|
|
| Back to top |
|
|
arcvns
Senior Member
Joined: 17 Oct 2006
Posts: 710
Location: Chennai, India
|
|
|
|
Hello ,
| Quote: |
| It works to some extent but I am losing records from fileA like rcds ABC 111 which don't have a match to fileB records. |
From the above post, I assume the jcl ran successfully, but dint produce the results as you expected. Can you post the actual file attributes and the key positions on both the files and also some sample input/output records. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Moderator
Joined: 15 Feb 2005
Posts: 4579
Location: San Jose, CA
|
|
|
|
| JOINKEYS implies the OP is using Syncsort, but he told another Moderator in an offline note that he's not using Syncsort. So we need to clear up which product he's using before we go any further with this. |
|
| Back to top |
|
|
MSTP
New User
Joined: 21 Jun 2007
Posts: 17
Location: Baltimore
|
|
|
|
We are using Syncsort and below is the sort card I used to get the rqd data.Thank for all the help
OPTION EQUALS
JOINKEYS FILE=F1,FIELDS=(65,8,A)
JOINKEYS FILE=F2,FIELDS=(1,8,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,300,F2:1,8)
SORT FIELDS=COPY
INREC OVERLAY=(309:SEQNUM,8,ZD,RESTART=(65,8))
OUTFIL FNAMES=SORTOUT1,INCLUDE=((309,8,ZD,LE,50),
AND,(301,8,CH,NE,C' ')),
BUILD=(1,300)
OUTFIL FNAMES=SORTOUT2,INCLUDE=((309,8,ZD,GT,50),
OR,(301,8,CH,EQ,C' ')),
BUILD=(1,300)
************************** Bottom of Data ******************** |
|
| Back to top |
|
|
| Moved: Sat Oct 11, 2008 1:13 am by superk From DFSORT/ICETOOL to JCL |
arcvns
Senior Member
Joined: 17 Oct 2006
Posts: 710
Location: Chennai, India
|
|
|
|
MSTP,
You can further simplify this by using the SAVE parameter for SORTOUT2 like this.
| Code: |
| OUTFIL FNAMES=SORTOUT2,SAVE,BUILD=(1,300) |
|
|
| Back to top |
|
|
|
|
|
