Ambiguous Error Message!

[mczern]

After processing approximately 1.7M records out of 3.5M, I suddenly got the following error:

Abnormal termination of stage Job1..CREATE_VALUES detected

CREATE_VALUES is a DataStage 'Transformer' that manipulates values from a source cursor and is creating additional columns for use down stream.
(Job1 used in place of real job name to protect the innocent)

Anyone gotten one of these verbose errors before?
Any pointers on where to start the debugging process?

Right now I'm in the process of re-running to see if it chokes in the same place. (1.5hrs required to process.)

tia
mike

[chulett]

When you job fails again, reset the job from the Director and see if it logs an entry with 'From previous run...' in it. If so, double-click on that entry and let us know what details are in there.

[ray.wurlod]

Welcome aboard! :D

Check for unexpected NULL in your source data.

[mczern]

When you job fails again, reset the job from the Director and see if it logs an entry with 'From previous run...' in it. If so, double-click on that entry and let us know what details are in there.

Thanks for the tip... Here's more detail....

From previous run
DataStage Job 567 Phantom 14619
jobnotify: Unknown error 911
DataStage Phantom Finished.
[14622] DSD.StageRun FactResultLabSTAGINGcore. Job1.CREATE_VALUES 1 0/2 - terminated.


From previous run
DataStage Job 567 Phantom 14622
Abnormal termination of DataStage.
Fault type is 11. Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.551435808.DT.1378857542.TRANS3 at address be.



There are no functions being called within the tranform. Mostly if then else if then else logic.

Any help decrypting would be appreciated.
Thanks again,
mike

[ketfos]

Hi,

You are running out of space.
You need to allocate more amount of space for the target object or reduce your records

Note - It is segment violation error.

If you query the sys.message table, you will find description for this error (911)

Ketfos

[mczern]

Hi,

You are running out of space.
You need to allocate more amount of space for the target object or reduce your records

Note - It is segment violation error.

If you query the sys.message table, you will find description for this error (911)

Ketfos

Thanks All for the help!
Great tips to help me in future debugging.

[mczern]

Hi,

You are running out of space.
You need to allocate more amount of space for the target object or reduce your records

Note - It is segment violation error.

If you query the sys.message table, you will find description for this error (911)

Ketfos

Thanks All for the help!
Great tips to help me in future debugging.

Can you direct me to where the sys.message table is?
It's not an Oracle table and I don't see it in the common project directory on the Linux box.

mike

[ray.wurlod]

Funnily enough, and despite what Oracle bin Larry would have you believe, there are other databases than Oracle.

SYS.MESSAGE is a UniVerse table. You can query it from the Administrator client Command window, or from a TCL prompt. TCL is UniVerse (DataStage) approximate equivalent of Oracle's sqlplus. Beware that identifiers, such as table names, are case sensitive in UniVerse (DataStage).

Also note that SYS.MESSAGE follows POSIX conventions by having six-character keys - you need to add leading zeroes to key values shorted than six characters.

Code: Select all

SELECT @ID FMT '6R', F1 FROM SYS.MESSAGE WHERE @ID = '000911';

[mczern]

Funnily enough, and despite what Oracle bin Larry would have you believe, there are other databases than Oracle.

SYS.MESSAGE is a UniVerse table. You can query it from the Administrator client Command window, or from a TCL prompt. TCL is UniVerse (DataStage) approximate equivalent of Oracle's sqlplus. Beware that identifiers, such as table names, are case sensitive in UniVerse (DataStage).

Also note that SYS.MESSAGE follows POSIX conventions by having six-character keys - you need to add leading zeroes to key values shorted than six characters.

Code: Select all

SELECT @ID FMT '6R', F1 FROM SYS.MESSAGE WHERE @ID = '000911';

Thanks again for pointing me in the right direction and the deprograming.
Unfortunately I've been heavily brainwashed by Larry's gang and I'm fairly new to the DataStage arena. So, I didn't even think to look in the UniVerse tables.

mike (The Rookie)

[chulett]

Ok... if it's in the SYS.MESSAGE table then why does DataStage report it as an "Unknown error 911"? Is the diagnosis that it is a space issue / segment violation correct?

[kcbland]

My guess is that there's a little more action happening between the ORAOCI stages, potentially a hash file that's getting too big or something else. We need a little more info.

[mczern]

My guess is that there's a little more action happening between the ORAOCI stages, potentially a hash file that's getting too big or something else. We need a little more info.

A little background...
The job used to run to completion when all hash file lookups were used within the job. We are attempting to tune performance so we replaced 2 of the 12 Hash File Key lookups with Oracle OCI direct lookups. We also saw that if we increased the array size to 100 on other jobs that the throughput increased dramatically. The reason for switching to direct lookups was 2 fold:
1. The tables are pretty big (12M and 11M records), so dumping to hash files takes a while.
2. We are running into a 2Gig limit on hash files so we are looking at alternatives to performing dimensional key lookups while populating fact tables in our data warehouse.

We are hoping that the speed trade off of doing direct lookups into the Oracle table will be faster than our current implementation of dumping to hash file then using the hash file to do the key lookups during Fact table loads.

Right now I have reduced the array size on the two direct lookups to 10 to see if a) completes or b) gets further into the data set than it has on previous runs.

Any help in providing an solution to key lookups would be greatly appreciated. Might be better as a separate thread?

mike

[kcbland]

Your reference lookups are against all 12 million rows? Are these dimensions? Why are you dumping the whole table to a hash file, why not just get the rows you need? If you took all of the distinct natural keys for each dimension in a typical run, I bet you would only require a vastly smaller number of rows be dumped into hash files.

There are methods of prescanning your source data for its natural keys, putting into a work table (we call it a bucket of keys), and inner-join extract the required rows and put that into the hash file. When you couple a smaller hash file, preload to memory option, multiple job instance capability, and performance of the hash file, the OCI lookups never scale. This technique is true for both Server and PX implementations. You cannot justify pulling 100% of the rows out of the database when you only need to reference 5%.

I've got white papers on these techniques if you're interested. Just get it off my website.

[chulett]

Probably so.

On the hashed lookups - is there really a need to load your entire Oracle tables into them? One 'trick' (aka Best Practice) is to only load what you need for the current dataset. I generally make sure that the key fields from the data that I am loading is also up in an Oracle table - then that set can be joined to the tables being hashed and thus constrain that build to only the keys that are needed for this run.

[chulett]

Doh... got distracted and sat on this post for too long.

You go, Ken!