Date fields being created as timestamp(0)

[PaulS]

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)

[ray.wurlod]

Oracle DATE data type allows for storage of time. In effect, it's a timestamp.

Oracle is... different.

[PaulS]

Indeed!

I've been searching through the forums - is there a utility to convert all ORAOCI9 stages to the new DS8.5 ORAOCI stage type? Anyone know how to get to it?

[qt_ky]

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?

[PaulS]

The database has been created with NLS_NCHAR_CHARACTERSET = AL16UTF16. The datastage oci stage is at UTF8.

Any idea how I install the correct characterset?

[qt_ky]

NLS_DATE_FORMAT is the setting that relates to what you're seeing on DATE fields. Oracle has many such NLS settings. Have you compared all of them across servers?

[PaulS]

Hi qt_ky,

Thanks for your help on this! Can I get the NLS_DATE_FORMAT in datastage?

The problem I'm having is - the migrated ORAOCI9 stages are creating tables (when specifically creating as DATE in user-defined DDL), as TIMESTAMP(0). The newer 8.5 ORAOCI stages do not, they are created as DATE.

[chulett]

Sounds to me like something to take to your official support provider. I seriously doubt NLS anything controls that.

[PaulS]

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)

[qt_ky]

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.

[PaulS]

I fixed this with a user defined environment variable.... DS_NO_FF=1

Thanks for all the help I got from here!

[PaulS]

I fixed this with a user defined environment variable.... DS_NO_FF=1

Thanks for all the help I got from here!

[chulett]

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.

[qt_ky]

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