One Star

TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

This is an error when I attempt to use the "Clear" option on a Netezza output table.
Exception in component tNetezzaOutput_1
org.netezza.util.PSQLException: ERROR: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

I'm attempting to do a basic truncate/reload operation on a table.
I don't see any other options..... there should be a way to run some basic "truncate table foobar;" type SQL, right?
Ideas?
Thanks...
--Ivan
4 REPLIES
One Star

Re: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

Hi Ivan,
"TRUNCATE" is a DDL command and can't be executed within a transaction. I don't know anything about your job or "Netezza". But you should ensure that you do not start a transaction before your command (or close an existing one).
Bye
Volker
One Star

Re: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

any workarounds for this.. running into similiar issue with version 3.1.1. what control can be used that is without tranaction scope for Neteezza
One Star

Re: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

Use the below approach as cautious workaround, make sure there are no transaction implications in the flow after this.
Have COMMIT before truncate statement like COMMIT; TRUNCATE TABLE xyz;
One Star

Re: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

Hi,
Try :-
EXECUTE IMMEDIATE 'TRUNCATE TABLE xyz;