DSXchange

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.

did you also try the extended property of timestamp to millisecods

Hi Keshav,
Thanks for the reply.

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

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

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

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

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.

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.

Hi,

Extract as varchar and try convert function StringToTimestamp

SAMEENA wrote: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.

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.

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?

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

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

SAMEENA wrote: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??

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