Update in Netezza Enterprise stage

Doing update or update then insert requires the use of unique key column in Input->Properties tab.

The following needs to be configured in Input->Properties tab :

Load Method = NZLOAD

Table_Name = ‘give your table name’

Update Mode = Update or Update Then Insert

Drop Work Table = ‘True’ (Usually we don’t create a work table for load. But if you have a work table, you can specify it here.)

Key Column = Specify Key Column

(You can add any number of Key column. Just add one Key Column property for each column)

Unique Key Column :

This column can be a sequential number which is generated and may or may not be present in the target table. Its ok even if this column is not present in the target table and still appears in

Target table metadata in datastage.

Usually we generate it in a transformer with some column like SEQ_SURR, and assign a value = @PARTITIONNUM+(@NUMPARTITIONS*(@INROWNUM-1))+1

The requisite is that it should have a unique value for each row coming from source.

Updatable column :

If you want the table to get updated values for all the columns for the specified key combination, then don’t select this property. Else if you want only some of your columns to be updated,

Then select this property, one for each column you want to update.

Write Method = Update

Connection :

Provide the connection details that are required for the properties

Datasource, Database, Password, Server and User

That’s it! That’s the minmum steps you require to configure updates for Netezza Enterprise stage.