A transaction is a series of one or more operations on database-related objects and/or data. The transaction management is process of ensuring the transaction is successfully completed and commited in the Netezza database. In this article, we will discuss about the Netezza transaction management using BEGIN, COMMIT and ROLLBACK.
Read:
- Netezza CREATE TABLE command and Examples
- Database ACID Properties and Explanation
- Clustered base table in Netezza
- IBM Netezza Alter Table Command and Examples
- Netezza Create User Command and Examples
You can execute the DDL statements in transactions. Statements executed outside of a transaction are automatically committed.
Netezza Transaction Management – BEGIN
Use the Netezza BEGIN command to start a transaction block. You need no special privileges to use the BEGIN command in the Netezza.
Netezza BEGIN Syntax
Syntax for starting a transaction block in Netezza:
BEGIN [ WORK | TRANSACTION ];
BEGIN command takes the following optional input parameters:
WORK or TRANSACTION – are optional keywords and have no effect on the transactions.
Netezza BEGIN Examples
TRAINING.ADMIN(ADMIN)=> BEGIN WORK; BEGIN TRAINING.ADMIN(ADMIN)=>
Netezza Transaction Management – COMMIT
You can use the Netezza COMMIT command to commit the current transaction. You must be an administrator, or an administrator must have given you the appropriate object privileges, to use this command.
Netezza COMMIT Syntax
Syntax for committing a current transaction:
COMMIT [ WORK | TRANSACTION ];
COMMIT command takes the following optional input parameters:
WORK or TRANSACTION – are optional keywords and have no effect on the transactions.
Netezza COMMIT Examples
TRAINING.ADMIN(ADMIN)=> COMMIT WORK; COMMIT TRAINING.ADMIN(ADMIN)=>
Netezza Transaction Management – ROLLBACK
Use the ROLLBACK command to abort the current transaction. You must be an administrator, or an administrator must have given you the appropriate object privileges, to use this command.
Netezza ROLLBACK Syntax
Syntax for rolling back the current transaction:
ROLLBACK [ WORK | TRANSACTION ];
COMMIT command takes the following optional input parameters:
WORK or TRANSACTION – are optional keywords and have no effect on the transactions.
Netezza ROLLBACK Examples
TRAINING.ADMIN(ADMIN)=> ROLLBACK WORK; NOTICE: ROLLBACK: no transaction in progress ROLLBACK TRAINING.ADMIN(ADMIN)=>
Netezza BEGIN, COMMIT, ROLLBACK Examples
Let us understand the Netezza BEGIN, COMMIT and ROLLBACK with an examples:
--Create sample table for demostration TRAINING.ADMIN(ADMIN)=> CREATE TABLE tab1 ( col1 INT, col2 CHAR(10) ); CREATE TABLE TRAINING.ADMIN(ADMIN)=> select * from tab1; COL1 | COL2 ------+------ (0 rows) -- insert record with transaction blocks TRAINING.ADMIN(ADMIN)=> BEGIN WORK; INSERT INTO tab1 values ( 1, 'abc' ); COMMIT WORK; BEGIN INSERT 0 1 COMMIT TRAINING.ADMIN(ADMIN)=> -- Check table for records TRAINING.ADMIN(ADMIN)=> select * from tab1; COL1 | COL2 ------+------------ 1 | abc (1 row)