DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
craigbarrick
Participant



Joined: 24 Sep 2007
Posts: 2
Location: UK
Points: 25

Post Posted: Fri Jan 23, 2009 4:19 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Apologies if already answered but I couldn't find it anywhere.

I have an issue whereby that I am trying to load from a dataset to a SQL Server 2005 table using the ODBC Connector.

All works fine until I try and load a timestamp (26,6) into a SQL datetime (23,3) - I then get an Arithmetic Overflow abort due to SQL expecting milliseconds (I think!).

In the Enterprise ODBC stage there are no such problems and Im a happy man as all loads as expected and the timestamp issues do not occur.

Would like to know if anyone encountered this problem / has advice.

Thanks
Craig
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54601
Location: Sydney, Australia
Points: 296091

Post Posted: Fri Jan 23, 2009 2:59 pm Reply with quote    Back to top    

You may need to apply a conversion, changing the fractional part of the seconds from .6 to .3 in the timestamp format picture. This may be possible in a Modify stage (I've not tried that). It is def ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
shamshad



Group memberships:
Premium Members

Joined: 25 Aug 2004
Posts: 147
Location: Detroit,MI
Points: 1383

Post Posted: Fri Jan 23, 2009 4:02 pm Reply with quote    Back to top    

May be worth trying this

(1) First HardCode Timestamp value and insert in target table and see if
jobs runs successfully.
(2) If it works fine, treat your input timestamp as character column and
then format the input value in the same way as the hardcoded value.

_________________
Datawarehouse Consultant
Rate this response:  
Not yet rated
mk_ds09
Participant



Joined: 25 Jan 2009
Posts: 72
Location: Pune
Points: 518

Post Posted: Mon Jan 26, 2009 3:17 am Reply with quote    Back to top    

Hi..

The default format of the timestamp is as following :-
%yyyy-%mm-%dd%hh:%nn:%ss. onent.

And seconds are stored as follows :-
– %ss.n: A two-digit seconds plus fractional part, where n is the
number of fractional digits with a maximum value of 6. If n is 0,
no decimal point is stored..

If your datatype is (26,3), you need to change the fractional part accordingly to store those values.

Hope this helps..

-------------------------
MK
Rate this response:  
Not yet rated
craigbarrick
Participant



Joined: 24 Sep 2007
Posts: 2
Location: UK
Points: 25

Post Posted: Thu Jan 29, 2009 4:37 am Reply with quote    Back to top    

Thanks guys.

Hardcoding the date led me to discover that the date format expected at the SQL Server target was yyyy-dd-mm and not yyyy-mm-dd as per the dataset and this is what was causing the arithmetic overflow (not the microseconds as first thought).

Just find it annoying that the Enetrprise ODBC handles this conversion perfectly but the ODBC Connector requires extra effort in handling this data type - or a change to the set up of the target database structure suffices (which fortunately I could do).

The requirement for this approach has since now gone away for our project anyway as the client is now looking to use DB2 but I think the fact they dont have NLS installed may have contributed to this problem and I believe that NLS may resolve some of this.
If I ever get chance I will try and re-post findings.

Thanks
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: 54601
Location: Sydney, Australia
Points: 296091

Post Posted: Thu Jan 29, 2009 3:16 pm Reply with quote    Back to top    

Connectors are new technology. I'd report this as a bug in the connector. After all, YYYY-DD-MM does not conform to any industry standard (particularly not ODBC), and IBM prides itself on standards ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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