Not able to retrieve timestamps column from database

oacvb · Post by **oacvb** » Wed Dec 21, 2005 5:34 am

Hi,

I am retrieving a set of columns from a table, one of the column is timestamp (Create_Ts) and directly writing into a file. I gave timestamp in datastage stages (Both OCI and File). But it is coming blank in file. Size of the timestamp columns in both the stages are 19. Even if i increase the column size the field is blank in the file.

I can make it varchar in both the ends, incase if at all want to do some transaction at trasnformer, what should i do?

Regards,
Venkatesh Babu Obla

richdhan · Post by **richdhan** » Wed Dec 21, 2005 6:22 am

Hi,

1. Have you used the View Data option in OCI.

2. Load the metadata from Datastage Repository. Usually it is Timestamp 38.

Pls let us know what happens.

Rich

Tasneem · Post by **Tasneem** » Wed Dec 21, 2005 6:40 am

Hi,
If u r using OCI load stage ,on the Properties page in the Date format try using this format :YYYY-MM-DD.
Just chg those formats and try.

kduke · Post by **kduke** » Wed Dec 21, 2005 8:36 am

I think varchar on both ends works great. It stores it internally as varchar anyway. DataStage itself is faster in a server job to use varchar on most everything. You only need the real type on a plugin like a bulk load. DataStage will handle the conversion at that point.

PX jobs are very different in regards to metadata like this.

chulett · Post by **chulett** » Wed Dec 21, 2005 8:41 am

What is the datatype in Oracle? Is it a DATE field or actually one of the newer TIMESTAMP types? If so, which exact type?

kduke · Post by **kduke** » Wed Dec 21, 2005 8:44 am

Craig is correct. The newer date types added milliseconds and some of the routines break. DataStage itself may not handle a new data type on a new release of a database. You may have to use varchar in that case.