Posted: Fri Feb 02, 2007 8:46 am
DSguru2B wrote:The issue remains with the metadata, rather than data itself. Both hold different scales which is causing the problem.
To go a littler farther, the issue is with the metadata dictated by the DataDirect driver used by DataStage.
-- The driver for SQL Server says a datetime has a length of 23 and a scale of 3 (e.g., 2007-02-02 08:05:01.000), which matches the data in SQL Server.
-- The driver for Informix says when a datetime is defined as year to fraction(x) (where x can be 1 to 5) it always maps it to fraction(5), even if the database really has the field defined as year to fraction(3). This always makes the length for Informix 25 and the scale 5 regardless of how the data is actually defined in the database.
So even though the underlying data really has the same length and scale, I can't use the same ODBC stage to load both databases, because the database driver forces different metadata.