DSXchange

HI,

I have one question on ISNULL() function.

I used ISNULL() function in transormer stage in few jobs to check the column value if it has null or not.
First I tested jobs in test, all went fine.I moved code to production as is.out of three jobs 2 ran fine.one got aborted with error'' trying to setISNULL to not nullable column''
But I made that column as nullable.

Another quesion

in production ,i see different elapse time for one job everyday.

It has one sourcce stage ,10 lookupstages ang 3 transformers and one agg.
Do you have any idea, what could be the problem.
Can you please help me ?

Thanks
Sri

The job that is failing, is it loading to a table. You might have that particular column set as nullable at the datastage end but if its not nullable in the database, it will generate error.
As for the run times, it depends upon the data volume, if network is involved, then network traffic etc etc.

Thanks for your quick reply

Not loadind data into table.storing i dataset
the column say XXXX ,that is coming from source is nullable

After source table I have transformer stage .I put this expression in stage variable
If IsNull(XXXX) then 123 Else If XXXX = 0 Then 123 Else XXXX

and I assigend this stgage variable to output column YYYYYwhich is not nullable.Job is aborting at transformer stage

the same logic worked in other jobs.
now to run the aborted job in production, I removed ISNULL function from expression If XXXX = 0 Then 123 Else XXXX
and checked this function in source query like NVL(XXXX,123).It went fine.
Still I am wondering how come it worked for one job for not the other.

For second question

Usually we process same no of records every day say 500000.Is it because of overloading of etl server or any other issue.Can you please explain

Thanks

DSguru2B wrote:The job that is failing, is it loading to a table. You might have that particular column set as nullable at the datastage end but if its not nullable in the database, it will generate error.
As for the run times, it depends upon the data volume, if network is involved, then network traffic etc etc.

Sri, There is nothing wrong with your IsNull statement. The only issue might be the Datatype of that field , sometimes it will behave like this if you are having some date or Timestamp fieds. For the other issue it might be possible that server is getting overloaded or network traffic as suggested. Try doing a cleanup and check whether that resolves your issue.

us1aslam1us wrote:Sri, There is nothing wrong with your IsNull statement. The only issue might be the Datatype of that field , sometimes it will behave like this if you are having some date or Timestamp fieds. For the other issue it might be possible that server is getting overloaded or network traffic as suggested. Try doing a cleanup and check whether that resolves your issue.

Thanks for your reply.

sri75 wrote: Thanks for your reply.

Does it eliminate your warning. If so, pls mark the topic as resolved.