Hi all,
I have a target oracle table with a few columns declared as NUMBER. I'm getting a warning in the execution of the job that inserts data to this table. the warning is " When checking operator: Column ID_CONTRAT floating point decimal not fully supported. Adjusting scale." the column ID_CONTRAT is declared as number in oracle table and the metadata in datastage is decimal(38,10).
The job does what i need it to do but with warnings for each column declared as number.
I have tried to change the types of my columns to Numeric, Integer, and Bigint but it doesn't change the warning, instead of that it adds more warnings concerning the implicit conversion.
PS: when I change the target to a dataset, the job run perfectly well without warnings.
I'll be very grateful if anybody can help
warning on insert decimal with oracle stage load append mode
Moderators: chulett, rschirm, roy
-
throbinson
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
An Oracle NUMBER without precision or scale (A floating point number) is interpreted by Datastage as DECIMAL(38,10). Also any function call resulting in a numeric result.
One CANNOT change this from the column grid as any metadata you define in the job will be overridden when the stage executes.
Datastage will insist it is a DECIMAL(38,10). Changing the datatype in DataStage will only add warnings as you have found if there is then an implicit conversion problem.
Numerous workarounds have been put forth as you will find when you do a search.
Why would Datastage do that? Why DECIMAL(38,10)? I do not know.
I do know there is a patch (at least for AIX 7.5.2) to the Oracle Enterprise stage that makes the warning go away although the DECIMAL(38,10) remains. Does this patch then mask a potential problem by lulling us into a false sense of security? Only if your job is dealing with the U.S government deficit or the number of meters to Mars and back.
Request the patch from IBM.
One CANNOT change this from the column grid as any metadata you define in the job will be overridden when the stage executes.
Datastage will insist it is a DECIMAL(38,10). Changing the datatype in DataStage will only add warnings as you have found if there is then an implicit conversion problem.
Numerous workarounds have been put forth as you will find when you do a search.
Why would Datastage do that? Why DECIMAL(38,10)? I do not know.
I do know there is a patch (at least for AIX 7.5.2) to the Oracle Enterprise stage that makes the warning go away although the DECIMAL(38,10) remains. Does this patch then mask a potential problem by lulling us into a false sense of security? Only if your job is dealing with the U.S government deficit or the number of meters to Mars and back.
Request the patch from IBM.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Because that's what Oracle reports when any client tool retrieves the table definition using SQLColumnAttributes() function and the Oracle column is of unqualified type NUMBER.throbinson wrote:Why would Datastage do that? Why DECIMAL(38,10)? I do not know.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
warning on insert decimal with oracle stage load append mode
Thanks all for your help.
I didn't find the way to avoid the warning in datastage. The alternative solution is to change in the target database (Oracle) the type of my columns from NUMBER to NUMBER(38,10).
I didn't find the way to avoid the warning in datastage. The alternative solution is to change in the target database (Oracle) the type of my columns from NUMBER to NUMBER(38,10).