[ORIGINALLY PUBLISHED - August 28, 2018]
By John Skier, IBM
Migrating Netezza applications to IBM Integrated Analytics System (IIAS), code name Sailfish, is more than a Netezza forklift upgrade. IIAS delivers better performance and up time than Netezza systems through the use of IBM Power 8 hardware; more tools to gain insight through the inclusion of the IBM Data Science Experience (DSX) product; and expanded support for broader database workloads through the use of IBM Db2 Warehouse. IBM DSX and Db2 are provided using container technology which makes system upgrades easier. Because Db2 Warehouse is a member of the common SQL family, applications are “write once run anywhere” including Db2 Warehouse on Cloud, Db2 Warehouse Local, Db2 and Db2 BigSQL.
Moving applications from Netezza to Db2 does require some migration work, but IBM has enhanced Db2 with many Netezza features, like zone maps, called data skipping in Db2, as well as adding support for Netezza User Defined Functions and User Defined Aggregates. This makes migrating custom functions a simple recompile for most user defined functions. Incompatible Netezza, Db2 SQL Syntaxes are handled at the session level through a compatibility mode SQLCOMPAT=’NPS’ or ‘DB2” (default), so SQL doesn’t need a complete rewrite.
IBM has implemented support for the INZA functionality, as well as the "R" functionality in Db2 to make migration easier, but the function names are slightly different. For example, 'nz' prefix is replaced with 'ap', so nzTapply() is apTapply(). IBM published the package in the CRAN repository as IBM Db2 R.
Migration of data is typically and transparently handled by the db_migrate command which supports the options of nz_migrate. There are also "db" versions of the most popular nz commands. For example, nzsql is mirrored by dbsql, nzload by dbload and there is also a dbunload command that was a long-standing request on the Netezza wish list. Db_migrate handles conversion of Netezza databases into Db2 schemas for Netezza systems that use only the default “ignore schema” mode of operation. db_migrate also provides multiple options for systems that use multiple schema support. Db_migrate enables the easy mapping of database and schema into a schema only context.
Once data is migrated, applications are usually easily migrated by changing the application connection IP, database name to BLUDB, schema to match the db_migrate schema name, which is typically the former Netezza database name and, finally, the database profile from Netezza to Db2. Hand written SQL often works, but editing with a tool like
SED (a stream editor), can change the database and schema names to reflect the new system architecture.
An advantage of the Db2 Warehouse foundation is the ability to store data in both columnar and row storage. Columnar storage provides excellent enterprise data warehouse (EDW) performance while row storage provides OLTP performance. Application developers who add an "organize by row" clause to their metadata table creation statements can store application metadata on the IIAS server, usually removing the need for an OLTP database server to hold application metadata. The ability to store metadata on the IIAS system can result in significant simplification in the EDW deployment through the consolidation of databases to Db2 and the elimination of the need to have two DBA skill sets to manage the solution. Adding the "organize by row" row clause to create table statement is supported by all versions of Db2 10.5 or later, so multiple code streams are not needed.
Development can be done in house using the “Db2 Warehouse Developers edition” available at no charge on the
Docker Store.
IBM is committed to the successful upgrade of our customers from Netezza to IIAS and is providing services to ensure a clean and smooth experience. For clients committing in 2018 to move from PDA/Netezza to IIAS, IBM is offering no charge services to help them assess, plan, and implement a move of their existing PDA workload to IIAS. The IBM team brings proven methodologies, standard practices and automation tooling to enable a smooth transition from Netezza to the new generation IIAS. The consultants will also provide the client staff with a demonstration of the new environment to enable their ongoing success. For more information, access the
IIAS data sheet IIAS Services Data Sheet and
IBM Data Architecture Services.
~~~~~~~~~~~~~~~~~~~~~~
John Skier, IBM
#DataWarehouse#IntegratedAnalyticsSystem#Netezza#PureDataSystemforAnalytics