SIGSEGV Error:Fail to load timestamp fields

[kanchan]

Hi Experts,

We are having SIGSEGV Error in the following scenario:

We are loading a oracle table which have timestamp fields in it.Basic workflow is: oracle>Transformer>Oracle
The records are loaded fine if those timestamp field contain null values in all the records or date values in all the records.
But when a combination of that is fed to the job,we get a SIGSEGV Error.

Operator terminated abnormally: received signal SIGSEGV

I had reviewed some related topics to SIGSEGV but they do not actually help.
Can anyone provide some workaround?
Thanks in advance.
Kanchan

[sud]

Have you defined the column data type in the job as timestamp as well? Also, what happens when you try to do a view data?

[nick.bond]

SIGSEGV comes from the operating system and is a segmentation fault which could be caused by many things. Are you loading using SQLldr or OCI stage?

Do you have the correct version of the client for Oracle installed?

[kanchan]

Have you defined the column data type in the job as timestamp as well? Also, what happens when you try to do a view data?

Hi sud,
Yeah,the datatype is timestamp and viewdata works fine.and even the first set of records for which the value is null in those timestamp fields gets loaded(around 1000 records),but as soon as it encounters the not null values,it aborts with error.

Can I partition my data based on null in that field before feeding to target oracle stage? will that be any help?

[sud]

... before feeding to target oracle stage? will that be any help?

You mean you are able to read the data but it errors while writing to a table?

[kanchan]

You mean you are able to read the data but it errors while writing to a table?

Exactly Sud, It is giving error while writing those records into the oracle table.It is in DATE datatype in oracle and taken as timestamp in DataStage.
Let me post the exact errors:

Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: signalHandler__Fi() at 0xd29f3034
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: .() at 0xf058
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: nioqsn(??, ??, ??, ??) at 0xd4d46fdc
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: ttcclr(??, ??, ??, ??, ??, ??, ??, ??) at 0xd4ad8c18
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: ttcedtm2dtm(??, ??, ??, ??, ??, ??, ??, ??) at 0xd4ae159c
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: ttcacs(??, ??, ??, ??, ??, ??, ??, ??) at 0xd4d37104
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: ttcdrv(??, ??) at 0xd4dbec58
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: nioqwa(??, ??, ??, ??, ??, ??) at 0xd4d4959c
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: upirtrc(??, ??, ??, ??, ??, ??, ??, ??) at 0xd49fad2c
Type: Warning
Event: Ora_D5_BANK_BRANCHES,1: kpurcsc(??, ??, ??, ??, ??, ??, ??, ??) at 0xd4af1708
Type: Fatal
Event: Ora_D5_BANK_BRANCHES,1: Operator terminated abnormally: received signal SIGSEGV
Type: Fatal
Event: main_program: Step execution finished with status = FAILED.

so,it seems datastage jobflow works fine till it tries to load the data.

[sud]

Okay, plss try this. Convert the timestamp to a string and in the target pass the string into the timestamp column. By the way, are you using insert/update or sql loader?

[kanchan]

Okay, plss try this. Convert the timestamp to a string and in the target pass the string into the timestamp column. By the way, are you using insert/update or sql loader?

Hi,
I had tried to use the type conversion functions in Transformer as well as modify stage to convert the timestamp into string then again back to timestamp,with some format specification.But the problem do not go away.

I am using isert/update option to insert the records into oracle with the update command disabled.
It seems that datastage is doing some default initialisation of the timestamp fields when null values are there and that is not compatible to the incoming data.Can you put some thought over it?

Kanchan

[nick.bond]

Are you using a stage variable for the derivation of your timestamp anywhere along the way?

At no point can you set a stage variable to null, there was a bug in 7.5.1 I think that allowed a job to be compiled with SetNull() in a stage variable. this was fixed by lot sof tha available patches.

I seem to remember that when running a job that sets a stage varaible to null at any point all subsequent values were also null, because the stage varaible could never actually become null. Or something like that. Just check if you pass the value through a stage varaible.

[kanchan]

Are you using a stage variable for the derivation of your timestamp anywhere along the way.

Nick,
We have used stage variables and also some functions..and as u said.. datastage functions do not work properly when a null value is encountered..so what we have done is splitting the incoming dataset using a filter and only not null values go into the transformer..the null records are then merged to the output dataset of the transformer using a funnel and sent to the target oracle stage..

Kanchan

[nick.bond]

A couple of things to add then.

When you have this problem of dates being null and you wish to use them in routines or stage variables, it can be usefull if you have a 'special date' that is outside of the domain of your data, i.e. 9999-12-31, which you can use set null dates to using

Code: Select all

NullToValue(input.date, StringToTimestamp('9999-12-31 00:00:00', '%yyyy-%mm-%dd %h:h%:mn:%ss'))

(not 100% about the format syntax of the time there but it's close.)

You can then check for that date in the column derivations and change back to null.)


Back you your current problem though, I gather you are still having problems if you pass the data straight through without going through the tansformer. If this is the case are you sure you have a null value? DS EE has a habit of converting nulls to ascii nulls if the field definition changes from nullable to not nullable. You can do a quick test on this by adding in a transformer just before you merge you data and use SetNull() on the date column. Make sure all meta data for that column has the field as nullable.

[kanchan]

Hi Nick,

Thanks for your tips.Let me try these things out and will let u know.

Kanchan

[DSguru2B]

Itsi bitsi change in Nick's timestamp format. Nick, if i may

Code: Select all

NullToValue(input.date, StringToTimestamp('9999-12-31 00:00:00', '%yyyy-%mm-%dd %hh:%nn:%ss'))