Checking Netezza SPU swap partition error

You may come across this error due to data distribution. This error is displayed as:

SPU swap partition : Disk temporary work space is full

This is displayed both to the client and in the logs (pg.log or pg.log.x). A couple of simple steps are given below:

1)      If your query consists of sub queries, run each of them. I prefer that you create a table for each sub query section. That is, if your sub query has “select name, make ….from table A order by 1, 2”, create a table using the same SQL with ‘create table t_1 as ‘ followed by that SQL. If this table is created, check distribution.

2)      You can check data distribution is a couple of ways; easy way is to use Netezza administrator. Right click on that table from database tab and choose ‘record distribution’. If data is distributed evenly across all SPU, you should see vertical bars correspondingly.

3)      If step (1) creates a SPU swap partition error, this requires further investigation. This query most likely available under ‘Query history’ under session branch in Netezza administrator. Double click that query and it shows summary.

 

Likely, this points to a problem. In my case, no rows returned and only 5 snippets completed out of expected 7.

(4) If all sub queries and SQL code blocks are completed without a problem when used with ‘create table as’, probably the final SQL block is where you need to check.

More Netezza Questions and Answers (Part II)

This is an extension of my previous post (“Netezza Questions and Answers”).

What data type is most suited for zone maps
Zone maps are typically useful for integers, date and time, variations of this data type. Zone maps are useful for ordered that that are usually built into data that is loaded; for example, phone call logs. [Also check more on zonemaps in my original post below].

How are materialized views (MV) used in Netezza.
Similar to other databases, materialized views are defined against base tables. Just like in views, we can not insert, update or delete from MV. MV is automatically used by optimizer when appropriate. When base table data changes, MV gets automatically updated by Netezza. MV is based on a single base table, thus practically not as useful.

What are typical join types in Netezza
Mostly hash joins (note that Netezza may do hash join in memory or on the SPUs if memory is not sufficient for doing a hash join); in some cases sort merge or even some cases nested loops  are performed (did not really come across much; In Netezza lingo it is called either function based join or something similar, not sure about the correct term). Of course, cross or product joins are possible like in any databases.

 

What is equivalent of replication of a table in Netezza on all SPUs.
Imagine a situation in which a small table (us_states) is required for joining against a large table (creditcard_txn). Netezza may decide to read this table from all SPUs (remember, table data is spread on all SPUs) and “assemble” this table on host. This data is then broadcasted to all SPUs, resulting in a copy on each SPU. Note that an important db parameter “factrel_size_threshold” holds that triggering number; any table beyond these many number of rows is considered as a fact table. That is, if this parameter is set to 1 million, any table holding more than 1 million rows is considered by Netezza as a fact table and will not result in this replication or broadcast.

 

Primary goal of a table design is to distribute data evenly on all tables. Is it a good idea to choose multiple columns in Netezza so that data gets distributed evenly.
NO, unless all columns are used during a join process. Most likely, this results in a large amount redistribution of data during query execution.

 

From design point of view, do you forsee performance problem using order ID as an integer for one table and Order num as varchar for another table as distribution keys. Assuming that these two tables are often used for join, do you see any performance problem.
YES, we will encounter performance problem as Netezza redistributes integer to varchar type and recreates the first table. This can be avoided using both tables distributed on order number as either integer or varchar.

 

What is a snippet
Snippet is a small block of database operation, typically three to four operations, that are carried out on all SPUs where data is location. If a query results in these snippets: Snippet A, Snippet B, Snippet C, ….Snippet X; they are carried out in a sequential manner.

 

List which options are prioritized when join operation is required.

Netezza evaluates joins in this order of preference:

1) Colocated joins: All data for joins are located on the same SPU.

2) Redistribute: All required data is not located in the same SPU, send data to corresponding SPU where driving table data is located.

3) Broadcast: Mentioned as replication above; Send all data from SPU to host which collates all that data, sends it to all SPUs. Each SPU has entire table data. That is, if Netezza machine has 32 SPU, there will be 32 physical tables, one one each SPU.

Coming to joins, typically Netezza prefers in this order:

A) Hash join in memory

B) Hash join on disk

C) Sort Merge join

D) Nested loops

E) Cross join

Oracle preference closely resembles the same or similar order.
 

How can I look into some system parameters

 nzsystem showRegistry: Command for looking into system specific information

/nz/data/postgresql.conf: To check NZ db parameters; we can change this file OR use set command at SQL.

 

Why integer data type is preferred in Netezza.

A couple of reasons:

1) Better joins, thus effecient.

2) Netezza compress works only for integer type of data, not with varchar or date.

3) Zonemaps are based on integer data types.

 

How can we find log (SQL) activity for a day.
We can find this under /nz/kit.x.y/log/postgres/pg.log file. Older files are named as pg.log.N (Where N starts from 1, after pg.log file this is the latest file). Assuming that we are looking for a week day within pg.log, we may run
$ cat pg.log | sed -n “/2010-02-01 00/,/2010-02-05 23:59/p” > pg.firstweekFeb2010.log
If this produces no data, look for corresponding log file based on the last update timestamp (ls -ltr sorts them in reverse time stemp order).

What are the ways to get data into Netezza. What happens if inserts are interrupted, how Netezza handles commits.
Please see my previous posting first. Here is a short list: load using nzload, SQL inserts (very slow), create table as command or inserts from other tables, and external tables. When insert is interrupted, all rows that are inserted are already committed unless we use transaction command.

How do you nzsystem command
Most Netezza commands come with a variety of sub commands. For nzsystem, we can list a number of parameters using “nzsystem showregistry”. To find quickly if Netezza is up and running, use “nzsystem showstate”. However, using these commands require you to set “NZ_USER” and “NZ_PASSWORD” at unix level. You can do this with ‘export NZ_USER=username’ and ‘export NZ_PASSWORD=userpassword’, and then run this command. If not, you can specify login and password combo at command level. I do not prefer this way, as any users can do “ps -ef” to check what commands you are using, which also gives login and password you entered at shell command level.

 

Where are Netezza binaries stored

Two important bin locations for Netezza are: /nz/kit.x.y/sbin and /nz/kit.x.y/bin.
Some configurations are located in /nz/data directory.

How can we remove formatting with NZSQL
NZSQL by default shows text formatted. In cases in which we do not need white spaces, use “nzsql -A” option. For example, “nzsql -A -c “select count(*) from hertz.daily_bookings;” allows us to run SQL command direct without logging into nzsql interactively.

Is there a way to stop NZSQL command, if one of the SQL commands fail.
Yes. A similar feature exists in other databases too. In this case, ON_ERROR_STOP=true on nzsql command so that other commands do not get executed. For instance, we like to create a new table (CTAS) and later drop old table. If a new table creation fails, we certainly do not want to drop old table. In this case, this option is very useful.
Can we access data in other databases with the same NZSQL.
This depends on the version. Version 5 and upwards support selects against database.owner.table, provided that user has same login and password access. Inserts are allowed in the current database with data from other databases, not the other way (meaning, we cannot insert into another database from the database where we logged in).

How can we plan and corresponding CPP files.
We can just to ‘explain’ on a query to see how plan looks. At run time, plans are created under /nz/data/plans we will see corresponding plans generated during run time. Corresponding CPP code is located under /nz/data/cache/

Netezza Architecture: Part 1 (Process)

Here is a simple view of Netezza process from client connection perspective. I am attaching a PDF that explains these processes briefly.

Please click link to open a PDF doc; this doc shows Netezza process architecture and process hierarchy: Netezza process interaction

Teradata versus Netezza

I was checking a small table with over 26,000 rows in Teradata. Irrespective of data skew or lack of index or other features, I expected this table to return data within a couple of seconds. I noticed a simple “select *” from Teradata was forcing data to an AMP (similar to SPU in Netezza). Query results returned in about 2 minutes later. This never happens in Netezza, data simply streams from SPU to host without storing data any where. 

* Netezza: within a couple of seconds or less.

* Teradata v12: More than 2 minutes.

I do not buy an argument that this table is not based on mulitset, partition, NUSI, JI or any other exotic operations. I expect any appliance or database to return data within a few seconds, including Oracle.  Surprisingly, not so with Teradata?!

Comparison of Teradata against Netezza

Teradata: AMP; one AMP to many disks; up t0 four columns for distribution; No key may be specified for distribution; PK and FK enforcement; Most database features such as indexes, stored procedures, functions, partitions, etc.; ODBC, JDBC and .NET support; Good support with libraries; Not such a big platform for third party support. Compression in Teradata requires us to specify for each column up to 256 high cardinality values. Somehow, you need to find high occuring values (check out Atanasoft tool with compression optional module in this blog) and create a table structure by specifying all these values. You may ask, what happens a year from now when a fact table is massive and data distribution changed? Looks like we need to rebuild that table with new structure.

Netezza: SPU; one SPU to one disk; up to four columns for distribution; No key may be specified for distribution; PK and FK defined at table level, no enforcement; Lacks most database features comared to Teradata; ODBC and JDBC support; Relatively small set of libraries and code base; Emerging as a platform for third party tools, especially FPGA based programming and analytics.

Netezza materialized views (MV) are pretty lame in stark comparison to quite impressive join indexes in Teradata. Netezza MV is limited to just a single table, no where clause and even restrictions of aggregates such as sum, count, etc.. Teradata supports a few combination of join indexes, including Aggregate Join Index (AJI) and Single Table Join Index (STJI) among others. Teradata AJIs can and usually do cut down query processing on aggregations from a few hours to seconds! Teradata supports many database features including a large set of User Defined Functions (UDF). Netezza also supports UDF, developing them is not as easy. Netezza compression is built in from Netezza 4.5 onwards, achieves typically over 30% savings.

Netezza Questions and Answers

Explain FPGA and how is it useful for query performance.

FPGA: Field Programmable Gate Array (FPGA) is located on each SPU. Netezza is different from other architectures. Netezza can do a “hardware upgrade” through software by using FPGA. Hardware is reconfigured during install.

While reading data from disk, FPGA on each SPU also helps in ‘filtering’ unnecessary data before getting loaded into memory on each SPU. This way, FPGA does not overwhelm with all the data from disk.

What is a zone map.

Zone map in Netezza is similar (concept wise) to partitions in Oracle. Netezza maintains map for data so that it does relies on zone map to pull only the range it is interested in. For example, if we need to pull out data from Jan 2009 till June 2009 from a table that is distributed on date column, zone map helps us to achieve this. Zone map is maintained by Netezza automagically, no user intervention needed. Zone mapping is done at a block (extent) level. Netezza has zone maps for all columns (not just distributed column) and includes information such as minimum, maximum, total number of records.

How do you deal with historical data, with respect to zone maps.

Sort data first, based on historical data (for example, date) and load this in using nzload.

What are different ways to load

1. nzload
2. External tables
3. Create table AS (aka, CTAS).
4. Inserts (Eeeewee!!)

Does everything gets cached in Netezza (or any other data appliance).

Typically only schema and other database objects are cached in appliances. Data is not cached, in general. In most cases, data is not saved any where (in any cache or on host computer) and is streamed directly from SPU to client software.

What is the best data appliance.

Obviously, it all depends. This is my (limited) view:

1. From features respect, Green Plum.
2. Popularity with a bit of hype, Netezza.
3. Matured and well respected, Teradata.
4. With existing database integration, Dataupia.

Largest implementations:

* Teradata: 72 nodes (two quad-core CPUs, 32GB RAM,104 / 300GB disks per node) and manages 2.4PB.
* Greenplum: Fox Interactive Media using a 40-node, Sun X4500 with two dual-core CPUs, 48 / 500GB disks, and 16 GB RAM (1PB total disk space)
Source: Vertica’s Michael Stonebraker!

How is load achieved in Netezza and why is that quick / fast.

Loads by pass a few steps that typically a query would go through (a query goes through plan generation, optimization and transaction management). Loads are done in terms of “sets” and this set is based on underlying table structure (thus loads for two different tables are different as their sets are based on table structures). Data is processed to check format and distribution of records calculated very quickly (in one step), fills into ‘set’ structure and writes to storage structure. Storage also performs space availability and other admin tasks, all these operations go pretty quick (think of them as UNIX named pipes that streams data and SPU stores these records).

When are we likely to receive incorrect (aggregate) results.

Very rarely a driver may return aggregated results that are still getting processed back to client. In this case, client may assume that calculation is complete, instead of updating with latest or final results. Obviously, driver has to wait for Netezza to complete operation on host computer, before delivering results.

Explain how data gets stored in Netezza and how does SPU failover take place.

Data is stored based on a selected field(s) which are used for distibution.

==Data (A)==> Hash Function (B) ==> Logical SPU identifier list (C) ==> Physical SPU list (D) ==> Storage (E)

When data arrives, it is hased based on field(s) and a hash function (B) is used for this purpose. For example, for a hypothetical 32 node system system, logical SPU identifier list has 32 unique entries. If there are 1000 hashed data items from (B), there are 1000 entries in (C), all having only 32 SPU entries (a number of data items go to the same SPU, thus multiple (B) entries map to same (C)). For instance, (C) has values [3,19,30,7,20,25,11,3,22,19....]. This way, 1000 data entries are mapped. (D) has physical IP address of both primary and failover SPU. If there is a failover, this is the only place where Netezza need to update its entries. Same goes for a system that has a new SPU added. It is a little complicated, in principle, this is the concept.

what are 4 environment variables that are required. What are different states on Netezza.
Environment variables: NZ_HOST, NZ_DATABASE, NZ_USER and NZ_PASSWORD
* Online: Normal or usual state.
* Stopped: Netezza will shutdown after completing current queries, no new queries allowed.
* Offline: Waits for completion of current queries, new or queries in queue receive error.
* Paused: Same as above, but no error displayed. Typically caused during Netezza bootup or startup.
* Down: Just plain down, could be due to Netezza server problem or user initiated.

Does Netezza support concurrent update of the same record
In case of conflict in which the same record is set for modification, Netezza rolls back recent transaction that is attempted on the same record, in fact same table. This is generally acceptable in DW environments. Netezza does support serialization transactions and does not permit dirty reads.
 
How Netezza updates records. Give an idea of how transactions are maintained and how read consistency is maintaned.
Netezza does not update records in place, it marks records with delete flag. In fact, each record contains two slots, one for create xid another for delete xid. Delete xid allows us to mark a record with current transaction for deletion, up to 31 transactions are allowed in Netezza for all tables. As noted earlier, only one update at a time allowed on the same table though. Here update refers to transactions that are not committed yet. Coming back to delete xid, this is how Netezza maintains transaction roll back and recovery. Once a record is modified, it’s delete xid is given transaction id; this is changed from previous value of 0, all records when loaded will contain 0 for delete xid. Note that FPGA uses its intelligence to scan data before delivering them to host or applications.
Sample data:
[ROW id][Create xid][Delete xid]
[R1][T1][0]                           // First time a record is loaded, record R1
                                                  // After some time, updating the same record
[R1][T1][T33]                      // Record R1 is updated; note T33
[R33][T33][0]                     // New update record R33; similar to a new record this has zero for Delete Xid

If the record is deleted, simply deletion xid will contain that transaction id.
* Based on the above, how do you know a record is the latest. It has zero in delete xid flag.
* Extending same logic, how do we know a record is deleted. It has non zero value in delete xid flag.
* How do you roll back to transaction. Follow similar to above listing, we can roll back a transaction of our interest.
Note that transaction id is located in create xid flag and that is our point of interest in this case. From what I know, row id and create id is never modified by Netezza.
 
What happens to records that are loaded during nzload process, but were not committed.
They are logically deleted and administrator can run nzreclaim, we may also truncate table.

Can a group become a member of another group in Netezza user administration. Can we use same group name for databases.
In Netezza, public group is created automatically and every one is a memeber of this group by default. We can create as many groups and any user can be member of any group(s). Group can not be a member of another group. Group names, user names and database names are unique. That is, we can not have a database called sales and a group also called sales.

How can we give a global permission to user joe so that he can create table in any database.
Login into system database and give that permission to user by saying “grant create table to joe;”

What permission will you give to connect to a database.
List.  Grant list, select on table to public (if logged into sales database, this allows all users to query tables in sales database).

Do we need to drop all tables and objects in that database, before dropping a database.
No, drop database will take care of it.

What constraints on a table are enforced.
Not null and default. Netezza does not apply PK and FK.

Why NOT NULL specification is better in Netezza.
Specifying not null results in better performance as NULL values are tracked at rowheader level. Having NULL values results in storing references to NULL values in header. If all columns are NOT NULL, then there is no record header.

Create Table AS (CTAS), does it distribute data randomly or based on table on which it received data.
Response: Newly created table from CTAS gets distribution from the original table.

Why do you prefer truncate instead of drop table command.
Just empties data from table, keeping table structure and permission intact.
 
When no distribution clause is used while creating a table, what distribution is used by Netezza.
First column (same as in Teradata).

Can we update all columns in a Netezza table.
No, the column that is used in distribution clause cannot be used for updates. Remember, up to four columns can be used for distribution of data on SPU. From practical sense, updating distribution columns result in redistribution of data; the single most performance hit when large table is involved. This restriction makes sense.

What is dataslice and SPU.
For me, they are the same! Of course, this answer is not accurate reply in your interview(s).

What data type works best for zone maps.
Zone maps work best for integer data types.

What feature in Netezza you do not like.

Of course, a large list, especially when compared to Oracle. PK and FK enforcement is a big drawback though this is typically enforced at ETL or ELT process [ELT: Extract, Transform and Load. Note that 'Transform' and 'Load' can happen within Netezza].

Database Technology

[Your comments are very much appreciated. Please feel free to suggest any topics of your interest in this domain].

Any major technology (IT) based solution will have database as a crucial component. Probably, this will remain that way for many years and it has been that way for decades. As we push towards more sophisticated, diverse and integrated solutions, database plays even more important role. On the application levels, one may use different languages (C, C++, Java, Ruby, etc) or techniques (Corba, Ajax or others) or protocols (TCP, HTTP, etc), one thing that is relatively constant is way in which you access data. The last mile has to be SQL, that is how it hits database. Databases will grow in every way (size, features, availability, business continuity, our expectations and even price). A few years ago, database with a few giga bytes in size is considered huge. Nowadays, Tera bytes and a few hundred Tera bytes is very common. I am creating this site to talk about database technology, giving overview from start to implementation. Before even we talk about technology, we need to look at problem that it intends to solve. We need a solution that would either improve existing one, create a new one or provide an alternative solution. I like to look at ‘holistic’ approach for such a solution:

• Study business case & their requirements: Understand, analyze, design and implement a business solution. This is handled by conceptual design & logical database design.
• Implement a database solution: This is where physically database gets created. Even before embarking on this journey, plan what is required and visualize system before anything is acquired. All technical details are ironed out in this phase.
• Accessing database: This is where applications are developed and data is accessed. As this is the last stage of any development and deployment, most folks give little attention to this phase. Unfortunately, this is the one that directly impacts users, including business sponsors.

Now let us dig into details a bit more.

Business Facing

Just like any major investments, DB technology and its implementation is very much business driven. Traditionally there has been so much disconnect between business and techie world, there is a discomfort between them even during any meetings! More and more people are tech savvy nowadays. However, normal users are more exposed to Internet based technology, not so much as database side.

Get business needs

Imagine that we are given a task of coming up with a solution, for sake of simplicity, let us assume it is a new system you need to implement this solution. We need to have some idea what is involved. Is this about customers, products, pricing, et cetera. What are important aspects you like to capture in this process. Similar to ‘memory maps’ or ‘flow charts’, database technology uses “Entity Relationship” diagram (ERD, for short). However, ERD does not imply flow or logical movement from one entity to another (meaning, all items in ERD are at same level, we do not start from one end and complete at another end. This kind of interpretation may make sense from functional implementation level, such as customer placing an order. This comes later in this topic).

Data Model

It is important to realize that any non-trivial project requires planning, analysis and clear thought out process. After a project details are spelled out to us, we need to plan for two major components: process & data . Though they are dependent (process uses data, data used by process), they can be treated independent

Conceptual => Logical => Physical

It is important to appreciate that each phase is completed before embarking on next phase of data model. This is often iterative process.

---

Performance

---

This is perhaps the most discussed & debated topic in database implementation. Very often customers and business users blame it on person who managing databases. Poor DBA folks are taciturn in accepting blame, even though any system that is designed is always based on so many compromises.

• Business users often see poor performance when they do not get results back.
  • Management with (not much) technical background would conclude: Let us upgrade CPU, bigger and better horse power! Think just for a moment, is that a good solution? Some times it is, lots of times it may not be. When you have high processing speed, it could process results quickly resulting in much worse I/O. That is, if Input / Output systems are not scaled properly, we will have lots more data than it can efficiently handle resulting in a huge performance downgrade. There could be more contention for resources as CPU is churning out more instructions. When some one gives this crazy idea of upgrading to better CPU, adjust their expectations.
• Throughput is another way to notice poor performance. Just like a water faucet with larger pipe attached gives more water, though system giving data out may be so slow that you could see data getting refreshing on your screen slowly.
  • In Data warehouse or Decision Support Systems, performance is enhanced greatly based on two important factors: Parallel Query Processing and Parallel Disk Access. This is where Netezza greatly surpasses any expectations! Each query given to Netezza is divided into ‘sub’ query to hundreds of mini-hosts (each have their own hard drive, memory, CPU and even their own OS). Each of these units process query at the same time from their pool of data and collate all that back for completion of query. This results in austounding response time. Even a table with Tera bytes of data returns results in less than one second, always!! Even well tuned any other database system could take minutes or half-hour. In this respect, Netezza is faster by 1000 times or more. See more about Netezza below.
• (Oracle has a cool feature, that allows us to ‘hint’ at first nrows)

---

Database Hardware & Systems Architecture: Netezza, Oracle, Sybase and Red Brick

---

Netezza

We have recently purchased Netezza and a number of DW projects are implemented on this platform. Netezza is super in saving costs, performance, admin (DBA & sys admin) support, ease of operation, simplicity (in understanding system) and easier upgrades. Any person (even without IT experience) can understand system at higher level. If there is only one reason, why any one should go for Netezza, it’s performance is exceptional among all database products.

Netezza uses many small units (known as SPU) with their own hard drive, memory, OS (DBOS) and network facility. These SPUs are interconnected, so that they can all share data and can be controlled by main host unit. Host runs on Linux and sends / receives queries. Netezza does not cache anything. Every query is pretty much a full table scan. Think about a mesh with each connecting point of horizontal & vertical lines is where a SPU resides. Imagine, if you have 100 SPUs on your Netezza system, data is usually spread across evenly against all SPUs. If you run a query to get data, all 100 SPUs send that data back. As you can imagine, this system *can be* up to 100 times faster, in this case.

SPU is divided into 3 areas for storage purpose. Main, back up and temp space. Same SPU never acts as a backup for same main storage. Reason is simple: if hard drive goes down, we like to have backup else where (on some other SPU), not on the same hard drive (then, essentially you lose entire data located on that SPU, as both online and backup are not available).

Let me give an analogy while performance of Netezza so amazing. Think of a hypothetical customer care scenario where a single person supports phone calls. She refers to a massive book catalog to find price and details of any product (think of this as a traditional database technology). She is now given ten interns, all are given a small section of the catalog. Any customer calls in, same customer care person assigns same work to all ten interns. They go through their small sections and give details back to her. She collates all that information and gives back to customer. Theoretically (and lots of times practically too), this is ten times faster than one person doing all this alone. Note that there is some time spent while collating (or aggregating) those details back. This is how Netezza works.

• It is not that easy to divide queries into mini-queries and forward them to all SPUs. Need to have a pretty good query processor.
• It is easy to collate or aggregate results back, especially those involving arithmetic operations. Suppose we are summing all sales, if each SPU returns sales_spu1, sales_spu2, sales_spu3…..sales_spuN. Even if any sales summation returned from any SPU can be added in any combination to get grand total (of course, we have to get results from all of them). Another way of saying it is: s1+s2+s3+…+sn is also same as s1+sn+s4+s2+s3+….s(n-1). Thus, we do not have to arrange these results from each SPU in any particular manner (unless operations such as sorting is requested in any query).

For some queries that were taking many hours on another system was tested on Netezza. Netezza just completed in a few minutes! Netezza is based on Postgres and uses only a small set of it. There is also another competing product: Datallegro (www.datallegro.com). There is even a rumor that many folks in datallegro came from Netezza. Netezza apparently has very small number of employees (a couple of hundred), yet sells & supports products that are worth a million dollars (smaller configuration costs around 500,000 or so).

However, there are a few things that do not impress many of us:

1. It crashes way too often. This is NOT a problem as such, since entire database comes back online within a few minutes without even any one noticing. This may make lots of people discredit this product as a serious competitor.
2. There is no good support for all database functionality. There are no triggers, no SP, no RI (yes, no referrential integriy; You can declare RI for a table and used only to enforce it for DDL. No data integrity checks while inserting / updating data), no other esoteric database objects and native code.
3. Same goes with database admin level: no back up (provides binary, for all practical purposes, very limited), no granular admin of users & roles, etc.
4. Overall, a bit more unstable product.

At the same time, it allows administrators to check various database related activities. It has even a windows based tool to check & administer various aspects (users, roles, tables, SPU, etc). You could check (SQL, database activity and various other db related) logs, including dynamically created C/C++ code Netezza uses for all queries. It is amazing that a query plan that Netezza creates is pretty extensive and detailed.

Netezza apparently has more competitors now: including Datallegro, IBM, etc.