ICETOOL for joining records from different files

amar-tayade · New User Joined: 01 Mar 2007 Posts: 5 Location: Mumbai

Hi,

Gone through the few similar posts in this forum, but I was not able to derive solution to my problem.

I am working on a requirement in which I have to join three flat files
File1 : Key1Data1
File2 : Key2Data2
File3 : Key3Data3
Where each file LRECL is of (Key + Data) = (8 + 992) = 1000 bytes.

Now I have to Join these 3 files in LRECL of 3000 Bytes and
1) If a key is common in the three files, O/P will contain
3000 bytes as addition from file1, file2 and file3

2) If there is no matching record found in any of the above files, process should still write an output record with missing file-record being initialized.
For example,
A) If the Key1 is not matching with Key2 or Key3, o/p should
contain only first 1000 bytes and rest 2000 bytes initialized.
(Key1Data1)(Spaces)(Spaces) ----> Total 3k bytes
B) Similarly non matching Key2 will have
(Spaces)(Key2Data2)(Spaces) ----> Total 3k bytes

Can I do this with ICETOOL ? And what should be the key statements

Thanx,
Amar

Frank Yaeger · Posted: Thu May 10, 2007 1:28 am

It's not clear what you want for output in all the possible cases.

Say conceptually your input is as follows with each of the values 1-7 representing a key and Fx representing the data:

File1

1 F1
2 F1
3 F1
4 F1

File2

1 F2
3 F2
6 F2
7 F2

File3
1 F3
4 F3
5 F3
6 F3

What would you expect for the output? I think the first two records would be as follows (where bbbb represents blanks):

amar-tayade · New User Joined: 01 Mar 2007 Posts: 5 Location: Mumbai

Sorry, I omitted the example in my first post . Yes the expected file output should be as :

Frank Yaeger · Posted: Thu May 10, 2007 11:24 pm

Here's a DFSORT/ICETOOL job that will do what you asked for:

amar-tayade · New User Joined: 01 Mar 2007 Posts: 5 Location: Mumbai

Hi Frank,

Appreciate your quick reply.
I tried with the above code and the job failed with following error:

ICE146I 0 END OF STATEMENTS FROM CTL4CNTL - PARAMETER LIST STATEMENTS FOLLOW
DEBUG NOABEND,ESTAE
OPTION,MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL4,SORTIN=T7,
SORTOUT=OUT123,DYNALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE
OCHECK
SORT FIELDS=(3002,8,CH,A)
MODS E35=(ICE35DU,12288)
ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE027A 1 END OF FIELD BEYOND MAXIMUM RECORD LENGTH

Here is the code I used

Frank Yaeger · Posted: Tue May 15, 2007 9:31 pm

You need MOD for //T7 as I had for //T1:

//T7 DD DSN=&&T7,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(CYL,(5,5))

Make sure you make that change to //T7. You don't need that "earlier code" with the concatenation.

Not having MOD would give you incorrect output, but it wouldn't give you the error you're getting as that indicates the T7 data set has less than 3001 bytes.

In fact, the only reason I can think of for you getting that message is that your site has changed DFSORT's shipped installation default of SOLRF=YES to SOLRF=NO. That's NOT a good idea. But if it's true, then you can add the following to your job to take care of it:

amar-tayade · New User Joined: 01 Mar 2007 Posts: 5 Location: Mumbai

Thanks Frank for the solution. OPTION SOLRF did work for me.