TimeStamp Format to read MicroSecond from Oracle RDB

[sohasaid]

I need to read the microseconds from timestamp column from Oracle RDB 7.1 (as a data source) to Oracle 10g as a destination but I couldn't. The default timestamp format at the job & projet level is %yyyy-%mm-%dd %hh:%nn:%ss.

I found the following solutions on the forum but it didn't work:

1- change the default timestamp format to %yyyy-%mm-%dd %hh:%nn:%ss.x where x is a no between 0 and 6 for the microseconds.

2- converting the timestamp column as Char (26), but I have hundreds of jobs which include timestamp columns so I can't convert each column per
job.

So, I need the required fromt to read the microseconds.

Any recommendations will be appreciated.

Regards.

[chulett]

Umm... I don't recall the 7.x version of Oracle having any kind of a TIMESTAMP column.

[sohasaid]

Thank you for your reply, Chulett.

I was talking about Oracle RDB v7.1 not Oracle v7. This is another version of Oracle, it's an older one which has different structure other than the relational Oracle database and which has this form of timestamp e.g. "2009-06-23 10:49:22.79".

It's defined as 'Date' in the Oracle RDB but DataStage interprets it as TimeStamp without reading the microseconds part e.g. "2009-06-23 10:49:22".

Regards.

[ArndW]

Did you also mark the column as using "extended", if not then the microseconds would get truncated even if the format correctly parsed the timestamp.

[sohasaid]

Andrew,

I followed your suggestions but DataStage reads all microseconds as zeros. For example, at the database is "2008-02-18 08:31:39.12" and DataStage interprets it as "2008-02-18 08:31:39.00"? That's first.

Second, I've a bigger problem, as I mentioned above hundreds of jobs have been developed. So, I'm looking for more generic solution on the project level such as changing the default format for the timestamp for the whole project, Is that possible?

Thanks a lot for your co-operation.

[ArndW]

The first thing you need to do is to get some solution for just one job that works. Once that is done you can figure out a way to generalize that solution for the whole project - this might involve doing a project export into a .dsx file and a global replace or perhaps even more involved manipulation of that file.
I have to admit that I've tried changing the default format for the date/timestamp before with no success, but that was at Version 7 and not the current Version 8. I've not worked with your version of the database so cannot offer any help in that direction, if you have no success from this forum then it might be best to involve your service provider and, when you get a solution, please to post it to this thread so others can benefit.

[sohasaid]

So what about reading the microseconds as zeros from DataStage when setting the extended property to Microseconds to a timestamp column?

Regarding the other issue I will assure to share the solution once I got it.

Thanks a lot, Andrew.

[sohasaid]

So what about reading the microseconds as zeros from DataStage when setting the extended property to Microseconds to a timestamp column?

Regarding the other issue I will assure to share the solution once I got it.

Thanks a lot, Andrew.

[mgendy]

The first thing you need to do is to get some solution for just one job that works. Once that is done you can figure out a way to generalize that solution for the whole project - this might involve doing a project export into a .dsx file and a global replace or perhaps even more involved manipulation of that file.

I Agree With the first part of arndw reply , you've mentioned before that when you use char(26) its works , so the plan now is
1 - export one jobe as dsx file
2 - open the job with the designer then change the datatype to char(26) 'as you said , i didn't try it'
3 - save and compile the job then export it again
4 - open the two dsx files with any text editor 'something like notepad++' then fine the changes happened
5 - export the the whole project into dsx file then replace the timesamp with the difference you found in the two dsxs
6 - import the dsx file again with overwite option 'don't forget to backaup your jobs' then use the multiple job compile tool

enjoy the workaround , i've do many things on the same mannar

[priyadarshikunal]

try timestamp with scale as 6, leave precision blank and see if that works.

[sohasaid]

Unfortunately It didn't work. DataStage still read it as Zeros.

[chulett]

Have you involved your official support provider yet? Asked them?

[bmarko22]

I'm in a DB2 shop so not sure if this applies to your situation but I was able to set the default timestamp at the project level. In the admin tool there is a defaults tab. Just add the .6 there.

[sohasaid]

Not Yet, Chulett.

[bmarko22]

I'm in a DB2 shop so not sure if this applies to your situation but I was able to set the default timestamp at the project level. In the admin tool there is a defaults tab. Just add the %yyyy-%mm-%dd %hh:%nn:%ss.6 there. All this really does is set microseconds in the table definition but worked for us. Have to say it's very surprising that in a DB2 stage the default is not to the millisecond as that's what a DB2 timestamp is. Might open a PMR to find out why as I'm with IBM.