Netezza Enterprise Stage in DataStage

Netezza Enterprise stage is a database stage. You can use this stage to write bulk data to Netezza Performance Server(NPS). The Netezza Enterprise stage uses the Netezza write operator (nzwrite) to write data to Netezza Performance Server.

The Netezza write operator sets up a connection to an external data source and inserts records into a table. The operator takes a single input data set. The write mode determines how the records of a data set are inserted into the table.

Netezza write operator:

The Netezza write operator writes data to Netezza Performance Server. Netezza does not provide a read operator and hence the data retrieved from the source database is saved in a data set. The Netezza enterprise stage reads data from the data set and writes it to the Netezza Performance Server database.

Netezza data load methods:

The Netezza write operator employs two alternate methods to load data to Netezza Performance Server. You can write data to a Netezza Performance Server database either by using a nzload load utility or by writing data to an external table before writing it to the database.

nzload method:

You can use this load method if the data in the source database is consistent; that is, it implements a single character set for the entire database. Also the input schema for the nzload must be the same as that of the target table in the Netezza Performance Server database. The prerequisite to use the nzload method is that, nzclient utilities and ODBC functionality must be installed on the same computer as the IBM InfoSphere Information Server engine.

External table method:

If the data source that you want to read contains default values for table columns and uses variable format for data encoding such as UTF-8. You can write the data to an external table before loading it into the Netezza Performance Server database.

Write modes

Append:

Appends new rows to the specified table. To use this mode, you must have TABLE CREATE and INSERT privileges on the database that is being written to. Also the table must exist and the record schema of the data set must be compatible with the table. This mode is the default mode.

create

Creates a new table in the database. To use this mode, you must have TABLE CREATE privileges. If a table already exists with the same name as the one that you want to create, the step that contains this mode ends in error. The table is created with simple default properties. To create a table that is partitioned, indexed, in a non-default table space, or to create a customized table, you must use the -createstmt option with your own create table statement.

Replace

Drops the existing table and creates a new one in its place. To use this mode, you must have TABLE CREATE and TABLE DELETE privileges. If another table exists with the same name as the one that you want to create, the existing table is overwritten.

Truncate

Retains all the attributes of a table (including its schema), but discards the existing records and appends new records into the table. To use this mode, you must have DELETE and INSERT privileges on that table.

Netezza system

A Netezza system consists of multiple hardware and software components working together to provide performance and reliability. The NPS models include configurations with one or more hardware racks. Within each rack are numerous components that work together to provide the asymmetric massively parallel processing of the NPS architecture. The key hardware components within an NPS include the following:
􀁘 NPS Host
􀁘 Snippet Processing Units
􀁘 Snippet Processing Arrays

NPS Host

The NPS host, located within the NPS rack, controls and coordinates the activity of the NPS. It performs query optimization; controls table and database operations; consolidates and returns query results; and monitors the NPS system components to detect and report problems.

Snippet Processing Units

The Snippet Processing Unit (SPU) is the basic unit of processing and storage in the NPS.Each SPU is basically a standalone microcomputer, with a CPU, logic processors, memory,and disk storage.The SPU is an intelligent disk storage device, as it has logic to quickly search for the correct information and to return only the matching results of the portions of the data that are saved on its disk.An NPS system has many SPUs: up to 56,User database tables are distributed across all of the SPUs to allow for the parallel query processing.
Each SPU is responsible for managing a portion of your database and tables (called a primary partition), as well as for maintaining a copy of another SPU’s primary partition (called a mirror partition). If an SPU should fail, the mirror partition is used to create a new primary partition on a standby SPU within the system, which will then take the place of the failed SPU.

Snippet Processing Arrays

Snippet Processing Arrays (SPAs) are racks within the NPS system that contain up to 14 SPUs and have the power supplies, fans, and communication fabric that allows the SPUs to communicate with each other and with the NPS host. NPS systems contain at least two SPAs. If you add SPAs to the system to increase the number of SPUs, the SPAs are added in pairs. For each pair of SPAs, one of the 28 SPUs takes the role of a hot standby, ready to take the place of a failed SPU within the system.

NZSQL:

Commonly Used Command Options

nzload Command Options: