Combine two files of different Lrecls

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

I have 2 different files with different length, how can I combine the contents of the 2 files based on one key into one output file.

For example: File 1 and File2 are going to combine on column key1. If record from File1 is not found in File2 on key1 then it will be assigned blanks for other columns.

files are in below format

File1

CICS Guy · Posted: Wed May 14, 2008 6:06 pm Post subject:

Look at the SPLICE Operator, it also has some good examples.....

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

hi ,

SPLICE operator is used by ICETOOL .
I think we cant use while running EXEC PGM=SORT .

So is there way to do with EXEC PGM=SORT .

CICS Guy · Posted: Wed May 14, 2008 7:21 pm Post subject:

It shouldn't matter, if you have DFSort, you have ICETOOL.
And DFSORT is called to sort the input data sets so the order shouldn't matter either.
Check out the two "Join fields from two files" in Smart DFSORT Tricks

Frank Yaeger · Posted: Wed May 14, 2008 9:17 pm Post subject:

skhorizon,

Here's a DFSORT/ICETOOL job that will do what you asked for. If you have DFSORT, then you have DFSORT's ICETOOL (it has been available with DFSORT since 1991).

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

Hi frank , thanks for ur response

But the result is not as expected .

Frank Yaeger · Posted: Thu May 15, 2008 8:38 pm Post subject:

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

Sorry that i have not shown in my previous post about duplicate rows .

Yes ,both files have RECFM=FB and LRECL=80 .

Yes ,input file2 can have a key that isn't in input file1 .

Yes ,input file1 can have a key that isn't in input file2.

Basically the requirement is that file1 have 1 key field and 3 other column . file1 can have duplicate key values .
file2 have 1 key field and 2 other columns. All key values in file2 are unique.

Then ,output file will have all records from file1 with one more column from file2 .
we have to match the key from file1 to file2 , and get the column value from file2 and append to output file .if the key is not found in file2 then assign blank value in the output file for that new column .

file1
------------

key1,col1,col2,col3
3CHAR,4CHAR,4CHAR,4CHAR

Frank Yaeger · Posted: Thu May 15, 2008 11:50 pm Post subject:

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

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

Thanks Frank , Its working .

I m getting the expected output .

I have not much idea about DFSORT/ICETOOL ,so can u please explain what does the following line means .i tried to get answer from DFSORT programming guide but was not able to understand clearly :

1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?

2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?

3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?

4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?

5. why we are using 81 here , whats its signinficance ?

6. how it is working : step by step concept ?

Frank Yaeger · Posted: Fri May 16, 2008 9:53 pm Post subject:

Everything about SPLICE is explained here:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

Also, see the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

It might also help if you display the records in T1 so you can see what they look like before they are spliced, and if you comment out the control statements in CTL3CNTL so you can see what the spliced records look like before OUTFIL removes the BB records and the ids.

I can give you some brief answers to your questions, but it may not help if you haven't spent the time to understand SPLICE.

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

Hi Frank ,

Thanks for so much information .

I wanted to know what will be the change in the code if the file length will be equal to record length .Then both input file will be of different record length and output will be different lrecl .

so according to above data :

File1 : RECFM=FB and LRECL=15

File2 : RECFM=FB and LRECL=11

File3 : RECFM=FB and LRECL=19

what will be the change in above code ?

Thanks in advance ..

Frank Yaeger · Posted: Mon May 19, 2008 9:11 pm Post subject:

skhorizon · New User Joined: 14 May 2008 Posts: 10 Location: bangalore

Hi Frank ,

The code is working in run member .
now the problem is that we have to use same code in many jobs. So i tried to write in PROC .calling the same proc in all jobs.

If run through PROC , then it gives JCL error as RC for the job . Its not recognizing from the statement
//TOOLIN DD *
.
.
/* (till last )

And same thing if written in job its giving RC=0 and running successfully.

Please help in finding the solution for this problem .
Do i need to add any system libary for running ICETOOL pgm . ?

Thanks in advance .

CICS Guy · Posted: Fri May 23, 2008 3:20 pm Post subject:

Put the TOOLIN, CTL1CNTL, CTL2CNTL and CTL3CNTL data in PDS members and change the DDs to point to those members.