Hash file making problem

rohit.ka07 · Post by **rohit.ka07** » Mon Sep 20, 2010 5:12 am

Hi,

I've a job with ODBC as source and performing lookup using hash file.
Job was running fine yesterday.
Today when I run, job is aborting with following error.

Abnormal termination of stage XXXX..TrfDevice detected.
Error message after resetting the job.

From previous run
DataStage Job 1209 Phantom 12704
Abnormal termination of DataStage.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.899766562.DT.1560382767.TRANS3 at address 11e.

Actually I am mapping four columns from hash file to target and fifth column I am using for lookup,.
When I ran around this, I found that, when I map only two columns and hard code some value(1) for other two columns, Job ran success.
Then I thought there is some problem with a particular column.
So, I tried mapping only single column and hard coding default value to rest of columns to identify which column is giving problem.
Worst thing is, job runs success when I map single column for all columns.
Job runs success if I map any two columns.
Job runs success if I map any three columns.
Job fails only when I map all four columns.

Please add suggestions on this.\
Thanks...

chulett · Post by **chulett** » Mon Sep 20, 2010 5:57 am

What is your actual target? Did you search the forums for that "Fault Type is 11" error message?

rohit.ka07 · Post by **rohit.ka07** » Mon Sep 20, 2010 7:03 am

No problem in the target side I guess...
there are two more transformers after this transformer.
Final trnf is Oracle bulk loader stage.

error is occurring in the Transformer stage reported in the error log..
I e the first transformer.

chulett · Post by **chulett** » Mon Sep 20, 2010 7:06 am

Errors in Server jobs always come from 'active' stages, typically Transformers but that doesn't always mean they are at fault. Me, I would concentrate on the target. Did you search for your error here? You are not the first person to see that particular issue, hence the suggestion.

rohit.ka07 · Post by **rohit.ka07** » Mon Sep 20, 2010 7:18 am

But I suspect, this issue is with the hash file which is used for lookup.
as I mentioned earlier, job runs success when we map any one single column to next transformer.
Job is aborting with the error message when I map all columns.

This is some what different issue compared to previous posts.
Isn't it?

FYI, I checked all previous posts related to "Fault type is 11. Layer type is BASIC run machine" error message.

Thanks...

chulett · Post by **chulett** » Mon Sep 20, 2010 8:31 am

Never seen hashed files cause an issue like that. As I said, in your shoes I'd concentrate on your target, the stage and Oracle client version. That and involve your official support provider for any low level fault like that.

rohit.ka07 · Post by **rohit.ka07** » Mon Sep 20, 2010 9:09 am

As I mentioned earlier, my target is Oracle Bulk Loader Stage.

Job design is like this.

ODBC---->Trnsf--->Transf---->Transformer------>Ora_Bulk_Loader

each transformers are connected with Hash files for lookup.

problem is with the first transformer (reported in the error log) which is connected by a hash file.

arunkumarmm · Post by **arunkumarmm** » Mon Sep 20, 2010 10:31 am

Do you have any stage variable or column derivation that has complex IF..THEN..ELSE? If so can you post them all? In all of the transformers?

rohit.ka07 · Post by **rohit.ka07** » Mon Sep 20, 2010 11:59 pm

Yes, there are two If Else conditions in the first transformer.

IF NOT(ISNULL(LnkIn.XXXX.YYYY)) Then 1 Else 0

IF NOT(ISNULL(LnkInXXXX.YYYY)) Then LnkInXXXX.ZZZZ Else 'Undefined'

No other transformations.
Also, no transformations in the rest of two transformers. Its direct mapping.

arunkumarmm · Post by **arunkumarmm** » Tue Sep 21, 2010 1:04 pm

rohit.ka07 wrote:Yes, there are two If Else conditions in the first transformer.

IF NOT(ISNULL(LnkIn.XXXX.YYYY)) Then 1 Else 0

IF NOT(ISNULL(LnkInXXXX.YYYY)) Then LnkInXXXX.ZZZZ Else 'Undefined'

No other transformations.
Also, no transformations in the rest of two transformers. Its direct mapping.

These are not complex though, can you remove these from the transformer and try running the job.

Are you sure you dont have any other condition in your transformers? Even in the constrains?

chulett · Post by **chulett** » Tue Sep 21, 2010 1:09 pm

I'm going to repeat one last time that, IMHO, concentrating on the transformer and/or hashed file lookup is a mistake. Those kind of errors come from the target, typically Oracle client / driver generated errors and they can be very odd and very subtle, working for 99.9% of what you do in your jobs and blowing up on .1%. Been there, done that, got the t-shirt hence the suggestion to check your driver versions and involve support.

arunkumarmm · Post by **arunkumarmm** » Thu Sep 23, 2010 3:43 pm

I had the same exact error, which was due to an incorrect constrain. I corrected that and never had the issue again. This may not be relevent, but that's what I did.