Date format in Server job causing issues in 11.5.

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
DataDoctor
Premium Member
Premium Member
Posts: 30
Joined: Mon Sep 14, 2009 10:34 am
Location: Omaha NE

Date format in Server job causing issues in 11.5.

Post by DataDoctor »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DataDoctor
Premium Member
Premium Member
Posts: 30
Joined: Mon Sep 14, 2009 10:34 am
Location: Omaha NE

Post by DataDoctor »

Ray

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

Thanks
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
DataDoctor
Premium Member
Premium Member
Posts: 30
Joined: Mon Sep 14, 2009 10:34 am
Location: Omaha NE

Post by DataDoctor »

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.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
DataDoctor
Premium Member
Premium Member
Posts: 30
Joined: Mon Sep 14, 2009 10:34 am
Location: Omaha NE

Post by DataDoctor »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Your database may be expecting a different date format? If so, modify the second argument of Oconv() to produce the expected format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DataDoctor
Premium Member
Premium Member
Posts: 30
Joined: Mon Sep 14, 2009 10:34 am
Location: Omaha NE

Post by DataDoctor »

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.
Post Reply