ODBC stage returns incorrect microseconds value in timestamp
Moderators: chulett, rschirm, roy
ODBC stage returns incorrect microseconds value in timestamp
Hi,
I am having an issue with the ODBC Enterprise stage while reading data from a DB2 table. The table contains timestamps with the following format:
%yyyy-%mm-%dd %hh:%nn:%ss.6
e.g. 2003-12-01 12:34:56.123456
When I do a view data the timestamps all end with .000000 which is not what is contained in the DB2 table.
I have set the extended property to microseconds and have played around with the default Timestamp strings but I haven't been able to get the microseconds to be read correctly.
Version: 7.5x2
OS: Windows
Job Type: Parallel
I would appreciate any help.
Regards
I am having an issue with the ODBC Enterprise stage while reading data from a DB2 table. The table contains timestamps with the following format:
%yyyy-%mm-%dd %hh:%nn:%ss.6
e.g. 2003-12-01 12:34:56.123456
When I do a view data the timestamps all end with .000000 which is not what is contained in the DB2 table.
I have set the extended property to microseconds and have played around with the default Timestamp strings but I haven't been able to get the microseconds to be read correctly.
Version: 7.5x2
OS: Windows
Job Type: Parallel
I would appreciate any help.
Regards
Most likely this has to do with view data, what happens if you write this column to a sequential file and look at that from Windows or to a peek stage?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I have also found that the problem occurs when I use the ODBC Enterprise stage as a sparse lookup with the lookup stage.
The timestamp value I retrieve from the lookup to the DB2 table has .000000 at the end.
Basically, anytime I use the ODBC Enterprise stage the microseconds data is lost, it doesn't matter whether I'm using view data or checking the results in a sequential file.
The timestamp value I retrieve from the lookup to the DB2 table has .000000 at the end.
Basically, anytime I use the ODBC Enterprise stage the microseconds data is lost, it doesn't matter whether I'm using view data or checking the results in a sequential file.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Have you checked whether this is a deficiency in the ODBC driver itself? Can you retrieve results with the DS_CONNECT facility that comes with DataStage? Can you use the test utility under branded_odbc? Can you use some completely different client software - maybe even one of your own C programs written to use the ODBC API?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I am using the following ODBC driver:
IBM DB2 ODBC Driver version 8.01.09.917
I have created a server job and used the ODBC stage to view the DB2 table and the microsecond data is returned successfully. This would indicate to me that the ODBC driver is capable of handling the timestamp.
Using a parallel job with the ODBC enterprise stage does not return the correct result.
IBM DB2 ODBC Driver version 8.01.09.917
I have created a server job and used the ODBC stage to view the DB2 table and the microsecond data is returned successfully. This would indicate to me that the ODBC driver is capable of handling the timestamp.
Using a parallel job with the ODBC enterprise stage does not return the correct result.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Hey even we are facing the same problem, but not in ODBC stage, but in OCI stage.
While inserting in the table through the OCI stage, it is inserting the correct values, but while extracting the timestamp from the OCI stage, the timestamp microseconds appear as .000000. I dumped these values in a dataset but in that also the microseconds value is not appearing.
While inserting in the table through the OCI stage, it is inserting the correct values, but while extracting the timestamp from the OCI stage, the timestamp microseconds appear as .000000. I dumped these values in a dataset but in that also the microseconds value is not appearing.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You've been here long enough to know the rules.
New topic: new thread.
This thread is about ODBC. Don't hijack it with what may turn out to be spurious OCI issues.
New topic: new thread.
This thread is about ODBC. Don't hijack it with what may turn out to be spurious OCI issues.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I had this same issue and the fix for me was to right-click on the timestamp column in the database stage. Choose "Edit Row" and then an "Edit Column Meta Data" popup box should display. In the Parallel tab, under the Field Type is a checkbox labeled Extended (Microseconds). After checking this box, the timestamp with microseconds was read correctly from the database.