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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog