How to update a row using Netezza

hiteshm · Post by **hiteshm** » Mon Dec 03, 2007 8:28 am

Hi,

I've just started a new project for a client using DataStage v8.0, using Netezza as the target databse.

I am new to Netezza, but apprantly they are unable to update an exisitng row in a Netezza table. I've searched the documentation, I can only see Append, Create, Replace and Truncate options.

Am I missing something obvious or does the Netezza stage not allow any updates?

Any suggestions would be appreciated.

Rgds

Hitesh

ray.wurlod · Post by **ray.wurlod** » Mon Dec 03, 2007 2:31 pm

It's the stage that does not permit updates, while your Write Method is set to use direct writes - effectively bulk load. Experiment with other values of Write Method and Write Mode properties, and you will probably encounter some form of Upsert being available.

I also have not used this stage type - the above is based on how other Enterprise stage types work. If the Netezza Enterprise stage does not have an Upsert (which I would find very surprising) there is always the ODBC Enterprise stage.

Althaf6553 · Post by **Althaf6553** » Wed Dec 05, 2007 3:47 am

just use odbce nterprise as target stage and instead of write u can find upsert mode //then u ll have different options such as insert only, update only, insert and update ...etc then u can carry out updation in netezza

hiteshm · Post by **hiteshm** » Wed Dec 05, 2007 9:15 am

Thank you for you suggestions Ray and Althaf.

But what is the difference between the ODBC Connector and ODBC Enterprise stage in v.8. Both seem to allow option to updates.

shawn_ramsey · Post by **shawn_ramsey** » Fri Dec 21, 2007 2:49 pm

Hitesh,

You do not (I really mean DO NOT) want to use the ODBC stage to do an update in Netezza. Netezza has no indexes and the ODBC issues singleton updates. The combination of the two with results in significantly poor performance (we had a table that was updating at a speed of 1 row every 15 seconds).

You have 2 options.
1) Talk to IBM and get the new Netezza stage. We have done a lot of work with them to resolve these performance issues and they have added the ability to bulk update as well as a bulk extract to the stage.

2) If you cannot update the stage, you can bulk load the data into a staging table and do an update SQL statement after the load.