IBM PureData System for Analytics (Netezza) and Db2 database, table, and schema compatibility

In a PureData® System for Analytics Netezza®) system, you can have multiple databases. In a Db2® system, there is one database, named BLUDB.

By default, when you move Netezza databases to a Db2 system by using the db_migrate command or Database Harmony Profiler, the Netezza databases are migrated to schemas within the BLUDB database. For information about the db_migrate command, see Moving data using db_migrate; for information about Database Harmony Profiler, see the Database Conversion Workbench page. If you configured your Netezza databases to use multiple schemas, the tools move the Netezza schemas to the BLUDB database, but if you are using the multiple schemas within multiple databases, additional work is required before you migrate. For information about how to proceed with your migration if you are using multiple databases with multiple schemas, see IBM PureData System for Analytics (Netezza) configuration choices that impact migration to Db2products.

Other notable differences between a Netezza system and a Db2 include:
  • By default, data in Db2 tables is organized by column (it is possible to organize data by row). Organizing data by column reduces the amount of I/O that is needed for processing a query because only the columns that are referenced in the query must be loaded into memory from disk. A column-based organization benefits analytic queries that access a large number of values from a subset of the columns and heavily use aggregations and joins.
  • A Db2 system uses UTF-8 encoding. A Netezza system uses Latin-9 encoding for single-byte characters and UTF-8 for multibyte characters.
  • Clustered base tables (CBTs) are not supported in a Db2 system.
  • Materialized views are not supported in a Db2 system. Materialized query tables (MQTs) might be a suitable alternative. An MQT is a table whose definition is based on the result of a query and whose data is in the form of precomputed results that are taken from the table or tables on which the MQT definition is based.
  • In a Db2 system, whether primary key constraints are enforced by default is determined by the setting of the ddl_constraint_def configuration parameter. The default setting of this parameter is NO (for NOT ENFORCED). You can override the default behavior by explicitly specifying either ENFORCED or NOT ENFORCED in your DDL statements.
  • Unlike Netezza, when Db2 performs query optimization, it always trusts that primary keys are unique. Consequently, if primary key constraints are not enforced, and if a primary key column contains duplicate values, query results might be incorrect.

For details about differences between Netezza and Db2 DDL, see IBM PureData System for Analytics (Netezza) and Db2 SQL compatibility.