DSXchange

We are upgrading from 8.7 to 11.5 and having issues with the server jobs. Few of the jobs uses transformer in which a column is defined as char(10) but it's a date but the target table datatype is DATE. This works just fine upto 8.7 but throwing error in 11.5. It does not give much of a message other than "Abnormal termination of stage JOBNAME..trn2 detected" When we delete that particular column the job runs fine which is how we were able to narrow down the issue.

We have tried few work arounds like
1. Using Oconv(Iconv(DSJobStartDate,"D4-YMD[4,2,2]"),"D4-YMD[4,2,2]") as the definition
2. Changing the data type to Date with iconv and oconv functions. - Throwing the message "Attempt to convert String value "2016-07-19" to Date type unsuccessful"

Any idea about this problem ? Appreciate your time.

Thanks

Try resetting the server job after it aborts, then viewing the log entry "from previous run".

There's nothing in your syntax that is incorrect, though I'd probably use "D-YMD[4,2,2]" rather than "D4-YMD[4,2,2]". What happens if you:
(a) just use Iconv (not Oconv), and
(b) specify the Data Element to be Date for that column?
That should prompt DataStage to use the internal format happily.

Ray

Thanks for the reply. I tried it and same results...

Thanks

Remove the OCONV from your string, since that converts the internal format to a string. If you still get the same error message, then most likely it is happening elsewhere in the job, perhaps when writing to the target database. Try writing to a sequential file instead as a test to see if the error persists.

Arnd

Ray suggested the same and I tried it already using the following:

Iconv(DSJobStartDate,"D-YMD[4,2,2]")

Writing to sequential file and writing to DB without that particular column works just fine.

So you still get the same error message using that ICONV() and writing to a sequential file? That's quite odd, since server jobs don't actually have a "date" type internally. The result of doing an ICONV() on a string is an integer number, which indicates that either the ICONV() didn't work or that there's still the OCONV() wrapper or that something else is happening.

If you write to a sequential file, keep the ICONV() but make the datatype a string, do you get a number in the file? Then change only the output data type to "date".

I'm not at a system running DataStage right now so cannot test that.

No. I don't get an error when I write to Sequential file. It works for date or string. It runs fine and I see a number. I have problems only when I write to database with that column. If I delete the column then even writing to database works fine. Only reason I am saying that is to denote that the problem is with that column where I get date.

Your database may be expecting a different date format? If so, modify the second argument of Oconv() to produce the expected format.

If the database is expecting it in a different format then it would not have ran in with Datastage 8.7. Correct ? It runs fine in 8.7 but not in 11.5 is the issue. Just FYI...We already have a open PMR with IBM on this.