GMT conversion before loading to Oracle Exadata

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
DSFreddie
Participant
Posts: 130
Joined: Wed Nov 25, 2009 2:16 pm

GMT conversion before loading to Oracle Exadata

Post by DSFreddie »

Hi All,

Are there any ways to convert a current timestamp to GMT format using Datastage prior to loading to Oracle Exadata ? I couldn't find any specific functions in transformer nor arrive at a solution.

Any help/thoughts are appreciated.



Thanks
Freddie
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Do you have a specific format in mind? (For example, the military use a Z to indicate GMT.)

The TimestampOffsetByComponents() function is probably the easiest to use (assuming you already have a timestamp) to handle the difference in hours. Interrogate the TZ environment variable to determine the number of hours to shift.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSFreddie
Participant
Posts: 130
Joined: Wed Nov 25, 2009 2:16 pm

Post by DSFreddie »

Thanks for your reply Ray.

There is no specific format requirement, all we need to do is convert the current date to a valid GMT Timestamp format.

Pls let me know if TimestampOffsetByComponents() function will work in this case.

Thanks
Freddie
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Timestamp and Date are different in DataStage, unlike Oracle. If you have a date, then you may need to convert that to a timestamp before providing it as an argument to TimestampOffsetByComponents(). Or you can rely on it being handled properly by DataStage as an implicit conversion.

Yes, it will work.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply