View previous topic :: View next topic
|
Author |
Message |
lakshmikondur
New User
Joined: 05 Jan 2006 Posts: 9 Location: hyderabad
|
|
|
|
Hi,
I have a requirement like below.
File1 attributes :
Record format . . . : FB
Record length . . . : 80
file 1 will be having n number of records.
Data example:
11111111111
22222222222
33333333333
File2 Attributes :
Record format . . . : FB
Record length . . . : 19
File 2 will be having m number of records.
file2 record count will be more than file1
Data example:
44444444444 99999999
55555555555 88888888
66666666666 77777777
77777777777 55555555
Now i want to replace the first 11 bytes value of file2 with first 11 bytes value of file1 and remaining value will be from file2.
There is no matching condition.
for example if file 1 is having 10 records output should be only the 10records with the first 11 bytes from file1 and remaining values from file2.
O/P :
11111111111 99999999
22222222222 88888888
33333333333 77777777
Please let me know how this can be achieved using DFSORT. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
You do a joinkeys with a sequence number temporarily added to each file. You only take the matches. You create your output with REFORMAT.
Have a look for examples of JOINKEYS with sequence numbers added. Try the Smart DFSORT Tricks, Page 17. |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
|
|
There are number of examples on the forum using JOINKEYS and SPLICE. I tried one and it worked, see below.
Code: |
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=YOUR-INPUT-FILE-RECL=19,DISP=SHR
//IN2 DD DSN=YOUR-INPUT-FILE-RECL=80,DISP=SHR
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(IN1) ON(81,8,ZD) WITH(1,11) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,19,81:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
INREC BUILD=(1,11,81:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
OUTFIL FNAMES=IN1,BUILD=(1,19)
/*
|
|
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Better with JOINKEYS, less IO. |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
lakshmikondur,
Alternatively, you can use JOINKEYS as below.
Code: |
//STEP0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD input file1/FB80
//SORTJNF2 DD input file2/FB19
//SORTOUT DD output/FB19
//SYSIN DD *
OPTION COPY
JOINKEYS FILES=F1,FIELDS=(81,08,A),SORTED,NOSEQCK
JOINKEYS FILES=F2,FIELDS=(20,08,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:01,11,F2:12,8)
/*
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//JNF2CNTL DD *
INREC OVERLAY=(20:SEQNUM,8,ZD)
/* |
Thanks, |
|
Back to top |
|
|
lakshmikondur
New User
Joined: 05 Jan 2006 Posts: 9 Location: hyderabad
|
|
|
|
Hi Naish/SQL code
I have tested with Both ICETOOL and SORT I am getting the below error for both
WER146B 4K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I T1 : RECFM=FB ; LRECL= 19; BLKSIZE= 27987
WER027A CONTROL FIELD BEYOND RECORD
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
It is saying the control field beyond error.
Please advice. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
You are using Syncsort rather than DFSORT. Your topic has been moved to where Syncsort questions are supported.
Read about this message:
Code: |
WER027A CONTROL FIELD BEYOND RECORD |
in the Syncsort documentation.
Post your JCL and sort control statements along with the complete diagnostic/informational messages from the problem run. |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
|
|
Could you post your SORT JCL? Also, are you sure that the files in IN1 and IN2 are correctly used? |
|
Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010 Posts: 577 Location: USA
|
|
|
|
lakshmikondur,
Looks like you are using Syncsort and the solution I posted was using DFSort.
However, if I am not mistaken, Syncsort should have similar functions (if not same). Try to understand below error message for Syncsort and see if you have correct fields/lengths for matching conditions.
Code: |
WER027A CONTROL FIELD BEYOND RECORD |
Thanks, |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Check your documentation to see if the "cntl" files are supported. Contact Syncsort to see if they can help.
If not, you'll have to do the addition of the sequence numbers seperately and then do the JOINKEYS.
Or write yourself a program. |
|
Back to top |
|
|
|