Unable to fetch microseconds from timestamp

[SAMEENA]

I am working on a job which is used for incremental loading depending upon the timestamp of the source records.

Now during execution it does not consider the microseconds.For eg: if the time stamp in source is 2006-08-25-01.02.42.764123 ; it executes the job taking the timestamp as 2006-08-25-01.02.42.000000 so more number of records get loaded in the target and also the data that gets loaded in the target column is 2006-08-25-01.02.42.000000.

I tried changing the format of timestamp for my project to 'yyyy-mm-hh-dd hh:mm:ss.SSSSSS' through administrator.Still the same problem persists.

My source and target stages are DB2/UDB API.

[keshav0307]

did you also try the extended property of timestamp to millisecods

[SAMEENA]

Hi Keshav,
Thanks for the reply.

Can you please explain how do we set this property of extending timestamp to milliseconds in parallel jobs.

[keshav0307]

in the field name, right click and and click on the proerty, and check the extended. if the column is timestamp, select microseconds from the drop down

[SAMEENA]

Hi Keshav,
I tried that but still it is giving microseconds as '000000'.

[ray.wurlod]

I'm sure this has been answered before. Did you do a Search for "microseconds"?

[SAMEENA]

Hi Ray!

I searched for it.There are indeed quite a few posts regarding this issue,but the issue has not been resolved so far,just a workaroung has been suggested..............to use varchar as datatype.

I tried writing in the database with varchar as data type.Its working.

Thank you and sorry for not searching it before and posting it under a new topic.

[SAMEENA]

I am still looking for suggestions as to what settings are required in this case if i need to work with Timestamp datatype only.

Please tell me.It would be of great help.

Thanks in advance.

[abdulgani]

Hi,

Extract as varchar and try convert function StringToTimestamp

I am working on a job which is used for incremental loading depending upon the timestamp of the source records.

Now during execution it does not consider the microseconds.For eg: if the time stamp in source is 2006-08-25-01.02.42.764123 ; it executes the job taking the timestamp as 2006-08-25-01.02.42.000000 so more number of records get loaded in the target and also the data that gets loaded in the target column is 2006-08-25-01.02.42.000000.

I tried changing the format of timestamp for my project to 'yyyy-mm-hh-dd hh:mm:ss.SSSSSS' through administrator.Still the same problem persists.

My source and target stages are DB2/UDB API.

[ray.wurlod]

The Transformer stage can still output a VarChar, then you use a downstream stage (another Transformer or a Modify stage) to convert the data type. Whether this will conserve the microseconds I am not sure, but it's the approach I'd investigate. My guess is that, if the timestamp format string includes microseconds, then they'd be handled properly by timestamp_from_string() function.

[SAMEENA]

Hi ,

When I keep format of timestamp as %yyyy-%mm-%dd %hh:%nn:%ss.6
its fetching microseconds but while writing in target which is DB2/UDB API stage ,it is truncating microseconds; even if I fetch microseconds separately and concatenate it .But I can populate it in a sequential file with data type as timestamp.So just wanted to know is it a problem with DB2/UDB API stage?

[salil]

Try a manual insert into the Database from backend to check whether ur DB settings allows the data in the exact timestamp format.

[SAMEENA]

Hi Salil,
I am able to insert timestamp with microseconds from backend.

[salil]

Hi Salil,
I am able to insert timestamp with microseconds from backend.

What if u use the same(from backend)timestamp formatting in the user defined insert statemnt inside ur DB2 stage??

[SAMEENA]

Hi Salil,
I tried to insert same timestamp through my stage,its truncating the microseconds