ICE126A 0 INCONSISTENT *INREC IFTHEN 0 REFORMATTING FIELD

vini_srcna · Posted: Mon Jan 21, 2008 9:22 pm

File 1: LRECL= 333,FB
Key: 1 to 10 (customerid CHAR(10)) (File sorted on customerid and duplicates are removed)

Cusotmerid1.......address....etc...
Cusotmerid2.......address....etc...
Cusotmerid3.......address....etc...

FILE 2: LRECL=20,FB (contains only customer id and kobnr)
Key:1 to 10 (customerid).

customerid1kobnr1
customerid1kobnr2
customerid1kobnr3
customerid3kobnr1

we want the output to somehow look like Left outer join results in DB2. (IF it was joined on Customer id)

OUTPUTFILE:

Cusotmerid1.......address....etc...kobnr1
Cusotmerid1.......address....etc...kobnr2
Cusotmerid1.......address....etc...kobnr3
Cusotmerid2.......address....etc...
Cusotmerid3.......address....etc...kobnr1

If the customer number in FILE1 is not in FILE2 then the kobnr (CHAR(10)) should be set as spaces. Customer id can have mulitple kob numbers and all should be in the output file.
ex cutomerid1 has three kob numbers.(kobnr1,kobnr2 and kobnr3)

Please help me with this. I tried the bleow one and it didnt work. I got it from this forum and edited to best of my knowledge. It doesnt work..! it gives the below error.

//TOOLIN DD *
COPY FROM(FILEB) TO(T1) USING(CTL1)
COPY FROM(FILEA) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,10,CH) -
WITHALL WITH(5,4) WITH(334,10) KEEPNODUPS
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1,10,9:5,6,443:X)
/*
//CTL2CNTL DD *
INREC OVERLAY=(334:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
SORT FIELDS=(81,8,CH,A)
OUTREC BUILD=(1,433)
/*

----error--

ICE201I E RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE126A 0 INCONSISTENT *INREC IFTHEN 0 REFORMATTING FIELD FOUND
ICE751I 0 C5-K24705 C6-K90007 C7-K90000 C8-K90007 E9-K90007 E7-K24705
ICE052I 3 END OF DFSORT

Please help...thanks

Frank Yaeger · Posted: Mon Jan 21, 2008 10:35 pm

hmmm ... your post is rather confusing. The customerid values you show are 11 bytes whereas you say they're 10 bytes and you have Cusotmeridn in file1 and customeridn in file2 which don't match. Also you seem to be doing something special with 5,4 but I can't tell what considering this would be part of the customerid in 1,10. The reason you got the ICE126A message is because you have 1,10,9:5,6 which means you're trying to put a field at position 9 after you've already put a field at positions 1-10 - that is, you have overlapping fields.

Assuming that your input file1 (FB/133) looks like this:

vini_srcna · Posted: Tue Jan 22, 2008 3:31 pm

Hi Frank,

Thanks a lot for your help. Great solution..It worked for me.

There are 14 million rows. Do u think this is still a better solution than a program..?. I guess it should perform better than a program written in PL1 and moreover its a one time process.

In fact I was asked this question and I was able to justify. Would like to know your opinion on performance.

Thanks again ..!
Vinay

Frank Yaeger · Posted: Tue Jan 22, 2008 9:09 pm

I can't tell you which will perform better ... it depends on too many things.

The best way to find out (really, the only way) is to try it both ways and see which performs better based on your own criteria for performance.