ODBC stage returns incorrect microseconds value in timestamp

nathomas · Post by **nathomas** » Mon Feb 25, 2008 5:27 pm

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

ArndW · Post by **ArndW** » Tue Feb 26, 2008 1:39 am

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?

nathomas · Post by **nathomas** » Tue Feb 26, 2008 4:17 pm

When I write the timestamp column(s) out to a sequential file the problem still occurs.

nathomas · Post by **nathomas** » Tue Feb 26, 2008 9:51 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 26, 2008 10:00 pm

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?

nathomas · Post by **nathomas** » Tue Feb 26, 2008 10:12 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 26, 2008 11:21 pm

Sorta looks that way. What does your official support provider have to say?

parag.s.27 · Post by **parag.s.27** » Tue Feb 26, 2008 11:57 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 27, 2008 1:10 am

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.

cppwiz · Post by **cppwiz** » Tue Apr 07, 2009 1:20 pm

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.

kduke · Post by **kduke** » Tue Apr 07, 2009 1:41 pm

That will work but it works even better if you make that column varchar all the way thru your job. We have DB2 and this solved our problems.