Exporting NaN/+-Infinity Value of Double/Real/Timestamp Datatype from Aster to Microsoft SQL Server - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software
Problem: Export double/real/timestamp data from Aster to SQLServer, if double/real data has the special values infinity/-infinity/NaN or timestamp data has special values infinity/-infinity, the migration result is different between INFA server on Windows and Linux:
  • [Windows] Data, except for the special values mentioned above, is exported to SQL Server successfully.
  • [Linux] No data can be exported to SQL Server even though INFA monitor shows the status as successful.

Analysis: The values NaN, +/- infinity are considered bad values for a SQL Server float field for both the Windows and Red Hat sessions. For Windows, Informatica uses Microsoft OLE DB Provider for SQL Server to load the data to SQL Server, and it successfully loads the rows with valid values and excludes the rows with the bad values. But for Red Hat, Informatica uses a legacy SQL Server ODBC driver that employs a different loading mechanism called Tabular Data Stream (TDS) protocol, which appears to behave differently in the presence of invalid data values. This issue results from the different ways SQL Server handles data loading from Windows and Linux sources in the presence of invalid values.

Workaround: You must remove the rows with NaN, +/- INF values from the source table and make sure the data loads properly.