Compared to Informix®, Netezza® has some limitations regarding transactions and concurrent data access.
Informix native mode (non-ANSI):
Netezza:
CREATE TABLE tab1 ( k INT PRIMARY KEY, c CHAR(10) ) WHENEVER ERROR CONTINUE BEGIN WORK INSERT INTO tab1 ( 1, 'abc' ) SELECT FROM unexisting WHERE key = 123 -- unexisting table = sql error COMMIT WORK
With Informix, this code will leave the table with one row inside, since the first INSERT statement succeeded. With Netezza, the table will remain empty after executing this piece of code, because the server will rollback the whole transaction.
Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with Netezza: Informix transaction handling commands are automatically converted to Netezza instructions to start, validate or cancel transactions. However, since Netezza is not designed for OLTP applications, you must review any code doing complex data modifications. See the concurrency topic for more details.
You must review the SQL statements inside BEGIN WORK / COMMIT WORK instruction and check if these can raise an SQL error. To get the same behavior in case of error when connected to a different database than Netezza, you must issue a ROLLBACK to cancel all the SQL statements that succeeded in the transaction, for example with a TRY/CATCH block.
TRY BEGIN WORK ... COMMIT WORK CATCH ROLLBACK WORK END TRY