IBM Mainframe Forum

by **ranga_subham** » Tue Sep 16, 2008 10:18 pm

Hi,

I am running below SORT with ICETOOL to overlap a three byte field in file1 with four byte field from file2 but with truncation.

Select all

//SORT100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//SSMSG DD SYSOUT=*
//IN1 DD *
123ABC123
234BCD234
345CDE345
//IN2 DD *
GABB123
ARSN345
//TE1 DD DSN=&&TE1,DISP=(MOD,PASS,DELETE),SPACE=(TRK,(1,1),RLSE)
//OT1 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(TE1) USING(GAB1)
COPY FROM(IN2) TO(TE1) USING(GAB2)
SPLICE FROM(TE1) TO(OT1) ON(1,3,ZD) WITH(1,7)
/*
//GAB1CNTL DD *
OUTREC FIELDS=(1,9,4X)
/*
//GAB2CNTL DD *
OUTREC FIELDS=(1:5,3,4:1,4)
/*
//

With above job the output appears as shown below:

Select all

123GABB23
345ARSN45

I expected the output to be as shown below:

Select all

123GABB123
345ARSN345

Is it possible to achieve it as I expected? What alterations I need to make to my SORT?

Thanks.

by **ranga_subham** » Tue Sep 16, 2008 10:30 pm

Hi,

I have achieved it by changing my control cards as given below.....

Select all

//GAB1CNTL DD *
OUTREC FIELDS=(1,3,4,3,X,8:7,3)
/*
//GAB2CNTL DD *
OUTREC FIELDS=(1:5,3,4:1,4,X)
/*

Hope it helps....

Thanks.

by **Alissa Margulies** » Tue Sep 16, 2008 11:13 pm

You can also achieve this using SYNCSORT's JOIN feature:

Select all

//STEP1 EXEC PGM=SORT
//SORTJNF1 DD *
123ABC123
234BCD234
345CDE345
//SORTJNF2 DD *
GABB123
ARSN345
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(5,3,A)
REFORMAT FIELDS=(F1:1,3,F2:1,4,F1:7,3)
SORT FIELDS=COPY
/*

by **bodhi** » Fri Feb 20, 2009 7:40 pm

Hi Alissa,

I have to do the extact the same operation which mentioned above with the some specfic condtion.what i have to do is read the first file and find the key in second file and join that field with record of the first file.Could you please help me out with this problem.Records are not in sequence

Ex:
1 file record
aa 1234 rohit
ab 12345 vimal

2 second file
12345 mumbai
1234 delhi

outfile
aa 1234 rohit delhi.
ab 12345 vimal mumbai.

Thanks
Bodhi

by **Alissa Margulies** » Sat Feb 21, 2009 12:31 am

Are all your fields variable? If so, please identify the maximum possible length of each field.

by **bodhi** » Sat Feb 21, 2009 3:26 pm

Hi Alissa,

Now is it working fine but i am facing some other problem. The problem is that in the output file i am getting duplicate records for each records means if i have 100 records in input file then i am getting the 200 records in output file beacuse of this my job abended with space abend as the record count is 1800000

My card is like this

//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(18,9,A)
JOINKEYS FILE=F2,FIELDS=(12,9,A)
REFORMAT FIELDS=(F1:1,99,F2:1,50)
SORT FIELDS=COPY
/*

Could you please suggest why it is happening.

by **dick scherrer** » Sat Feb 21, 2009 10:29 pm

Hello,

It may help if you post a bit of the actual inputs and the output written from that input.

by **Alissa Margulies** » Fri May 15, 2009 8:58 pm

bodhi,

If you have duplicate records in either input file, that will cause multiple JOIN records to be produced. In order to get rid of the duplicates, modify your control statements as follows:

Select all

//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(18,9,A)
JOINKEYS FILE=F2,FIELDS=(12,9,A)
REFORMAT FIELDS=(F1:1,99,F2:1,50)
SORT FIELDS=(1,149,CH,A)
SUM FIELDS=NONE
/*

IBM Mainframe Forum

Overlap using SPLICE

Overlap using SPLICE

Used this to Overlap using SPLICE.

Re: Overlap using SPLICE

Re: Overlap using SPLICE

Re: Overlap using SPLICE

Re: Overlap using SPLICE

Re: Overlap using SPLICE

Re: Overlap using SPLICE