topic Re: TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block in Talend Studio

TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

Anonymous — Sat, 16 Nov 2024 14:06:54 GMT

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

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

Anonymous — Sun, 04 Jan 2009 20:46:26 GMT

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

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

Anonymous — Fri, 19 Jun 2009 12:40:16 GMT

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

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

Anonymous — Mon, 22 Jun 2009 13:06:16 GMT

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;

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

Anonymous — Mon, 22 Jun 2009 20:51:19 GMT

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