Getting Started with IBM Netezza

Note: More recent version covering Netezza 7 is available here: http://szahariev.blogspot.com/2013/07/getting-started-with-ibm-puredata.html

Data Warehouse, Business Intelligence, ETL, Data Analysis are terms we here more and more every day. But there is one name that becomes more and more popular: Netezza – state of the art data warehouse appliance offered by IBM. If you want to get started with Netezza, there is a good news: IBM distributes an emulator running under Windows that you can download and deploy at home. So here is what you need:

1. Download and install VMware player (free) or VMware workstation (paid)
2. Install VIX API (free) if using VMware player
3. Download the Netezza emulator by first requesting to join the IBM Netezza Developer Network (NDN) by following this link: https://www.ibm.com/developerworks/mydeveloperworks/groups/service/html/communityview?communityUuid=35ac05e2-4e00-42fe-b252-111e5f3ad8fa

When you join the IBM NDN you will find there not only the emulator but also the required product documentation that will guide you to master the product. Here is what you will get when the emulator is running:

Currently IBM does not offer Windows based GUI for querying the Netezza so you will have to connect to the Netezza host and use nzsql by using ssh client.

One 3rd party alternative to the nzsql is the Aginity Workbench for Netezza. It runs under Windows and provides GUI for querying the Netezza. The application will require a free registration after a 10 days trial period. The downside is that you will need a Netezza ODBC or OLE DB driver to connect to the Netezza host. Unfortunately these drivers can be downloaded only by IBM customers.

Using Notepad++ to Search and Replace using Regular Expressions

Not long time ago I had to modify a 300 lines SQL Server stored procedure that uses columns containing spaces in the names into a version that does not contain spaces. For example columns like [Price Rate] should be converted to Price_Rate. Doing this manually is long and tedious task. Fortunately Notepad++ saved the day once again – use regular expressions.

To to search and replace the spaces for the column names with underscore open the Notepad++ Replace dialog (Ctrl+H) and type the following for the search pattern:

\[([0-9a-zA-Z]*) ([0-9a-zA-Z]*)\]

The above will match everything that starts with [ continues with combination of letters or numbers, has a space after that, has a combination of letter or number after the space and ends with ]

Type the following for the “Replace with” field:

\1_\2

This means that Notepad++ will replace the \1 with the match between the [ and the space from the column name. The \2 will be replaced with the match between the space and the ]. Here we are using one special feature – if part of regular expression is within a round brackets then this match can be tagged using \1, \2, \3, etc.

At the end select “Regular expression” for “Search Mode” and hit the “Replace All” button.

For more information about the special character of a regular expressions take a look here.