DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
DataDoctor



Group memberships:
Premium Members

Joined: 14 Sep 2009
Posts: 30
Location: Omaha NE
Points: 296

Post Posted: Tue Jul 19, 2016 4:46 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54398
Location: Sydney, Australia
Points: 295054

Post Posted: Tue Jul 19, 2016 7:00 pm Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
DataDoctor



Group memberships:
Premium Members

Joined: 14 Sep 2009
Posts: 30
Location: Omaha NE
Points: 296

Post Posted: Wed Jul 20, 2016 12:25 pm Reply with quote    Back to top    

Ray

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

Thanks
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Thu Jul 21, 2016 3:01 am Reply with quote    Back to top    

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.

_________________

Image
Rate this response:  
DataDoctor



Group memberships:
Premium Members

Joined: 14 Sep 2009
Posts: 30
Location: Omaha NE
Points: 296

Post Posted: Thu Jul 21, 2016 9:19 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Thu Jul 21, 2016 10:44 am Reply with quote    Back to top    

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.

_________________

Image
Rate this response:  
Not yet rated
DataDoctor



Group memberships:
Premium Members

Joined: 14 Sep 2009
Posts: 30
Location: Omaha NE
Points: 296

Post Posted: Thu Jul 21, 2016 11:07 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54398
Location: Sydney, Australia
Points: 295054

Post Posted: Thu Jul 21, 2016 3:58 pm Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
DataDoctor



Group memberships:
Premium Members

Joined: 14 Sep 2009
Posts: 30
Location: Omaha NE
Points: 296

Post Posted: Fri Jul 22, 2016 9:53 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours