IBM PureData® System for Analytics (Netezza) and Db2 differences in non-internal data types

Some non-internal data types that are supported in both Netezza® and Db2® products are supported differently, as described in this topic. For information about Db2 support for internal Netezza data types, such as ROWID, see IBM PureData System for Analytics (Netezza) and Db2 differences in internal data types.

The following table describes differences in support and recommendations for migrating to a Db2 product if the differences result in problems. IBM® Database Harmony Profiler maps Netezza data types to Db2 data types and, in some cases, can perform the changes that are recommended in the table. Carefully review the Database Harmony Profiler assessment report to understand the conversions and to learn about differences that might require some changes to queries and reporting. Data truncation might occur as a result of conversion.

Table 1. Netezza and Db2 data type differences
Data type	Difference	Recommendations for migrating
CHAR	The maximum length for the Db2 data type is 255 bytes. The maximum length for the Netezza data type is 64,000 bytes.	Check whether you can redefine the length of the data type to comply with the Db2 limit. If not, use the VARCHAR data type. Database Harmony Profiler converts CHAR(`n`) values where `n` is 255 - 32,592 to VARCHAR(`n`). Database Harmony Profiler also converts CHAR(`n`) values where `n` is greater than 32,592 to VARCHAR(32592).
VARCHAR	The maximum length for the Db2 data type is 32,592 bytes. The maximum length for the Netezza data type is 64,000 bytes.	Check whether you can redefine the length of the data type to comply with the Db2 limit. If not, use the CLOB data type in a row-organized table. Database Harmony Profiler converts CHAR(`n`) values where `n` is greater than 32,592 to VARCHAR(32592).
NCHAR	The maximum length for the Db2 data type is 63 bytes. The maximum length for the Netezza data type is 16,000 bytes.	Check whether you can redefine the length of the data type to comply with the Db2 limit. If not, use the NVARCHAR data type. Database Harmony Profiler converts NCHAR(`n`) values where `n` is 64 - 8148 to NVARCHAR(`n`). Database Harmony Profiler also converts NCHAR(`n`) values where `n` is greater than 8148 to NVARCHAR(8148).
NVARCHAR	The maximum length for the Db2 data type is 8148 bytes. The maximum length for the Netezza data type is 16,000 bytes.	Check whether you can redefine the length of the data type to comply with the Db2 limit. If not, use an NCLOB data type in a row-organized table. Database Harmony Profiler converts NVARCHAR(`n`) values where `n` is greater than 8148 to NVARCHAR(8148).
VARBINARY	The maximum length for the Db2 data type is 32,592 bytes. The maximum length for the Netezza VARBINARY and ST_GEOMETRY data types is 64,000 bytes.	Check whether you can redefine the length of the data type to comply with the Db2 limit. If not, use a BLOB data type in a row-organized table. Database Harmony Profiler converts VARBINARY(`n`) values where `n` is greater than 32592 to VARBINARY(32592).
DECIMAL (NUMERIC)	The maximum precision of the Db2 data type is 31 digits. The maximum precision of the Netezza data type is 38 digits.	Check whether you can redefine the precision of the data type to comply with the Db2 limit. If not, consider using the DECFLOAT data type, which has a maximum precision of 34 digits. Database Harmony Profiler converts DECIMAL(`p`,`s`) values where `p` is greater than 31 to DECIMAL(31,`s`). Similarly, Database Harmony Profiler converts NUMERIC(`p`,`s`) values where `p` is greater than 31 to NUMERIC(31,`s`).
Floating-point types	If you specify a precision (n) for a Db2 floating-point type, SQL standard precision ranges apply. Netezza does not use the standard precision ranges.	If you specify a precision, ensure that you are using the appropriate type (REAL or DOUBLE). It is preferable to change Netezza types with explicit precisions, as follows: If you were using the Netezza DOUBLE PRECISION or FLOAT(15) type, change to the Db2 FLOAT type. If you were using the Netezza REAL or FLOAT(6) type, change to the Db2 REAL type.
TIME	The Db2 TIME data type does not support the microseconds portion of the Netezza TIME data type (for example, 999999 in the value 23:59:59.999999).	Check whether you require microsecond-level precision and can therefore use the TIME data type. If not, use the TIMESTAMP data type.