I am saving a TIMESTAMP into a TIMESTAMP WITH TIME ZONE column in an Oracle stage. However, I want to
A) Store it in a different time zone than the default.
B) Preserve the Time Zone when extracting the data and moving to other Oracle databases.
Does anyone have suggestions? Here is what I've been able to find and try with limited success:
DataStage apparently does not recognize or support the "WITH TIME ZONE" capability, and the Oracle stage does not seem to honor the "Additional Connection Option" to modify the default Time Zone for a connection (ORA_SDTZ = 'DB_TZ' | 'OS_TZ' | '[+|-]HH:MI' | 'timezone_name'). I also tried to add ALTER SESSION code to the Insert statement in the Oracle stage (ALTER SESSION SET TIME_ZONE = '-2:00'; ) but that fails at run time, I think due to the ";".
I have considered three workarounds and combinations thereof, each with drawbacks:
1) Convert to UTC when extracting the data. Unfortunately, the original time zone information is permanently lost at that point unless I carry it through as a separate column.
2) Carry the DateTimes as strings in DataStage. This preserves the Time Zone but requires lots of converting if any time manipulation is required, and at that point maintaining the original time zone is complex.
3) Experiment with UNIX $TZ environment variables. This may help with actually inserting the data into Oracle, but doesn't help with internal DataStage manipulation.
On a related note, will DataStage Version 8x do a better job with TIMESTAMP/TIME ZONE support?
Oracle TIMESTAMP WITH TIME ZONE datatype.
Moderators: chulett, rschirm, roy
-
mjmagalsky
- Participant
- Posts: 5
- Joined: Wed Apr 18, 2007 2:38 pm
Luckily, I haven't had the pleasure of dealing with TIMESTAMP WITH TIMEZONE data types in Oracle, so can't really offer an advice from personal experience.
You are correct in that DataStage only officially supports DATE fields, not any flavor of the true TIMESTAMP types. At least that's true for any 7.x version, I can't speak to 8.x and if it supports them. However, custom SQL should be up to the task, I would think.
I've done plenty of 'before sql' alter sessions in the OCI stage, but not that one specifically. You are correct that the trailing semi-colon isn't needed unless multiple statements are involved, but I don't recall them causing runtime problems if included. Easy enough to check by removing it. Did you try that?
I would suggest setting up ORA_SDTZ in the Administrator as a 'User Defined environment variable'. Create it there with its current 'normal' value so as to not effect any other job, as all jobs will have it set at runtime the moment you create it. Then in this job, specifically add it as a parameter using the 'Add Environment Variable' button and override the default value to be what you need to play this timezone game. If you think that's key to getting things working as you need, that should bring it into play in the job.
You are correct in that DataStage only officially supports DATE fields, not any flavor of the true TIMESTAMP types. At least that's true for any 7.x version, I can't speak to 8.x and if it supports them. However, custom SQL should be up to the task, I would think.
I've done plenty of 'before sql' alter sessions in the OCI stage, but not that one specifically. You are correct that the trailing semi-colon isn't needed unless multiple statements are involved, but I don't recall them causing runtime problems if included. Easy enough to check by removing it. Did you try that?
I would suggest setting up ORA_SDTZ in the Administrator as a 'User Defined environment variable'. Create it there with its current 'normal' value so as to not effect any other job, as all jobs will have it set at runtime the moment you create it. Then in this job, specifically add it as a parameter using the 'Add Environment Variable' button and override the default value to be what you need to play this timezone game. If you think that's key to getting things working as you need, that should bring it into play in the job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
mjmagalsky
- Participant
- Posts: 5
- Joined: Wed Apr 18, 2007 2:38 pm
I tried that, to no avail. Without the ";", I still get this at runtime:chulett wrote: I've done plenty of 'before sql' alter sessions in the OCI stage, but not that one specifically. You are correct that the trailing semi-colon isn't needed unless multiple statements are involved, but I don't recall them causing runtime problems if included. Easy enough to check by removing it. Did you try that?
ORA-00922: missing or invalid option
That worked well. It accomplished the task of inserting values sensitive to the correct new time zone. Thank you.chulett wrote: I would suggest setting up ORA_SDTZ in the Administrator as a 'User Defined environment variable'. Create it there with its current 'normal' value so as to not effect any other job, as all jobs will have it set at runtime the moment you create it. Then in this job, specifically add it as a parameter using the 'Add Environment Variable' button and override the default value to be what you need to play this timezone game. If you think that's key to getting things working as you need, that should bring it into play in the job.
As for issue B) Preserve the Time Zone when extracting the data and moving to other Oracle databases.
I expect there's no easy DataStage workaround short of handling the TimeStamp as a separate column from the Time Zone. If anyone has other ideas, please post.
Thanks!