Page 1 of 1
Timestamp and DB2
Posted: Mon Oct 23, 2006 11:18 am
by asitagrawal
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.???
Posted: Mon Oct 23, 2006 11:20 am
by Krazykoolrohit
can you post an exapmle?
Posted: Mon Oct 23, 2006 11:32 am
by asitagrawal
Krazykoolrohit wrote:can you post an exapmle?
INSERT INTO FDM1PA.PS_ASCDTTM (JOB,ASC_DTTM) VALUES (?,?);
Posted: Mon Oct 23, 2006 12:09 pm
by Krazykoolrohit
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.
Posted: Mon Oct 23, 2006 12:18 pm
by asitagrawal
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'
Posted: Mon Oct 23, 2006 12:22 pm
by DSguru2B
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?
Posted: Mon Oct 23, 2006 12:38 pm
by asitagrawal
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...
Posted: Mon Oct 23, 2006 12:46 pm
by DeepakCorning
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.
Posted: Mon Oct 23, 2006 12:54 pm
by DSguru2B
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?
Posted: Mon Oct 23, 2006 1:42 pm
by asitagrawal
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!
Posted: Sat Nov 21, 2009 11:16 pm
by bmarko22
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.