Migrate from Oracle to Netezza

Following are the major steps involved in Oracle to Netezza migration:

1. Capture DDL, you may use “dbms_metadata.get_ddl” to capture all table definitions. Change varchar2 to varchar, does not really matter if char is more than 16 characters; stored the same way if more than 16 characters. Number is either numeric or float type. Timestamp can be either data or time. This is the most time consuming part and mostly done manually though tools are available, such as ErWin reverse engineering.
2. Most important for Netezza is distribution of data on SPU or disk. Choose distribution for each table, you may like to pick up only a handful fields for distribution of data for tables across the entire database. Recommended to use only one distribution key (field) per table, not multiple fields. For small tables, Netezza broadcasts to host where data gets sent to all SPU or nodes; this is similar to replicate in other appliances.
3. With steps (1) and (2) above, DDL are created on Netezza. Create a user and / or group and assign permissions.
4. You may like to NFS mount Netezza binaries on Oracle host (OR) Oracle file system on Netezza. I prefer the first one, if Oracle system guys do not object.

Mouting NFS from NZ host to Oracle:

Oracle to Netezza migration typically involves spooling data out as a flat text file and using nzload to seed data.
A) These are contents of /etc/export after making changes for allowing Oracle system to access NFS mount. Last line is added for this purpose. IP address for illustration purpose: Oracle (10.11.12.13) & Netezza (10.10.11.11).
[root@NZ_host1 ~]# cat /etc/exports
# NPS setup — do not edit below this line — NFS
/nz/export      10.0.0.2/16(rw,async,insecure,anonuid=500,anongid=500)
# NPS setup — do not edit above this line — NFS
# For exporting as NFS mount on to Oracle
/nz/kit         10.11.12.13/255.255.255.255(sync,no_root_squash,rw)
B) Issue this command at Netezza host
[root@NZ_host1 ~]# exportfs -ra
C) Make a new directory on Oracle, for example: /home/oracle/NZ_bins
D) On Oracle system, do a mount command
[root@ora_dw1 /]# mount -t nfs 10.10.11.11:/nz/kit /home/oracle/NZ_bins
Once mounted, you should see binaries under bin directory (/home/oracle/NZ_bins). If you are trying to load using nzload, you may encounter a problem with password. You need to cache password on the local host first.
[oracle@ora_dw1 bin]$ nzpassword add -u admin -pw password -host 10.10.11.11
[oracle@ora_dw1 bin]$ ./nzload -host 10.10.11.11 -u nzadmin1 -pw zzadmin1 -db dw_db1 -t day_time_t -delim ‘|’ -df /tmp/1
Above should load data into remote netezza database. If done, go to Netezza host and use nzsql to find number of rows inserted. Check on Oracle row count.

NZLOAD can be streamed using UNIX named pipes. That is, first do ‘mkfifo /oraExport/fifo.tmp’ for example. Then, spool Oracle data and write to this file (spool /oraExport/fifo.tmp). Assuming Netezza binaries are available (see above steps), load directly on to Netezza host.