|
View previous topic :: View next topic
|
| Author |
Message |
ms0033436
New User
Joined: 20 Nov 2008
Posts: 15
Location: Pune
|
|
|
|
I need to replace some columns from one dataset with the specific columns from another dataset. I have around 65000 records in both the files. Both are of fixed block type
for example:
2 files x1 & x2
| Code: |
x1: abcd12345678ansi
abcd45697856sint
x2: ac45678994chg
bc98756123ang |
I want to replace the columns 5 to 13 in x1 with columns 2 to 11 from X2
I have tried using OUTREC OVERLAY but this is replacing all rows with the same value
Thanks
Mani |
|
| Back to top |
|
 |
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
ms0033436,
What is the LRECL and RECFM of the input and output files? |
|
| Back to top |
|
|
ms0033436
New User
Joined: 20 Nov 2008
Posts: 15
Location: Pune
|
|
|
|
Hi,
Following are the details of both the files
Record format . . . : FB
Record length . . . : 2005
Thanks,
Mani |
|
| Back to top |
|
|
Alexis Sebastian
New User
Joined: 04 Mar 2010
Posts: 38
Location: DC
|
|
|
|
Mani,
Is there any common key in both files? If so, what is the starting position and its length? |
|
| Back to top |
|
|
ms0033436
New User
Joined: 20 Nov 2008
Posts: 15
Location: Pune
|
|
|
|
Hi,
I don't have any common keys in both the files |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello,
So u want to merge record like first record of X1 with first record of x2 ? |
|
| Back to top |
|
|
hikaps14
Active User
Joined: 02 Sep 2005
Posts: 189
Location: Noida
|
|
|
|
Mani,
There is something wrong with your column lengths
| Quote: |
| I want to replace the columns 5 to 13 in x1 with columns 2 to 11 from X2 |
13-5 = 8
11-2 = 9
Do you want to replace 8 byte field with 9? or it was some typo error ?
You have 2 options logically
1) You may try using splice operator for this type of record by record concatenation
2) You can also use new Reformat Fields operator used with join keys.
You need to create temporary sequence no. for each record for both files then join files on that sequence no. |
|
| Back to top |
|
|
ms0033436
New User
Joined: 20 Nov 2008
Posts: 15
Location: Pune
|
|
|
|
| Sorry that was a typo error |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi hikaps14,
I was thinking to the second option u mentioned but could not understand can we everything in single ort step, I mean adding the temp seq no to 2 files as well as joining dem -
In the below sort card, Hoe can we add the logic to create temp se no in at posn 17 in file1 and 13 in file2 -
| Quote: |
JOINKEYS FILES=F1,FIELDS=(17,08,A)
JOINKEYS FILES=F2,FIELDS=(13,08,A)
REFORMAT FIELDS=(F1:01,04,F2:03,08,F1:13,4)
OPTION COPY
BUILD=(1:1,4,05:05,08,13:13,4) |
Please suggest |
|
| Back to top |
|
|
hikaps14
Active User
Joined: 02 Sep 2005
Posts: 189
Location: Noida
|
|
|
|
Try the below job
| Code: |
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(2006,8,ZD) WITH(5,8) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1,2005,2006:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
INREC BUILD=(5:3,8,2006:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(1,2005)
/* |
I have assumed that you are replacing 5-12 in X1 with 3-10 in X2. All I/P and O/p files are FB and lrecl=2005.
I have tries the above code but with different lrecl and position of files. |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi,
In the above sort card, I understood that all the records are in the temp file and then SPLICED in the seqnum added, but could understand the WITH part.
| Quote: |
| SPLICE FROM(T1) TO(OUT) ON(2006,8,ZD) WITH(5,8) USING(CTL3) |
Could you please explain.
Also, Can you please suggest how can we accomplish this using the joinkeys.
Thanks in advance. |
|
| Back to top |
|
|
hikaps14
Active User
Joined: 02 Sep 2005
Posts: 189
Location: Noida
|
|
|
|
Splice Operator:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA10/6.13?DT=20050222160456
Try going through the above link.
In short WITH is the keyword which helps you in picking the Column of other records with same key (key is defined using ON keyword). There are other options of WITHALL and WITHEACH. These are well defined wih examples in above link.
I would try and let u know about the Joinkeys job. Still, did the earlier job run fine and produce the desired result ? |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hi,
Thanks for the info.
Yes it did give the desired result.
But was curious to know if can be done with joinkeys since was trying it.
Please let me know if find anything on that.
Thanks. |
|
| Back to top |
|
|
hikaps14
Active User
Joined: 02 Sep 2005
Posts: 189
Location: Noida
|
|
|
|
Hi,
Have you tried finding out on Google. There is a pdf 'Smart DFSORT Tricks'. This should help
ftp://ftp.software.ibm.com/storage/dfsort/mvs/sorttrck.pdf
There is topic 'Join fields from two files record-by-record'. You should find it interesting and useful. You will get a chance to try and experiment more on dfsort tricks.
Thanks. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Quote: |
| In the below sort card, Hoe can we add the logic to create temp se no in at posn 17 in file1 and 13 in file2 |
With the appropriate INREC statement in JNF1CNTL (for file1) and in JNF2CNTL (for file2). INREC in JNFnCNTL is applied to the Fn file before JOIN processing. This is all explained in my paper at:
www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174 |
|
| Back to top |
|
|
anshul_gugnani
New User
Joined: 02 Nov 2009
Posts: 73
Location: Mumbai
|
|
|
|
Hello Frank,
Thanks a lot. This indeed is an awesome user Guide.  |
|
| Back to top |
|
|
|
|
