IBM PureData System for Analytics, Version 7.1

Transaction control

Transaction control enforces database integrity by ensuring that batches of SQL operations run completely or not at all. The transaction control commands are BEGIN, COMMIT, and ROLLBACK.

IBM® Netezza® SQL supports auto-commit transaction mode. In this mode, all SQL commands commit when you run them. If the system encounters a SQL command before a BEGIN SQL command, it runs the SQL command in auto-commit transaction mode. If the system encounters a BEGIN SQL command, it runs all successive SQL commands within the transaction. To end a transaction, you must issue a COMMIT or ROLLBACK SQL command.

Some SQL commands are prohibited within the BEGIN/COMMIT transaction block. For example:

BEGIN
[CREATE | DROP] DATABASE
ALTER TABLE [ADD | DROP] COLUMN operations
SET AUTHENTICATION
[SET | DROP] CONNECTION
GROOM TABLE
GENERATE STATISTICS
SET SYSTEM DEFAULT HOSTKEY
[CREATE | ALTER|DROP] KEYSTORE
[CREATE | DROP] CRYPTO KEY
SET CATALOG
SET SCHEMA dbname.schemaname, where dbname is not the current database

These SQL commands are also prohibited within the body of a Netezza stored procedure. If you use one of these commands within a transaction block or stored procedure, the system displays an error similar to the following message:

ERROR: CREATE DATABASE: may not be called in a transaction block or stored procedure