TRUNCATE TABLE cannot run inside a BEGIN/COMMIT block

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
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
Highlighted
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;

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog