Category Archives: Netezza

LDAP Authentication in IBM Netezza

Three authentication methods:

  • Local
  • LDAP
  • Kerberos

LDAP authentication differs from local authentication method in that Netezza system uses the username and password stored on LDAP server.

Please note that: same LDAP user account must also exist in Netezza system catalogs.

Keep in mind the following characteristics of LDAP authentication:

  • After the LDAP authentication process completes successfully, the Netezza system looks up the user in the system catalog. The system displays an error message if it does not find the user, and it terminates the session.
  • If authentication fails, you see the message LDAP authentication failed. The system notes the reason for the failure in the /nz/kit/log/postgres/pg.log file.
  • Netezza users should not notice any difference between LDAP and local authentication.
  • When you CREATE or ALTER a user account, a password is not required if you use LDAP authentication. (Local authentication continues to require a password for user accounts.)

Finding out active netezza host

[root@nzhost1 ~]# crm_resource -r nps -W

crm_resource[5377]: 2009/06/07_10:13:12 info: Invoked: crm_resource 
-r nps -W
resource nps is running on: nzhost1

Runaway query event

You can use the RunAwayQuery event type to monitor queries that exceed configured query timeout limits.

Outdated rows in IBM Netezza

Outdated is another name for deleted rows.

nzdumpschema in IBM Netezza

Use the nzdumpschema command to generate a shell script with SQL statements that duplicate a database by extracting its object definitions and statistics. nzdumpschema generates Table definitions, view definitions, database statistical information, and us
er-defined extensions registered within the database

Note: Because no actual data is dumped, you cannot use this command to back up a database.
Example: To dump table and view definitions to the file named, enter:
$ nzdumpschema -R testdb3

Callhome service in IBM Netezza

The callhome service is an automated notification process that detects problem conditions on the IBM Netezza appliances and reports them to IBM Netezza Support.

This is an optional feature and, if enabled, opens a PMR with IBM automatically. When enabled, callhome automatically collects information and attaches all required log files in the automatically opened PMR.

The callhome service is supported on the following appliance models:

  • IBM PureData System for Analytics N1001 (including the IBM Netezza TwinFin and IBM Netezza 1000 models)
  • IBM PureData System for Analytics N200x
  • IBM PureData System for Analytics N3001

Few example scenarios of automatically opening PMR:

  • SPU/S-Blade/Disk failures
  • Host failovers
  • Unexpected system state changes
  • SPU Core files


Query history database in Netezza

The below note will explain on how to build history database:

We have two types of History Databases

query history – A query database collects and stores the data that is most commonly needed to monitor and report on the query activity of a system.

audit history – An audit database collects the same data as a query database, but stores the data in row-secured tables and digitally signs the data to prevent it from being changed.

Note: If an audit database is used and the history data staging area exceeds its STORAGELIMIT value, the system stops, and the administrator must free up space and restart the system before users can resume activity.


Sequences in Netezza

What is a sequence?

A sequence is a named object in a database that can be used to generate unique numbers.

A sequence may be byteint, smallint, integer, bigint. You can use sequence values wherever you would use numeric values. You can create, alter, and drop named sequences.



CREATE SEQUENCE sequence_name AS data_type   [<options>];

where the options are the following:

> START WITH start_value

> INCREMENT BY increment_value

> NO MINVALUE | MINVALUE minimum_value

> NO MAXVALUE | MAXVALUE maximum_value



Sequences do not support cross database access; you cannot obtain a sequence value from a sequence defined in a different database.

Default MINVALUE is 1

Default MAXVALUE is the max value possible in the datatype.

INCREMENT BY can take both positive and negative values

By default, sequences do not cycle.

Sequences have gaps because IBM Netezza caches sequence values on the host and SPUs for efficient operation.



Best practises for choosing distribution key in IBM Netezza

Please find below some advise:

(1) Avoid choosing Boolean, Floatiing point datatypes as distribution columns. Boolean causes data skew. Floating column cause Merge sort join.

(2) Pick up commonly joined column(s) as distribution column

(3) Note that you can’t update distribution column

(4) If possible, avoid multi-column distribution columns. Go for RANDOM in such scenario!

(5) Its advantageous not to worry so much about dimensional tables distribution key. Prefer choosing RANDOM for dimensional tables

(6) Use same data types for distribution between joined columns

(7) Don’t distribute on Random because it is the easy choice

Zonemaps in Netezza

Before we learn about Zonemaps, it’s important to understand below two terms:

(1) Extent: Smallest unit of disk allocation on a SPU/Disk. 1 Extent = 3 MB

(2) 1 page = 128 KB. An Extent is a combination of 24 pages.

What are Zonemaps?

  • Automatically created persistent internal tables
  • For each extent, these internal tables have the details of (zone map’s supported) column’s minimum & maximum values. i.e., zone maps summarize the range of column data for each page and extent
  • Zone maps reduce disk scan operations by eliminating unnecessary extents
  • Less disk scan operations è High Throughput & High response time
  • Zone maps supported columns (by default): date, timestamp, byteint, smallint, integer, and Bigint
  • Zone maps are much beneficial for nearly ordered data. For example: Historical data/call records/web logs/financial transactions

When page granular level zonemaps introduced?

NPS 7.0 introduced page granular zone maps (zone maps extend down to 128KB pages instead of only 3MB extents, effectivly 24 times more detailed (24 times more performance ? )