Timestamp and DB2

asitagrawal · Post by **asitagrawal** » Mon Oct 23, 2006 11:18 am

I am writing the timestamp (YYYY-DD-MM HH:MI:SS.SSSSSS) into a DB2 tables, from a job.
The data when written to a seq file, is written with the microseconds., but when the same is written into a DB2 table, the microseconds change to '000000'.
How do I handle the issue.???

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Oct 23, 2006 11:20 am

can you post an exapmle?

asitagrawal · Post by **asitagrawal** » Mon Oct 23, 2006 11:32 am

Krazykoolrohit wrote:can you post an exapmle?

INSERT INTO FDM1PA.PS_ASCDTTM (JOB,ASC_DTTM) VALUES (?,?);

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Oct 23, 2006 12:09 pm

I meant, the data examples. Like the input data which you have. what format its in seq file and what format its in DB2 table.

asitagrawal · Post by **asitagrawal** » Mon Oct 23, 2006 12:18 pm

Input = '2006-10-23 17:15:58.135789'
Output (SEQ File) = '2006-10-23 17:15:58.135789'
Output (Db2) = '2006-10-23 17:15:58.000000'

DSguru2B · Post by **DSguru2B** » Mon Oct 23, 2006 12:22 pm

What if you insert something thats on the higher end in the nanoseconds category. Something like
'2006-10-23 17:15:58.835789'
Does that get rounded off too
'2006-10-23 17:15:59.000000'

What if you specify the sql type as varchar, do you still encounter the same problem?

asitagrawal · Post by **asitagrawal** » Mon Oct 23, 2006 12:38 pm

Yes...it is getting stored as 000000 only for microseconds...no rounding off to next highr sec is taking place......and also VarChar did not help...

DeepakCorning · Post by **DeepakCorning** » Mon Oct 23, 2006 12:46 pm

ASC_DTTM -- What is the type of the field in the DB ?? Date , DateTime , Timestamp?? Also Can you Populate a SEQUENTIAL file again in addition with the DB2 Table and let us know what is getting stored in that one? This will help us to find out that it is happening during the job or while inserting.

DSguru2B · Post by **DSguru2B** » Mon Oct 23, 2006 12:54 pm

Are you providing any sort of transformations?
I have worked with DB2. If you dont insert the nano seconds part, only then the database will add .000000 to it. If you are passing it, then it gets inserted as it is. If nothing else worked, varchar should have worked. There is something else going on. Are you doing any iconv/oconv of type MTS?
Did you try inserting a record with nano seconds from command line? does that work?

asitagrawal · Post by **asitagrawal** » Mon Oct 23, 2006 1:42 pm

The datatype is TImestamp.
I have verified, populating, a Seq File, that the nano seconds are coming properly....
Also, am not using any othe Trans (Iconv or Oconv)...
I ahve also tried inserting from Command Line...it is going abs fine...
but the prob is coming from Ascential to DB2.....

Even tried by hard coding a vakue like. 2006-12-12 12:15:16.123456..
nut it is going like 2006-12-12 12:15:16.000000!

bmarko22 · Post by **bmarko22** » Sat Nov 21, 2009 11:16 pm

Are you setting microseconds on table definition of source and target? Also, you say you are looking for (YYYY-DD-MM HH:MI:SS.SSSSSS) I was able to get timestamp with milliseconds by having it converted to %yyyy-%mm-%dd %hh:%nn:%ss.6 in the format.