|
View previous topic :: View next topic
|
| Author |
Message |
hsinaz50
New User
Joined: 09 Apr 2014
Posts: 5
Location: United States
|
|
|
|
Hello Everyone,
Here is my base dsn sample:
H*20120105
DXY9-0000-
DXY2-0000-
DXY5-0000-
DXY3-0000-
TCOUNT04
Here is my look up DSN
SOMEDATA-XY5-0012
SOMEDATA-XY9-0345
Result/output dsn expected
H*20120105
DXY2-0000-
DXY3-0000-
DXY5-0012-
DXY9-0345-
TCOUNT04
So 1) retain the header and trailer on base. 2) Sort only detail (D) records on base on position 2 through 4 3) Pull position 14-17 from lookup by joining/matching on base (2-4) with lookup (10-12).
I tried simple DFSORT with JOINKEYS and found that the header and trailer both were at the top of the file, not OK.
I tried splice but I lost the header and trailer on output.
I am unable to figure out ICETOOL with JOINKEYS using HEADER TRAILER operator on COPY to retain the header trailer on output.
Please help!
Thanks!
hs |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
You need to post the jcl and sort control statements you used for the JOINKEYS along with any diagnostic or informational messages generated. |
|
| Back to top |
|
|
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
Avoid using SPLICE. I find it confusing.
You can still use JOINKEYS and provide extra statements to modify the sort sequence
| Code: |
//SYSIN DD *
JOINKEYS FILE1=F1,FIELDS=(1,1,CH,A,3,3,CH,A)
JOINKEYS FILE2=F2,FIELDS=(your-lookup-dsn-key-field)
REFORMAT
etc.
//JNF1CNTL DD *
INREC BUILD=(1,1,CHANGE=(1,
C'H',C'1',
C'T',C'3'),NOMATCH=(C'2'),
1,record-length)
|
The CHANGE will take the first byte and change it to a '1', '2' or '3'
I have not tested it so you might have to do some debugging. This is just meant to show the general method. |
|
| Back to top |
|
|
hsinaz50
New User
Joined: 09 Apr 2014
Posts: 5
Location: United States
|
|
|
|
Thanks Dick & Jerryte!
Jerryte, I like your idea. I will use it in future. Alhough yesterday I resorted to using 3 steps to do this.
1) Sort the base using ICETOOL datasort ignoring header and trailer.
2) Sort and inrec build the lookup to keep keys and what I needed to pull.
3) Joinkeys the two above, using SORTED and NOSEQCK options.
Jerryte, I think I could have done it in one step using your trick. I am already in UAT so lazy to go back for this one 
Thanks again! |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| I hope you don't run into any performance issues in case of millions of records.. So you may wants to check the CPU and elasped time by having actual volume of production. If it is going to be less records then that should be fine. |
|
| Back to top |
|
|
|
|
