|
View previous topic :: View next topic
|
| Author |
Message |
vaibhavjadhav
New User
Joined: 27 Jul 2007
Posts: 33
Location: mumbai
|
|
|
|
Hi,
Could you please help me in getting the desired output through ICETOOL.
Explanation:
There are two input files which needs be merged based on the matching DEPTNO. The overlaid fields in the output file is as shown in the output structure.
The length of each field DEPTNO, NAME and SAL can assumed as 10 bytes. Input files can have RECFM=FB and LRECL=80,
Please refer to the below output structure for more clarity.
| Code: |
Input:
File-1:
DEPTNO NAME SAL
001 BECKY 100
002 JACKIE 300
003 MILAN 600
003 PARIS 700
004 JAMIE 510
004 JENNY 610
File-2:
DEPTNO NAME SAL
001 ROONEY 200
002 CHANG 400
002 TED 500
003 ARNOLD 800
004 PETER 900
004 PAN 410
004 SITA 710
Output:
DEPTNO NAME SAL
001 ROONEY 200
002 CHANG 400
002 TED 500
003 ARNOLD 800
003 PARIS 700
004 PETER 900
004 PAN 410
004 SITA 710
|
|
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
why don't you tell us how you managed to take file 1 and file 2 and
create the ouput that you have provided.
what critera should we use to drop
001 BECKY 100
002 JACKIE 300
003 MILAN 600
004 JAMIE 510
004 JENNY 610
and to cause
PETER to come before PAN since part of merging is to have both file in sequence, which in the case of PETER and PAN in FILE-2, they are not.
but the PETER PAN fiasco is not as important as how do you merge two files,
yet drop 5 records? |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
vaibhavjadhav,
Use the following DFSORT JCL
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
001 BECKY 100
002 JACKIE 300
003 MILAN 600
003 PARIS 700
004 JAMIE 510
004 JENNY 610
//INB DD *
001 ROONEY 200
002 CHANG 400
002 TED 500
003 ARNOLD 800
004 PETER 900
004 PAN 410
004 SITA 710
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,10,A,81,8,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(1,10,A,81,8,A),SORTED,NOSEQCK
JOIN UNPAIRED
REFORMAT FIELDS=(F2:1,80,?,F1:1,80)
OUTFIL IFOUTLEN=80,
IFTHEN=(WHEN=(81,1,CH,EQ,C'1'),BUILD=(82,80))
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,10))
//JNF2CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,10))
//* |
dbz,
I am guessing that OP wants this
| Code: |
IF FILE1. DEPT = FILE2.DEPT and FILE1.DEPT.SEQ = FILE2.DEPT.SEQ
write output from File2
else-if File1
write output from file1
else-if File2
write output from file2
END-IF
|
|
|
| Back to top |
|
|
vaibhavjadhav
New User
Joined: 27 Jul 2007
Posts: 33
Location: mumbai
|
|
|
|
| Thanks for the solution Kolusu.......but unfortunately fields like joinkeys doesnt work on my machine. Please could you provide me the solution with simple ICETOOL. And yes the output is just the merge of the two input files and it criteria can be seen as mentioned in the output structure. |
|
| Back to top |
|
|
vaibhavjadhav
New User
Joined: 27 Jul 2007
Posts: 33
Location: mumbai
|
|
|
|
Hi,
Did you managed to find the desired output please? |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| vaibhavjadhav wrote: |
Hi,
Did you managed to find the desired output please? |
This is not do it for me forum....
DO NOT ASK HAS IT DONE QUESTIONS .. WE DONT WORK FOR YOU...  |
|
| Back to top |
|
|
vaibhavjadhav
New User
Joined: 27 Jul 2007
Posts: 33
Location: mumbai
|
|
|
|
| Apologies on that....didnt mean to say that way.....Could you please help me with the above issue. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
vaibhavjadhav,
See if below works...While concatenating,make you preserve the order of file as File2 followed by File1.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD INPUT FILE2/FB80
// DD INPUT FILE1/FB80
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,10))
SORT FIELDS=(1,10,CH,A,81,8,ZD,A),EQUALS
SUM FIELDS=NONE
OUTFIL BUILD=(1,80)
/* |
With your sample input, output from the above job is as below...
| Code: |
001 ROONEY 200
002 CHANG 400
002 TED 500
003 ARNOLD 800
003 PARIS 700
004 PETER 900
004 PAN 410
004 SITA 710 |
Thanks, |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| vaibhavjadhav wrote: |
| Thanks for the solution Kolusu.......but unfortunately fields like joinkeys doesnt work on my machine. Please could you provide me the solution with simple ICETOOL. And yes the output is just the merge of the two input files and it criteria can be seen as mentioned in the output structure. |
vaibhavjadhav,
That does not make any sense. Joinkeys has been a part of DFSORT since November 2009. What do you mean it doesn't work?
Run the following Job and show us the complete sysout including the message ids which helps us determine the level of DFSORT you have.
| Code: |
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN DD *
OPTION COPY
/* |
|
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Quote: |
| Thanks for the solution Kolusu.......but unfortunately fields like joinkeys doesnt work on my machine. |
See the following for an explanation of the different levels of DFSORT:
ibmmainframes.com/viewtopic.php?t=33389
In the future, tell us what level of DFSORT you're at so we can provide appropriate solutions. |
|
| Back to top |
|
|
|
|
