Date fields being created as timestamp(0)
Moderators: chulett, rschirm, roy
Date fields being created as timestamp(0)
Hi,
We have a new install of DS8.5 (upgrading from 7.5.2), pumping data into an oracle 11g database.. and hit an odd problem.
No matter what I do with the create_table statement, user defined or generated DDL,.. the date fields create as timestamp(0). The columns coming in from the ORAOCI9i stage are Timestamp. I have to_date() surrounding the fields on the insert statements.
Thanks in advance
Paul
Generated DDL
CREATE TABLE statement:
CREATE TABLE #TartgetTable# (
FIELD_ID DECIMAL(19)
LOAD_DATE DATE)
Desc Target_table
FIELD_ID NUMBER(19)
LOAD_DATE TIMESTAMP(0)
We have a new install of DS8.5 (upgrading from 7.5.2), pumping data into an oracle 11g database.. and hit an odd problem.
No matter what I do with the create_table statement, user defined or generated DDL,.. the date fields create as timestamp(0). The columns coming in from the ORAOCI9i stage are Timestamp. I have to_date() surrounding the fields on the insert statements.
Thanks in advance
Paul
Generated DDL
CREATE TABLE statement:
CREATE TABLE #TartgetTable# (
FIELD_ID DECIMAL(19)
LOAD_DATE DATE)
Desc Target_table
FIELD_ID NUMBER(19)
LOAD_DATE TIMESTAMP(0)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Oracle DATE type = "Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone"
- http://docs.oracle.com/cd/B19306_01/ser ... nts001.htm
What is the odd part of the problem?
How is your default NLS_DATE_FORMAT set on the Oracle server and from each Oracle client?
- http://docs.oracle.com/cd/B19306_01/ser ... nts001.htm
What is the odd part of the problem?
How is your default NLS_DATE_FORMAT set on the Oracle server and from each Oracle client?
Choose a job you love, and you will never have to work a day in your life. - Confucius
Yeah, I've raised it with IBM - I'm waiting on the response,.. I'm sure they'll come back with a request for more info on a 'must responsed by SLA'.
I would have been insteresting to see if anyone had done this migration in the real world (7.5.2 to 8.5) and if they'd hit the same issues with dates being created as timestamp(0)
I would have been insteresting to see if anyone had done this migration in the real world (7.5.2 to 8.5) and if they'd hit the same issues with dates being created as timestamp(0)
Sorry I haven't hit that issue. It sounds like DATE and TIMESTAMP(0) are equivalent from Oracle's view, but I can see how that can cause you problems.
You can check and compare all the Oracle NLS settings from within each Oracle Client installed on each DataStage Server.
You can check and compare all the Oracle NLS settings from within each Oracle Client installed on each DataStage Server.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Aha! :D
You can search for that variable name here and see the other discussions where it comes up and it's usually an "oh yah, forgot about that" moment as it doesn't come up very often. From what I recall, it has been mentioned earlier when milliseconds have been added to a DATE field by the stage, making it invalid. Odd that you have to force it on to get it to create DATE fields correctly, but glad you got it sorted out.
Did you report this back to support? Sounds like something they should... fix.
You can search for that variable name here and see the other discussions where it comes up and it's usually an "oh yah, forgot about that" moment as it doesn't come up very often. From what I recall, it has been mentioned earlier when milliseconds have been added to a DATE field by the stage, making it invalid. Odd that you have to force it on to get it to create DATE fields correctly, but glad you got it sorted out.
Did you report this back to support? Sounds like something they should... fix.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The DS_NO_FF setting is NOT mentioned in the 8.7 parallel guides (regular or advanced) and not really explained in any other post here.
FF is a reference to Oracle's fractional seconds. The DS_NO_FF setting is explained here:
http://www-01.ibm.com/support/docview.w ... wg21388363
FF is a reference to Oracle's fractional seconds. The DS_NO_FF setting is explained here:
http://www-01.ibm.com/support/docview.w ... wg21388363
Choose a job you love, and you will never have to work a day in your life. - Confucius