Reimporting a permanent table is changing data types

[Former Member]

Hi,

I had a template table that was imported as a permanent from an Oracle 11g database.

I'm using DS 14.2.2.596.

Field types Int and Date were kept ("Schema Out") as in the "Schema In" during the first import:

ATL code:

RECORD INT  NULL  SET("ui_mapping_text" = 'Query.RECORD'),  

DATE_OF_BIRTH DATE  NULL  SET("ui_mapping_text" = 'Query.DATE_OF_BIRTH'),

But if I reimport the same permanent table again, those field types are changed as follows:

Int    ---> Decimal (28,0)

Date ---> Datetime

This is causing errors in the jobs execution.

ATL code:

RECORD DECIMAL(28, 0)  NULL ,

DATE_OF_BIRTH DATETIME(0)  NULL ,

All the attribute parameters in the "Tools/Options/Designer/Attribute Values" are set to "Preserve".

In older DI versions there was a utility "fixupUIMapText" to fix issues regarding the attribute "ui_mapping_text".

Can anyone have had this same Data Services issue?

Thanks,

Francisco

[Former Member]

when you select INT as datatype for Oracle template table, the table column will be actually created as NUMBER datatype

when import this table to make it a regular table and reimport Data Services will update the metadata and Oracle NUMBER datatype will be imported as DECIMAL(28,0)

I don't think this will affect the loading ? what is the issue with the datatype change after reimport is the load failing ? are you getting incorrect results ?

[Former Member]

You’re right Dhyani, the Int conversion to decimal is not affecting the results.

The issue is more related to date conversion date --> datetime during the re-importing.

Even making new DS queries against that table is generating errors as we cannot make them against timestamps.

The workaround is to rewrite manually the Oracle table in the schema to its original definition, but if you have hundreds of tables it would take time.

Why DS is changing the Date data type? and what does need to be done to reimport it correctly?