When migrating data from DB2 to Netezza, it’s important to understand how data types are mapped between the two platforms. This will help ensure that data is transferred accurately and efficiently, without any loss of information. This technical article will explore how data types are mapped from DB2 to Netezza.
DB2 and Netezza Data Types DB2 and Netezza have several data types in common, but there are also some differences between the two platforms. DB2 supports various data types such as INTEGER, DECIMAL, CHAR, VARCHAR, and DATE, while Netezza supports a similar set of data types, such as INTEGER, DECIMAL, CHAR, VARCHAR, and DATE. However, there are some differences in how these data types are defined and used.
Mapping DB2 Data Types to Netezza Data Types When mapping DB2 data types to Netezza data types, it’s important to consider the following:
- Integer Data Types: DB2’s INTEGER data type maps directly to Netezza’s INTEGER data type. However, DB2’s BIGINT data type maps to Netezza’s BIGINT data type.
- Decimal Data Types: DB2’s DECIMAL data type maps directly to Netezza’s DECIMAL data type, but there are differences in how these data types are defined. DB2 supports a maximum precision of 31 digits, while Netezza supports a maximum precision of 38 digits.
- Character Data Types: DB2’s CHAR and VARCHAR data types map directly to Netezza’s CHAR and VARCHAR data types, respectively. However, there are differences in how these data types are defined. Netezza supports a maximum length of 64K for both CHAR and VARCHAR, while DB2 supports a maximum length of 32K for CHAR and 32,672 for VARCHAR.
- Date Data Types: Both DB2 and Netezza support the DATE data type, and there is no mapping required for this data type.
Mapping data types from DB2 to Netezza is an essential step in the data migration process. By understanding the differences between data types in both platforms and how they are mapped, you can ensure that your data is transferred accurately and efficiently. It’s important to test the data migration thoroughly to ensure that all data has been transferred correctly and that there are no data type conversion issues.
DB2 To Netezza Data Type Mapping Table
This is an extracted table I created from the IBM source a while back when investigating in what format to convert data fields into for IBM Infosphere DataStage. I have had it floating around in my notes, but lately, I have found myself referencing it to help other team members, so it seems useful to include it here. The notes column is just a few snippets of information, which I have found useful to reference when planning data field conversions.
| Transformer data type | DB2 data type | Netezza data type | Notes |
| BIGINT | BIGINT | BIGINT | |
| BINARY | CHARACTER | Not supported | |
| BIT | SMALLINT | BOOLEAN | |
| CHAR | CHARACTER | CHAR | |
| DATE | DATE | DATE | |
| DECIMAL | DECIMAL | NUMERIC or DECIMAL | |
| DOUBLE | DOUBLE | DOUBLE PRECISION or FLOAT(15) | |
| FLOAT | DOUBLE | FLOAT | |
| INTEGER | INTEGER | INTEGER | |
| LONGVARBINARY | BLOB | Not supported | |
| LONGVARCHAR | CLOB | VARCHAR | The maximum character string size is 64,000. |
| NUMERIC | DECIMAL | NUMERIC | |
| REAL | REAL | REAL or FLOAT(6) | |
| SMALLINT | SMALLINT | SMALLINT | |
| TIME | TIME | TIME | DB2 9.1 TIME data type does not support fractional digits or microseconds. |
| TIMESTAMP | TIMESTAMP | TIMESTAMP | DB2 9.1 TIME data type does not support fractional digits or microseconds. |
| TINYINT | SMALLINT | BYTEINT | |
| VARBINARY | VARCHAR | Not supported | |
| VARCHAR | VARCHAR | VARCHAR | The maximum character string size is 64,000. |
| WCHAR | GRAPHIC | Not supported | |
| WLONGVARCHAR | LONG VARGRAPHIC | Not supported | |
| WVARCHAR | VARGRAPHIC | Not supported |
